In the previous post we saw how we can add business days into a date to get a new date. This was posted here.

In this post, we will do the same thing but a bit more cleverly.

The logic:
Lets say we have date1 and numberx. Where numberx is the number of business days to add to date1. In the earlier post, we were evaluating which day of the week the start date i.e. “date1” is and subsequently modifying what to add to date1.

Now what if I tell you that we can amend the logic to have our “start date” always as a Monday!! You hear me right !

How do we do it then:

We all know that 1900/1/1 was a Monday. So we find the number of business days from 1900/1/1 until date1, add numberx to it what we have is a start date of 1900/1/1 which is a Monday and a total number of business days from 1900/1/1.

Part 1: Finding the number of business days from 1900/1/1 to date1:

(
FLOOR((Just_a_date__c - DATE(1900,1,1))/7)*5 //number of full weeks * 5
+
CASE(
MOD((Just_a_date__c - DATE(1900,1,1)),7),
                     //number of days left after full weeks count
5,4, //if 5 ie. Saturday then add 4 business days (tuesday to friday)
6,4, //if 6 ie. Sunday then add 4 business days (tuesday to friday)
MOD((Just_a_date__c - DATE(1900,1,1)),7) //else add actual i.e. 1 to 4
)
)

Part 2: Add our numberx field:

(
FLOOR((Just_a_date__c - DATE(1900,1,1))/7)*5 //number of full weeks * 5
+
CASE(
MOD((Just_a_date__c - DATE(1900,1,1)),7),
                     //number of days left after full weeks count
5,4, //if 5 ie. Saturday then add 4 business days (tuesday to friday)
6,4, //if 6 ie. Sunday then add 4 business days (tuesday to friday)
MOD((Just_a_date__c - DATE(1900,1,1)),7) //else add actual i.e. 1 to 4
)
+
Number_of_business_days_to_add__c
)

Part 3: Add the above “total” number of business days to 1/1/1900

DATE(1900,1,1) + 

/*number of business days*/ 

(
FLOOR((Just_a_date__c - DATE(1900,1,1))/7)*5 +
CASE(
MOD((Just_a_date__c - DATE(1900,1,1)),7),
5,4,
6,4,
MOD((Just_a_date__c - DATE(1900,1,1)),7)
)
+
Number_of_business_days_to_all__c
)
+
2*
(FLOOR(
(
FLOOR((Just_a_date__c - DATE(1900,1,1))/7)*5 +
CASE(
MOD((Just_a_date__c - DATE(1900,1,1)),7),
5,4,
6,4,
MOD((Just_a_date__c - DATE(1900,1,1)),7)
)
+
Number_of_business_days_to_all__c
)
/5))

And that’s it!

Cheers…

Advertisements