Please help me to understand the code.

[anonymous008_G]

Hi,

Can someone please help me to understand the below code. The idea of creating this code was if i am running the workflow in mid-quarter then last quarter end date should be populated, For example, if I am running the formula tool in Jan, Feb or Mar 2024 then 12/31/2023 date should get populated in the new column. It was happening as per expectations earlier, but now i see date as 12/31/2024 is getting populated. Can someone please help in this?

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

[binuacs · Accepted solution]

@anonymous008_G 

put the parts of your expression in a formula tool

[anonymous008_G]

thanks for the detailed explanation and what about  "if DateTimeFormat(DateTimeToday(),'%m') in ('10','11','12')" this.What is the meaning of this statement?

[binuacs]

The formula DateTimeFormat(DateTimeToday(),'%m') returns the month number, So the above formula checks whether the current month falls on October, November or December 

[anonymous008_G]

thanks, much appreciated. Now i understood why it is giving 12/31/2024 date as a output data, because current is 3 which subtracting from 12 equals to 9 and adding back this value in current month which Dec-2024. 

Could you please advise how can i modify this statement so that if i run the code in Jan-24, Feb-24 and Mar-24 then Dec-23, If i run in Apr-24, May-24 and Jun-24 then Mar-24, If i run in Jul-24, Aug-24 and Sep-24 then Jun-23 and so on. 

Can we do that?

[binuacs · Accepted solution]

@anonymous008_G @If you look at your formula you can see the DateTimeToday() function is being used which always returns current date, if you want to check the formula for a specific date field  you need to mention that field instead of DateTimeToday() in the formula. Suppose that you have field called Date then replace the DateTimeToday() with [Date] field in the formula

[anonymous008_G]

Thanks, In actual we dont have any date column but i can think creating on which i can use conditional statement. Thanks for the idea