function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Stephen02Stephen02 

Calculate date based on day of week

My org and all user locales are Eastern Standard Time. The formula below essentially says, if it is before 1PM, then show a date of today, but if it is after 1PM show a date of tomorrow.  Now I need to add a condition that says, if Today equals "Saturday" or "Sunday" then return a date of Monday.

IF( NOW()  <  DATETIMEVALUE(TODAY())+(20/24) , TEXT(YEAR(TODAY())) + LPAD(TEXT(MONTH(TODAY())),2,"0") + LPAD(TEXT(DAY(TODAY())),2,"0"), TEXT(YEAR(TODAY()+1)) + LPAD(TEXT(MONTH(TODAY()+1)),2,"0") + LPAD(TEXT(DAY(TODAY()+1)),2,"0"))
Best Answer chosen by Stephen02
mritzimritzi
CASE( MOD( DAY(
               IF( NOW() < DATETIMEVALUE(TODAY())+20/24, TODAY(), TODAY()+1 )
      ),7),
	6, TODAY () +2, 
	7, TODAY() +1,
IF( NOW()<DATETIMEVALUE(TODAY())+20/24, TODAY(), TODAY()+1 ))

Breakdown:
IF( NOW()<DATETIMEVALUE(TODAY())+20/24, TODAY(), TODAY()+1 )   gives you either today's or tomorrow's date depending on your logic.

DAY gives the date of current month (based on the date returned by IF condition)

MOD (day,7 ) gives the week day of the date returned by (  DAY ).

Finally, CASE gives monday's date (if value returned by MOD is saturday or sunday), if none of the condition matches then the date actually calculated by innermost IF is returned.

If this solution works for you, please mark this as Best Answer.

All Answers

Parker EdelmannParker Edelmann
You could try this:
IF(MOD(TODAY() - DATE(1900, 1, 13),7) = 0, TODAY() + 2,
   IF(MOD(TODAY() - DATE(1900, 1, 13),7) = 1, TODAY() + 1,
      IF(NOW() < DATETIMEVALUE(TEXT(TODAY()) & ' 13:00:00'), TODAY(), TODAY() + 1
       )
    )
)
The return type is Date, so you will need to set the field accordingly. Let me know if this helps.
 
Thanks,
Parker
mritzimritzi
CASE( MOD( DAY(
               IF( NOW() < DATETIMEVALUE(TODAY())+20/24, TODAY(), TODAY()+1 )
      ),7),
	6, TODAY () +2, 
	7, TODAY() +1,
IF( NOW()<DATETIMEVALUE(TODAY())+20/24, TODAY(), TODAY()+1 ))

Breakdown:
IF( NOW()<DATETIMEVALUE(TODAY())+20/24, TODAY(), TODAY()+1 )   gives you either today's or tomorrow's date depending on your logic.

DAY gives the date of current month (based on the date returned by IF condition)

MOD (day,7 ) gives the week day of the date returned by (  DAY ).

Finally, CASE gives monday's date (if value returned by MOD is saturday or sunday), if none of the condition matches then the date actually calculated by innermost IF is returned.

If this solution works for you, please mark this as Best Answer.
This was selected as the best answer