Setting a DateTime to a Weekday in a Formula

This is one of those things that took way too long to sort out. WEEKDAY() is a great formula function, but it only works on Dates. 

Our requirement was to set the value to three days from now, but only if the day is a weekday, and put it in a datetime field.

Thinking it through, they wanted the result to come out as follows.

  • Cases received on Monday are due Thursday (+3 days)
  • Cases received on Tuesday are due Thursday (+3 days)
  • Cases received on Wednesday are due Monday (+5 days, skipping 2 weekend days)
  • Cases received on Thursday are due Tuesday (+5 days, skipping 2 weekend days)
  • Cases received on Friday are due Wednesday (+5 days, skipping 2 weekend days)
  • Cases received on Saturday are due Wednesday (+4 days, skipping 1 weekend day)
  • Cases received on Sunday are due Wednesday (+ 3 days)

WEEKDAY() returns  an integer value, where 1 is Sunday and 7 is Saturday. Therefore:

CASE(WEEKDAY(TODAY()),
4,now()+5,
5,now()+5,
6,now()+5,
7,now()+4,
now()+3
)

Since WEEKDAY() only works on Dates, you need to use TODAY() for that. So this says, "Check what day of the week it is. If it's Day 4 (Wednesday), 5 (Thursday), or 6 (Friday), return now() plus five days. If it's day 7 (Sunday), return now() plus four days. Otherwise, return now() plus three days."

And, generally speaking - use NOW() for datetime, TODAY() for date.

Bonus: Brute force Testing

We found this really confusing, with the awkward numbers for days of the week and all. So to test, here's a way we did it so it's all laid out. In this method we are going to create seven records with a static date that'll stand in for NOW().

On any object in your sandbox, make three fields. The first is a DateTime called Test Case Submit Time. The second is a modified version of the above formula. I called this one Test Calculated Follow Up Time.

CASE(WEEKDAY(DATEVALUE(TEST_CASE_SUBMIT_TIME__c),
4,
TEST_CASE_SUBMIT_TIME__c+5,
5,
TEST_CASE_SUBMIT_TIME__c+5,
6,
TEST_CASE_SUBMIT_TIME__c+5,
7,
TEST_CASE_SUBMIT_TIME__c+4,
TEST_CASE_SUBMIT_TIME__c+3
)

Finally, the third one is optional but super helpful - just convert the Calculated Follow Up Time to a readable weekday. I called this Test Follow Up Day of Week.

CASE(WEEKDAY(DATEVALUE(Test_Calculated_Follow_Up_Time__c)),
1,'Sun',
2,'Mon',
3,'Tue',
4,'Wed',
5,'Thu',
6,'Fri',
7,'Sat',
''
)

Now, create seven records and set the Test Case Submit Time on each one to a different day of the week. You can then get a nice layout that shows what the value would be with different input values. 

 

In this case, when Test Case Submit Time is 8/20/2021, a Friday, the calculation returns 8/25/2021, a Wednesday. In practice, if you used NOW() instead of 8/20/2021 3:42 PM, you would get the same result.


Comments

Popular posts from this blog

"Disjunctions not supported" - Why Custom Metadata and Flow Don't Mix

What Happens To "Inflight" Process Builders When You Activate a new Version?

Update Knowledge Articles in Bulk