I have a column named [week of the day] enumtype list.
I want to get ang actual date value to the selected days of the week. But i want to select to convert text to date is the most recent date from today excluding those expired dates.
In this example. the day should be converted to date is wednesday = 05/30/2023.
the 2nd example to convert to date should be friday = 06/02/2023
I don't understand exactly what you're seeking. This partial draft may point you in a helpful direction.
{start date} +
SWITCH(
INDEX(
[week of the day]],
COUNT(
[week of the day]]
)
),
"Monday", 1
"Tuesday", 2
...
)
Hi. here are the expected output for my question. selecting the neariest day from today but greater than today.
Here's a conceptual description of an approach. You can work through all the syntactical details.
SWITCH(
TEXT([Weekday]),
"Monday", TODAY() - WEEKDAY(TODAY()) + 2,
"Tuesday", TODAY() - WEEKDAY(TODAY()) + 3,
"Wednesday", TODAY() - WEEKDAY(TODAY()) + 4,
"Thursday", TODAY() - WEEKDAY(TODAY()) + 5,
"Friday", TODAY() - WEEKDAY(TODAY()) + 6,
"Saturday", TODAY() - WEEKDAY(TODAY()) + 7,
"Sunday", TODAY() - WEEKDAY(TODAY()) + 1,
""
)
User | Count |
---|---|
42 | |
29 | |
28 | |
14 | |
14 |