# Return last week's date based on string

Topic Labels: Data Formulas
Solved
1035 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hey all 👋

The subject may be confusing.

Basically, I have a column {Day}, {Day} is a single select field.

The options in {Day} includes the following strings:

• monday
• tuesday
• wednesday
• thursday
• friday
• saturday
• sunday

I want a formula field that returns the date, based on {Day}.
For example, this post is written on 13 Dec 2023, Wednesday.
If {Day} is "monday" then the cell should return 11 Dec 2023. (last monday)
If {Day} is "wednesday" then the cell should return 13 Dec 2023 (today)
If {Day} is "thursday" then cell should return 7 Dec 2023 (last thursday)

how should I write this formula?

1 Solution

Accepted Solutions
5 - Automation Enthusiast
DATETIME_FORMAT(
IF(
{Day} = {Today},
TODAY(),
SWITCH(
{Day},
"Monday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 2, 1, -6), 'days'),
"Tuesday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 3, 2, -5), 'days'),
"Wednesday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 4, 3, -4), 'days'),
"Thursday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 5, 4, -3), 'days'),
"Friday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 6, 5, -2), 'days'),
"Saturday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 7, 6, -1), 'days'),
"Sunday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 1, 7, 0), 'days')
)
),
'YYYY-MMM-DD'
)
2 Replies 2
5 - Automation Enthusiast
DATETIME_FORMAT(
IF(
{Day} = {Today},
TODAY(),
SWITCH(
{Day},
"Monday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 2, 1, -6), 'days'),
"Tuesday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 3, 2, -5), 'days'),
"Wednesday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 4, 3, -4), 'days'),
"Thursday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 5, 4, -3), 'days'),
"Friday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 6, 5, -2), 'days'),
"Saturday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 7, 6, -1), 'days'),
"Sunday", DATEADD(TODAY(), -WEEKDAY(TODAY()) + IF(WEEKDAY(TODAY()) >= 1, 7, 0), 'days')
)
),
'YYYY-MMM-DD'
)
11 - Venus

Good.
Some formulas are a little simpler.

``````SWITCH(
{Day},