+ Start a Discussion
Mohan Raj 33Mohan Raj 33 

How to chenge this formula for working correctly in within week days different also?

I have the formula to calculate the  date/time difference in between two days the diffference are only the nomber of week days only. But here what's the wrong is it's not working in with in same week and the future days to the date/time field of I am using here. My formula is,
(CASE(MOD( DATEVALUE(Calculating_Date__c) - DATE(1985,6,24),7), 
  0 , CASE( MOD( NOW() - Calculating_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( NOW() - Calculating_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( NOW() - Calculating_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( NOW() - Calculating_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( NOW() - Calculating_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( NOW() - Calculating_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( NOW() - Calculating_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999))

So here I want to change the formula to working in with future and the current week also in correct manner of working to calculating to the weekdays only(i.e Monay to friday) only.For answer's thanks in advance. thank you Mohan
Best Answer chosen by Mohan Raj 33
Akhil AnilAkhil Anil
Hi Mohan

Use the below formula. It should work.
 
(CASE(MOD( DATEVALUE(Calculating_Date__c) - DATE(1985,6,24),7), 
  0 , CASE( MOD( TODAY() - DATEVALUE(Calculating_Date__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( TODAY() - DATEVALUE(Calculating_Date__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( TODAY() - DATEVALUE(Calculating_Date__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( TODAY() - DATEVALUE(Calculating_Date__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( TODAY() - DATEVALUE(Calculating_Date__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( TODAY() - DATEVALUE(Calculating_Date__c) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( TODAY() - DATEVALUE(Calculating_Date__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999)
  +
  (FLOOR(( TODAY() - DATEVALUE(Calculating_Date__c) )/7)*5))

Hope that helps !