If not today then next week day

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:

MM_Group_0-1702401763925.png

MM_Group_1-1702401888466.png

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 Solved
0 5 275
1 ACCEPTED 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)
)

View solution in original post

5 REPLIES 5
Top Labels in this Space