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!

date formulas.jpg

Christina_H

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")