Need help with dynamic date formulas

[whope]

Hi everyone.

I have a workflow with the following data formulas. Prior to 2025, it worked well, but since the new year, it's off.

Basically, the 'End of Week' formula (#4) becomes the date later in the workflow, and everything from that week (Saturday-Friday) is grouped into that date. That date should always be a Friday, but after January 3, it starts to show as Sundays. I changed the '4' in formula 4 to '2,' which corrected all 2025 dates to Fridays, but then it threw off 2024 data.

I'm hoping to replace the current filters with new dynamic filters that will avoid this issue in future years.

Thanks in advance!

[SPetrie]

Assuming your weeks starts on a monday, this formula will get you the Monday that starts the week for any date you give it. You can do datetimeadd to get the day of the week you want from there.

DateTimeAdd([TestDate],1-ToNumber((Replace(tostring(DateTimeFormat([Testdate],"%u")),"0","7"))),"days")

I think calculating the week and then adding it to the date is causing your issues here.

[Christina_H · Accepted solution]

Your formulas assume that 1st January is always on the same day.  You need to make them dynamic to handle different days.

You stated that your weeks are grouped Sat-Fri, these formulas will give you the dates of the Saturday and Friday defining each week.

Start of Week: DateTimeAdd([Date],-MOD(ToNumber(DateTimeFormat([Date],'%w'))+1,7),"day")

End of Week: DateTimeAdd([Start of Week],6,"day")