ShowAll Questionssorted byDate Posted
cldave

# Formula to calculate days between 2 dates minus some exceptions

Hi i would like to know if it's possible for a formula to calculate the # of days between 2 dates with a couple exceptions.

Example count days between Jan 1st 2013 - Dec 31st 2013 minus christmas day(dec 25) , new years (dec 31st).....

Any workaround/idea would be appreciated.

thx
Hd037

so are u basicaly want a formulla for it or are you looking for apex code to do it..

thanks and regards..

Agi
Hi cldave,

the following formula exceeds the 5000 limit, but maybe somebody else here has an idea how to reduce it:

CASE(MOD(Start__c - DATE(1985,6,24),7),
0 , CASE(MOD(End__c - Start__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(End__c - Start__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(End__c - Start__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(End__c - Start__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(End__c - Start__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(End__c - Start__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(End__c - Start__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((End__c - Start__c)/7)*5)
-
IF(NOT(OR(
MOD( Start__c - DATE (2000 ,1, 1) ,7)=0,
MOD( Start__c - DATE (2000 ,1, 1) ,7)=1)), 1, 0)

-
((
IF (AND(Start__c< Date(Year(End__c),01,01), End__c-Start__c>=(Date(Year(End__c),01,01)-Start__c)),1,0)
+IF(AND(Start__c< Date(Year(End__c),01,20), End__c-Start__c>=(Date(Year(End__c),01,20)-Start__c)),1,0)
+IF(AND(Start__c< Date(Year(End__c),02,17), End__c-Start__c>=(Date(Year(End__c),02,17)-Start__c)),1,0)
+IF(AND(Start__c< Date(Year(End__c),05,26), End__c-Start__c>=(Date(Year(End__c),05,26)-Start__c)),1,0)
+IF(AND(Start__c< Date(Year(End__c),06,04), End__c-Start__c>=(Date(Year(End__c),06,04)-Start__c)),1,0)
+IF(AND(Start__c< Date(Year(End__c),09,01), End__c-Start__c>=(Date(Year(End__c),09,01)-Start__c)),1,0)
+IF(AND(Start__c< Date(Year(End__c),11,27), End__c-Start__c>=(Date(Year(End__c),11,27)-Start__c)),1,0)
+IF(AND(Start__c< Date(Year(End__c),12,25), End__c-Start__c>=(Date(Year(End__c),12,25)-Start__c)),1,0))*if(((End__c-Start__c)/365)<1, 1, Floor((End__c-Start__c)/365)))
cldave
Hd037 as title says i would like a formula and ty agi for that formula, it gives me a place to start, hopefully someone knows how to reduce it :)