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!!

[Ladarthure]

Hi @kherty,

to do so, I wouyld use the week number and compare to the "today", here is a sample workflow

[Maskell_Rascal · Accepted solution]

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

[kherty]

@Ladarthure  Thanks for this!  I tried it and it appears to work well.  It did drop the Sunday of last week though.  Any idea of how I could get this included?

[kherty]

@Maskell_Rascal  Thanks!  This works.

I appreciate everybody's help here.