Formula Logic - Set Date Range

kherty

I apologize if this has been answered in the past, but I cannot seem to find a good way to automate this formula tool.  I run a report M-F of the current week and always want to provide date ranges for prior week and next week.

If [Date] >= "2020-05-24" and [date] <= "2020-05-30" then "NEXT WEEK"
elseif [Date] >= "2020-05-17" and [date] <= "2020-05-23" then "CURRENT WEEK"
elseif [Date] >= "2020-05-10" and [date] <= "2020-05-16" then "LAST WEEK"
elseif [Date] >= "2020-05-03" and [date] <= "2020-05-09" then "PRIOR WEEK"
else Null()
endif

Is there a way to use DateTimeToday() so I don't have to manually roll these dates each Monday?

Thanks!!

Maskell_Rascal

Hi @kherty 

The attached should work for you. I'm calculating the week start date for both the Date column and for the current week. You can then use a DateTimeDiff formula to calculate the difference in days between each week's start date and call out the appropriate week identifier.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Formula Logic - Set Date Range.yxmd