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
Natalia Rex 1Natalia Rex 1 

Help in veryfing existing formula - counting weekdays between two dates

Hi all, some time ago we had implemented a formula that should count a number of days between two dates, to not count weekends. We want to count end date as well. Below is a formula, however when I verify this is a website that counds number of days between dates, it is different than what we get in SF:

Formula:

1 +
(
 (End_date_of_project__c - Start_date_of_project__c) * 5 -
 (MOD(Start_date_of_project__c - DATE(1970,1,4),7) - MOD(End_date_of_project__c - DATE(1970,1,4),7)) * 2
) / 7 -
IF(MOD(End_date_of_project__c - DATE(1970,1,4),7) = 6,1,0) -
IF(MOD(Start_date_of_project__c - DATE(1970,1,4),7) = 0,1,0)


In SF, we got the following:
Start date of project: 22/03/2021
End date of project: 31/12/2021

According to this website that counts dates (https://www.calculator.net/date-calculator.html?today=03%2F22%2F2021&ageat=12%2F31%2F2021&calctype=diff&useholiday=1&hdny=1&hdml=1&hdpd=1&hdmd=1&hdid=1&hdld=1&hdcd=1&hdvd=1&hdtx=1&hdxm=1&n0=&m0=&d0=&n1=&m1=&d1=&n2=&m2=&d2=&n3=&m3=&d3=&n4=&m4=&d4=&n5=&m5=&d5=&n6=&m6=&d6=&n7=&m7=&d7=&n8=&m8=&d8=&n9=&m9=&d9=&n10=&m10=&d10=&n11=&m11=&d11=&n12=&m12=&d12=&n13=&m13=&d13=&n14=&m14=&d14=&n15=&m15=&d15=&n16=&m16=&d16=&n17=&m17=&d17=&n18=&m18=&d18=&n19=&m19=&d19=&x=71&y=21), we get 196 days.

But in Salesforce, based on formula, we get 205. Want to figure out - why?

 

Best Answer chosen by Natalia Rex 1
CharuDuttCharuDutt
Hii Natalia Rex 1
Try The Below Formula 
CASE(MOD(  Start_date_of_project__c - DATE(1985,6,24),7), 
  0 , CASE( MOD(  End_date_of_project__c - Start_date_of_project__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( End_date_of_project__c - Start_date_of_project__c)/7)*5)
Please Mark It As Best Answer If It Helps
Thank You!
 

All Answers

CharuDuttCharuDutt
Hii Natalia Rex 1
Try The Below Formula 
CASE(MOD(  Start_date_of_project__c - DATE(1985,6,24),7), 
  0 , CASE( MOD(  End_date_of_project__c - Start_date_of_project__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( End_date_of_project__c - Start_date_of_project__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( End_date_of_project__c - Start_date_of_project__c)/7)*5)
Please Mark It As Best Answer If It Helps
Thank You!
 
This was selected as the best answer
Natalia Rex 1Natalia Rex 1
Hi CharuDutt, thanks for the quick answer. I still get, however 205 - same answer as the previous formula. I'm thinking it may have to do with it being rounded up, but I am not sure why it would round up until 205 from 196?
Muhammad Ahmad 16Muhammad Ahmad 16
we used different calculators but a good one is on the website Calculators pro (http://www.calculatorspro.com)