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:
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.
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.
Hi! This is great stuff. You've done a great job with this blog.ReplyDelete
This comment has been removed by the author.ReplyDelete