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)
)
User | Count |
---|---|
35 | |
31 | |
30 | |
18 | |
17 |