LinkedIn
Twitter
Bit of a tricky one this.
I need an expression to return data for TODAY() and then after midnight as the day changes return data for the same day next week. Example:
If today is Monday I want to see data for the current Monday (which is 11th December for the example), but when midnight comes and goes that same expression returns data for the following Monday ( which should be 18th December). The same expression should work for all week days, see the screenshot of the same thing in Microsoft Access and the expressions which run it:
As you will see, each text box holds an expression, the green text is the current day (tues) which will return data from next tues after midnight.
Here's what I have wich doesn't work.
IF(
HOUR(NOW()) < 24,
TODAY(),
DATE(
YEAR(TODAY()),
MONTH(TODAY()),
DAY(TODAY()) + 7
)
)
Solved
1 ACCEPTED SOLUTION
Thank you @Landan_QREW.
If this is Monday:
[WEC] =
IF(
WEEKDAY(TODAY()) > 2,
(TODAY() - WEEKDAY(TODAY()) + 7 + 2),
(TODAY() - WEEKDAY(TODAY()) + 2)
)
Does this work for Tuesday:
[WEC] =
IF(
WEEKDAY(TODAY()) > 2,
(TODAY() - WEEKDAY(TODAY()) + 7 + 3),
(TODAY() - WEEKDAY(TODAY()) + 3)
)
Here is the result for all days;
Nearly correct. But It should return:
Monday [WEC] = 18/12/2023 (Next Monday because we have surpassed Monday this week)
Tuesday [WEC] = 19/12/2023 (Next Tuesday because we have surpassed Tuesday this week)
Wednesday [WEC] = 13/12/2023 ( Today because we haven't surpassed todays date.
Thursday [WEC] = 14/12/2023 (tomorrow)
Friday [WEC] = 15/12/2023
Many Thanks
