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! Go to Solution.
I think this is the solution, appreciate if the expressions could be checked and confirmed.
many Thanks:
Monday
[WEC] =
IF(
WEEKDAY(TODAY()1) > 2,
(TODAY() - WEEKDAY(TODAY()) + 7 + 2),
(TODAY() - WEEKDAY(TODAY()) +2)
)
Tuesday
[WEC] =
IFS(
WEEKDAY(TODAY()) = 3, TODAY(),
WEEKDAY(TODAY()) < 3, (TODAY() - WEEKDAY(TODAY()) + 3),
TRUE, (TODAY() - WEEKDAY(TODAY()) + 7 + 3)
)
Wednesday
[WEC] =
IFS(
WEEKDAY(TODAY()) = 4, TODAY(),
WEEKDAY(TODAY()) < 4, (TODAY() - WEEKDAY(TODAY()) + 4),
TRUE, (TODAY() - WEEKDAY(TODAY()) + 7 + 4)
)
Thursday
[WEC] =
IFS(
WEEKDAY(TODAY()) = 5, TODAY(),
WEEKDAY(TODAY()) < 5, (TODAY() - WEEKDAY(TODAY()) + 5),
TRUE, (TODAY() - WEEKDAY(TODAY()) + 7 + 5)
)
Friday
[WEC] =
IF(
WEEKDAY(TODAY()1) > 6,
(TODAY() - WEEKDAY(TODAY()) + 7 + 6),
(TODAY() - WEEKDAY(TODAY()) +6)
)
Saturday
[WEC] =
IF(
WEEKDAY(TODAY()1) > 7,
(TODAY() - WEEKDAY(TODAY()) + 7 + 7),
(TODAY() - WEEKDAY(TODAY()) +7)
)
Here is an example for your Monday column:
IF(
WEEKDAY(TODAY())>2,
(TODAY() - WEEKDAY(TODAY()) + 7 + 2),
(TODAY() - WEEKDAY(TODAY()) + 2)
)
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
I think this is the solution, appreciate if the expressions could be checked and confirmed.
many Thanks:
Monday
[WEC] =
IF(
WEEKDAY(TODAY()1) > 2,
(TODAY() - WEEKDAY(TODAY()) + 7 + 2),
(TODAY() - WEEKDAY(TODAY()) +2)
)
Tuesday
[WEC] =
IFS(
WEEKDAY(TODAY()) = 3, TODAY(),
WEEKDAY(TODAY()) < 3, (TODAY() - WEEKDAY(TODAY()) + 3),
TRUE, (TODAY() - WEEKDAY(TODAY()) + 7 + 3)
)
Wednesday
[WEC] =
IFS(
WEEKDAY(TODAY()) = 4, TODAY(),
WEEKDAY(TODAY()) < 4, (TODAY() - WEEKDAY(TODAY()) + 4),
TRUE, (TODAY() - WEEKDAY(TODAY()) + 7 + 4)
)
Thursday
[WEC] =
IFS(
WEEKDAY(TODAY()) = 5, TODAY(),
WEEKDAY(TODAY()) < 5, (TODAY() - WEEKDAY(TODAY()) + 5),
TRUE, (TODAY() - WEEKDAY(TODAY()) + 7 + 5)
)
Friday
[WEC] =
IF(
WEEKDAY(TODAY()1) > 6,
(TODAY() - WEEKDAY(TODAY()) + 7 + 6),
(TODAY() - WEEKDAY(TODAY()) +6)
)
Saturday
[WEC] =
IF(
WEEKDAY(TODAY()1) > 7,
(TODAY() - WEEKDAY(TODAY()) + 7 + 7),
(TODAY() - WEEKDAY(TODAY()) +7)
)
That looks like it should work to me.
ChatGPT ๐ค, it didn't get it right first time but after testing each returned solution I found the one which worked the best. Thanks for heading me in the right direction though. Many Thanks.
User | Count |
---|---|
42 | |
30 | |
25 | |
23 | |
13 |