Formula to calculate last day of February each year

[sophievanluin]

Hi there,

I'm building a workflow where I need to make several calculations which contain dynamic dates.

The formulas I used was:

[Feb Cur Year]

IF [Start] > DateTimeFormat(DateTimeAdd([Reporting Date],0, "years"), "%Y-02-29") THEN 0

ELSEIF !IsNull([End]) AND [End] <DateTimeFormat(DateTimeAdd([Reporting Date],0, "years"), "%Y-02-29") THEN 0

ELSE [FTE]*100
ENDIF

[Feb Next Year]

IF [Start] > DateTimeFormat(DateTimeAdd([Reporting Date],1, "years"), "%Y-02-28") THEN 0

ELSEIF !IsNull([End]) AND [End] <DateTimeFormat(DateTimeAdd([Reporting Date],1, "years"), "%Y-02-28") THEN 0

ELSE [FTE]*100
ENDIF

In the formulas above [Start] represents the starting date and [End] the ending date. [Reporting Date] represents that last day of the month that I would like to report on. So all data is related to this date.

See below some example data:

Name	Start	End	Reporting Date	FTE	Feb Cur Year	Mar Cur Year	Feb Next Year	Mar Next Year
Person 1	2020-01-01	2021-02-28	2020-11-30	1	100	100	100	0
Person 2	2020-01-01	2021-01-01	2020-11-30	0.8	80	80	0	0

In the scenario above, 'Feb Cur Year' is 2020-02-29 and 'Feb Next Year' is 2021-02-29

Now I've manually written the last days of the months in the formula tools, however, this is not applicable if I take for example 2021 and 2022, since both years go till the 28th.

How do change my formula so it automatically picks the last day of February for that year, so that I don't have to change it manually?

[JosephSerpis]

Hi @sophievanluin you can use the function datetimetrim which has the arguement of lastofthemonth so you could add that into your formula. Attached a workflow that use the datetimetrim function added to your formula.

[MarqueeCrew · Accepted solution]

@sophievanluin 

Did you know that March 1st is the day after the last day of February?

If you have a known YEAR (string),

DateTimeAdd([Year]+"-03-01",-1,"Days") 

That is the last day of February ...

Cheers,

mark