Generate a date type column with a given range.

[chiukim]

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 · Accepted solution]

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) ,"")

[chiukim]

Thanks alot! 😍

[chiukim]

Hi @Suvrutt_Gurjar i would like to add another condition do this formula. example today() is 5/8/2024. the return date for eom Jan, Mar, May, July, Sept, Nov should be 3/31/24. same goes with  for EOM Mar, July, Nov the  return date should be 3/31/24 as well. Thanks

[Suvrutt_Gurjar]

Could you elaborate?

You have given a date of today as 5/8/2024

---

@chiukim wrote:

the return date for eom Jan, Mar, May, July, Sept, Nov should be 3/31/24. same goes with  for EOM Mar, July, Nov the  return date should be 3/31/24 as well

---

But expected output is always 3/31/2024 for all 12 months? This does not seem to make a sense. Please elaborate with more examples and more clarity.

[chiukim]

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

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

for this formula, if today()  5/8/24 is less than the monthend of today's month 5/31/24. the date value should return is below the today's month in the list which is  3 (march) then return the last date of march which is 3/31/24. Another example, if today's date is 4/31/24 which is not in the months list because its 4. should return also a date 3/31/24. 
Another example, if todays date is 3/18/24, should return 1/31/24. which is the below month of march in the list formula. 

[Suvrutt_Gurjar]

Okay thank you but there are still more dates test cases you please mention how those should return?

What about if today's date is 1/15/2024 or 12/14/2024? I guess 12/14/2024 will return 11/30/2024?

Also if today is 05/31/2024 or today is 03/31/2024, then what it should return?

Please confirm.

[chiukim]

if today is 1/15/24 should be 11/30/23
if today is 12/14/24 should be 11/30/24
if today is 05/31/24 should be 05/31/24
if today is 03/31/24 should be 03/31/24

i created this formula feel free to correct. 
ifs(
and(today()<>EOMONTH(TODAY(), 0),IN(MONTH(TODAY()) , LIST(1 , 3 , 5 , 7 , 9 , 11))), EOMONTH(TODAY(), -2),
and(today()=EOMONTH(TODAY(), 0),IN(MONTH(TODAY()) , LIST(1 , 3 , 5 , 7 , 9 , 11))), today(),
and(IN(MONTH(TODAY()) , LIST(2 , 4 , 6 , 8 , 10 , 12))), EOMONTH(TODAY(), -1)
)

[Suvrutt_Gurjar]

Great. You seem to be on right track with your formula. Please test and correct if any errors.

Edit: minor edits to the post.

[Suvrutt_Gurjar]

---

@chiukim wrote:

if today is 12/14/24 should be 11/30/23

---

I think you mean 11/30/24 and not 11/30/23 here? 

[chiukim]

yes its 2024. my apology. thanks