Help

Return last week's date based on string

Topic Labels: Data Formulas
Solved
Jump to Solution
679 2
cancel
Showing results for 
Search instead for 
Did you mean: 
DennisyuTSS1
5 - Automation Enthusiast
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
DennisyuTSS1
5 - Automation Enthusiast
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'
)

See Solution in Thread

2 Replies 2
DennisyuTSS1
5 - Automation Enthusiast
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'
)
Sho
11 - Venus
11 - Venus

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")
)