Skip to main content

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?

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

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

 

Reply


Terms & Conditions