function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Alpana RawatAlpana Rawat 

how to calculate exact days , hours and minute difference between two dates excluding weekends

how to calculate exact days , hours and minute difference between two dates  excluding weekends 
for example:
 if date1: 5-feb-2019 11:15 am and date2: 6-feb:2019 1:15 pm so output would be 1 days 2hours 00 mins
Best Answer chosen by Alpana Rawat
Alpana RawatAlpana Rawat
Formula with lil correction..
TEXT(ROUND((CASE(MOD( DATEVALUE([Account].CreatedDate) - DATE(1900, 1, 8),7), 
0 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 

(FLOOR(( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) )/7)*5) ) 
-1 
+(DATETIMEVALUE(DATEVALUE([Account].CreatedDate)) - DATETIMEVALUE(DATEVALUE([Account].CreatedDate)) 
+ DATETIMEVALUE(DATEVALUE([Account].Pending_Approval_Time__c)) - DATETIMEVALUE(DATEVALUE([Account].Pending_Approval_Time__c))),0)) 

& " Days " & 
TEXT( IF(ROUND(MOD(([Account].Pending_Approval_Time__c- [Account].CreatedDate)*24,24),0)=24,0,
ROUND(MOD(([Account].Pending_Approval_Time__c- [Account].CreatedDate)*24,24),0) 
) )&" Hours " & 
TEXT( IF(Round(MOD(([Account].Pending_Approval_Time__c- [Account].CreatedDate)*1440,60),0)=60,0,
Round(MOD(([Account].Pending_Approval_Time__c - [Account].CreatedDate)*1440,60),0) 
)) &" Minutes "

All Answers

abhishek singh 497abhishek singh 497
Hello Alpana,
you can try below formula to get exact output you want but not sure this will exclude weekends.

TEXT(
FLOOR(End_Date__c - Start_Date__c)
) & " Day(s) " &
TEXT(
ROUND(MOD((End_Date__c - Start_Date__c)*24,24),0)
) &" Hour(s) " &
TEXT(
ROUND(MOD((End_Date__c - Start_Date__c)*1440,60),0)
) &" Minute(s) "

Please let me know it's helpful.
Please mark it has the best answer if solves your query.
Raj VakatiRaj Vakati
Try this

https://help.salesforce.com/articleView?id=000004526&type=1
 
ROUND( 8 * (
   ( 5 * FLOOR( ( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )

 
Alpana RawatAlpana Rawat

@Raj Thanks for replying.. however  its not working properly...
its only calculating days. not time..
if date2 is : 2/5/2019 5:30 AM and date1 is 2/5/2019 3:41 AM .. the output of your formula is 0. its not calculating hours and mins.
abhishek singh 497abhishek singh 497
Hello Alpna did you tried logic which I have written??
Alpana RawatAlpana Rawat
Hello @abhishek.. thanks for replying.. the output is coming correct.. however its not excluding the weekends..
i have modify it to exclude the weekends.
PFB the same...
TEXT(ROUND((CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 
0 , CASE( MOD( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 

(FLOOR(( DATEVALUE(Pending_Approval_Time__c) - DATEVALUE(CreatedDate) )/7)*5) ) 
-1 
+(DATETIMEVALUE(DATEVALUE(CreatedDate)) - DATETIMEVALUE(CreatedDate) 
+ DATETIMEVALUE(Pending_Approval_Time__c) - DATETIMEVALUE(DATEVALUE(Pending_Approval_Time__c))),0)) & " Days " & 

TEXT( 
ROUND(MOD(( Pending_Approval_Time__c - CreatedDate)*24,24),0) 
) &" Hours " & 

TEXT( 
Round(MOD((Pending_Approval_Time__c - CreatedDate)*1440,60),0) 
) &" Minutes "
Alpana RawatAlpana Rawat
thanks alot @abhishek for helping me out :)
abhishek singh 497abhishek singh 497
Hello Alpana,
Yes you are correct mine formula does not exclude weekends.
Thanks for modifying it is and happy to learn something new from you.
All the best.

Thanks & Regards,
Abhishek Singh.
 
abhishek singh 497abhishek singh 497
If you find my answer best, please mark it the best answer so that it can help someone in the future when looking for something similar.
Thanks & Regards,
Abhishek Singh.
Alpana RawatAlpana Rawat
Formula with lil correction..
TEXT(ROUND((CASE(MOD( DATEVALUE([Account].CreatedDate) - DATE(1900, 1, 8),7), 
0 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 

(FLOOR(( DATEVALUE([Account].Pending_Approval_Time__c) - DATEVALUE([Account].CreatedDate) )/7)*5) ) 
-1 
+(DATETIMEVALUE(DATEVALUE([Account].CreatedDate)) - DATETIMEVALUE(DATEVALUE([Account].CreatedDate)) 
+ DATETIMEVALUE(DATEVALUE([Account].Pending_Approval_Time__c)) - DATETIMEVALUE(DATEVALUE([Account].Pending_Approval_Time__c))),0)) 

& " Days " & 
TEXT( IF(ROUND(MOD(([Account].Pending_Approval_Time__c- [Account].CreatedDate)*24,24),0)=24,0,
ROUND(MOD(([Account].Pending_Approval_Time__c- [Account].CreatedDate)*24,24),0) 
) )&" Hours " & 
TEXT( IF(Round(MOD(([Account].Pending_Approval_Time__c- [Account].CreatedDate)*1440,60),0)=60,0,
Round(MOD(([Account].Pending_Approval_Time__c - [Account].CreatedDate)*1440,60),0) 
)) &" Minutes "
This was selected as the best answer