Solved: Generate a date type column with a given range. - Page 2

chiukim · 04-14-2023 12:02 AM

I have table called PERIOD. It have [date] and [new virtual column].
I need to generate a date type column in [new virtual column] based on the [date].
Im currently using the IFS() for the 1st three rows. I dont know how to get the formula for the 4 and 5 row.

Its expected value should be 01/31/2023 if today's month is january or 03/30/2023 if today's month is march and so on. If today's month is not in the [date] is should have leave blank.

Suvrutt_Gurjar

Please try for EOM Jan, Mar, May, July, Sept, Nov

IF( IN(MONTH(TODAY()) , LIST(1 , 3 , 5 , 7 , 9 , 11)) , EOMONTH(TODAY(), 0) ,"")

and for EOM Mar, July, Nov

IF( IN( MONTH(TODAY()) , LIST(3 , 7 , 11)) , EOMONTH(TODAY(), 0) ,"")

View solution in original post