function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
cldavecldave 

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
Hd037Hd037

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

please b specify so anyone can understand your basic problem..

 

thanks and regards..
AgiAgi
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)))
cldavecldave
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 :)