You need to sign in to do that
Don't have an account?
Alpana 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
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
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
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.
https://help.salesforce.com/articleView?id=000004526&type=1
@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.
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 "
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.
Thanks & Regards,
Abhishek Singh.
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 "