Hi Jeff, I dived a bit into this with some Math Columns. @Eric_Ruiz_Molero I hope this helps.
So I had a set up of date of week (in text), then start time and end time in numerical.
The magic is mostly here:
WEEKDAY(F)-WEEKDAY(N)+7 brings me to the corresponding weekday of next week.
Then WEEKDAY(F)/WEEKDAY(N) returns something less than 1 for days that have already passed (for example today’s Tuesday, then Sunday and Monday have passed) and more than 1 for days that have not already passed (Wednesday - Saturday).
LOG puts it in a -1 to 1 scale. Anything less than 1 here returns something less than 0, else it’s more than 0. I convert it to 0 and 1 with CEILING.
returns -7 when the day has not already passed (so it’s for this week) and returns 0 when it needs to be next week.
The other 2 columns is just converting the time to the right offset.
Now + Offset minus everything related to hour, minutes and seconds for “now”, then add the corresponding starting hours.
Same setup for the ending time.
As this is another date-time related thing I hope you find this useful as well @Robert_Petitto @Darren_Murphy @Lucas_Pires.
Only thing I haven’t done is if it falls to the current day, then it should move to next week only when “now” is past the start time, but I think it’s a bit complex.