Return last week's date based on string

[DennisyuTSS1]

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?

[DennisyuTSS1 · Accepted solution]

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'

)

[Sho]

Good.
Some formulas are a little simpler.

SWITCH(
  {Day},
  "Monday", DATEADD(DATETIME_PARSE(WEEKNUM(TODAY()),"w"),1,"day"),
  "Tuesday", DATEADD(DATETIME_PARSE(WEEKNUM(TODAY()),"w"),2,"day"),
  "Wednesday", DATEADD(DATETIME_PARSE(WEEKNUM(TODAY()),"w"),3,"day"),
  "Thursday", DATEADD(DATETIME_PARSE(WEEKNUM(TODAY()),"w"),4,"day"),
  "Friday", DATEADD(DATETIME_PARSE(WEEKNUM(TODAY()),"w"),5,"day"),
  "Saturday", DATEADD(DATETIME_PARSE(WEEKNUM(TODAY()),"w"),6,"day"),
  "Sunday", DATEADD(DATETIME_PARSE(WEEKNUM(TODAY()),"w"),7,"day")
)