Leap Year

[novice1]

Hi all,

I have the Period Start and Period End Date as I need to extract data for whole year up to and including yesterday and compare this to the same period last year.

I am using following formula to work out date today last year

DateTimeAdd(DateTimeToday(), -364, "days")

this is taking into consideration that this year is leap year. Therefore if today is Friday 11/12/2020 - same Friday last year was 13/12/2019

Everything is working as intended, except i can see issue occurring when i will need to calculate the same for non-non leap year. 

Is there a way to design this flexibly, even for longer time periods?

Please see my formulas as they are at the moment down below:

[Qiu]

@novice1 

By "work out date today last year", you mean it if today is Friday 11/12/2020, you want the output to be '11/12/2019'?

[novice1]

hi,

no. if you look at the calendar this Friday last year was 13/12/2019.

so if i take say any given Friday in 2020 say 11/12/2021, the same Friday in 2019 would have been 13/12/2020 (extra 2 days), as 2020 is a leap year.

but if i take Friday 25/01/2019, the same Friday in 2018 was 26/01/2018 (extra 1 day).

Just trying to make my report future proof.🙂

The idea is to modify reports as little as possible once they are scheduled to run at certain times.

[Qiu]

@novice1 
So basically

1. Give a date. 11/12/2019

2. Look the the date that is having same WeekDay (Monday, Tues, and so on) in past and futhur one year with minimum Date Difference?

If that is the case, we need to use more tools.

[novice1]

@Qiu 

Pretty much. 

Except i mainly take today and then workout today last year.

This is used to analyse Company Sales performance Year on Year.

What would you suggest? the attached workflow example feeds into my SQL Query and then I use 'Modify SQL Query' and replace certain strings with Year start, Year end dates

[Tyro_abc]

Hi

Are you trying to add a condition that  if

Leap Year --------> subtract 365 days

Non-Leap Year ---> subtract 364 days.

I just added a condition to determine if the current year is "Leap year" and accordingly changed how many days to subtract. Let me know for any explanation. 

Best Regards

Arundhuti

[novice1]

@Tyro_abc 

Thank you. I have modified my formula based on your suggestion, but now I am getting Malformed If statement error. Would you be able to let me know what I am missing?

Firstly i used formula to determine Year now

DateTimeYear(DateTimeToday())

and then tried to apply formula below

If mod([Year],4) then DateTimeAdd(DateTimeToday(), -365, "days")
elseif mod([Year],100) then
DateTimeAdd(DateTimeToday(), -364, "days")
elseif mod([Year],400) then
DateTimeAdd(DateTimeToday(), -365, "days")
endif

[Qiu]

@novice1 
Credits goes to @Tyro_abc , but I fixed the erro as below

[novice1]

@Qiu 

still get error. I need date to be as date not V_String

If mod([Year],4) then DateTimeAdd(DateTimeToday(), -365, "days")
else if mod([Year],100) then
DateTimeAdd(DateTimeToday(), -364, "days") else//elseif mod([Year],400) then
DateTimeAdd(DateTimeToday(), -365, "days")
endif

[Qiu · Accepted solution]

@novice1 
Miss out one endif...Sorry.