Please help me to understand the code.

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

Help

Accepted answers

binu_acs

@anonymous008_G

put the parts of your expression in a formula tool

DateExplained_19022024.yxmd

binu_acs

@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

All comments

binu_acs

@anonymous008_G

put the parts of your expression in a formula tool

DateExplained_19022024.yxmd

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?

binu_acs

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?

binu_acs

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703