Salesforce Formula – Add X number of days to a date ensuring the date does not fall on weekends

Requirement: Create a formula field that calculates a date 30 days from today and ensures that the date does not fall on a weekend. If the calculated date lands on a Saturday or Sunday, the formula returns the following Monday.

CASE( 
	MOD( (TODAY()  + 30) - DATE(1900, 1, 7), 7), 
	0, (TODAY()  + 31), /* Sunday -> Monday */
	1, (TODAY()  + 30), /* Monday -> No Change */
	2, (TODAY()  + 30), /* Tuesday -> No Change */
	3, (TODAY()  + 30), // Wednesday -> No Change */
	4, (TODAY()  + 30), // Thursday -> No Change */
	5, (TODAY()  + 30), // Friday -> No Change */
	6, (TODAY()  + 32), // Saturday -> Monday */
	TODAY()  + 30       // Default
)

Explanation

1. Day of the Week Calculation:
   • (TODAY() + 30) - DATE(1900, 1, 7): This calculates the number of days from January 7, 1900 (a base date which is a Sunday) to the date 30 days from today.
   • MOD(..., 7): This computes the remainder when dividing by 7, which results in an integer between 0 and 6 representing the day of the week (0 for Sunday, 1 for Monday, etc.).
2. Adjust the Date Based on the Day of the Week:
   • If 30 days from today is a Sunday (0), add 31 days to move to Monday.
   • If it’s a Monday (1), Tuesday (2), Wednesday (3), Thursday (4), or Friday (5), add 30 days, meaning no change in the day of the week.
   • If it’s a Saturday (6), add 32 days to move to Monday.
   • The default action is also to add 30 days, which applies if none of the other cases match (although logically, one of them always will).