How to create column to populate latest quarter-end.

Question

Hi All --- I want to create a new column using formula tool which populate latest quarter-end date (31-Dec, 31-Mar, 30-Jun & 30-Sep) using system date. Suppose if I am running the workflow in the month of Nov or Dec then still workflow should populate quarter-end date as 09/30/2023. Only after completion of December month, data should populate as 12/31/2023.

Can someone please help me on this.

Thank You!

cjaneczko · Accepted Answer

Are you always looking to find the most current Quarter end? This formula should give you the last date of the quarter you are currently in.

edit: Updated else to null().

if DateTimeFormat(DateTimeToday(),'%m') in ('10','11','12') then
datetimetrim(DateTimeAdd(DateTimeToday(),12-tonumber(DateTimeFormat(DateTimeToday(),'%m')),'months'),'lastofmonth') elseif
DateTimeFormat(DateTimeToday(),'%m') in ('07','08','09') then
datetimetrim(DateTimeAdd(DateTimeToday(),9-tonumber(DateTimeFormat(DateTimeToday(),'%m')),'months'),'lastofmonth')
elseif
DateTimeFormat(DateTimeToday(),'%m') in ('04','05','06') then
datetimetrim(DateTimeAdd(DateTimeToday(),6-tonumber(DateTimeFormat(DateTimeToday(),'%m')),'months'),'lastofmonth')
elseif
DateTimeFormat(DateTimeToday(),'%m') in ('01','02','03') then
datetimetrim(DateTimeAdd(DateTimeToday(),3-tonumber(DateTimeFormat(DateTimeToday(),'%m')),'months'),'lastofmonth')
else null()
endif