This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Help

Return last week's date based on string

Topic Labels: Data Formulas
Solved
Jump to Solution
791 2
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
DennisyuTSS1
5 - Automation Enthusiast
5 - Automation Enthusiast
See Solution in Thread

‎Dec 12, 2023 08:12 PM - edited ‎Dec 12, 2023 08:22 PM

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?

0 Kudos
Reply
1 Solution

Accepted Solutions
DennisyuTSS1
5 - Automation Enthusiast
5 - Automation Enthusiast
Solved See Solution in Thread

‎Dec 12, 2023 11:06 PM

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

0 Kudos
Reply
2 Replies 2
DennisyuTSS1
5 - Automation Enthusiast
5 - Automation Enthusiast
Solved See Solution in Thread

‎Dec 12, 2023 11:06 PM

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'
)
0 Kudos
Reply
Sho
11 - Venus
11 - Venus
See Solution in Thread

‎Dec 12, 2023 11:57 PM

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
Copyright © 2024 Khoros, LLC