You need to sign in to do that
Don't have an account?
Stephen02
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"))
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"))
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
Thanks,
Parker
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.