You need to sign in to do that
Don't have an account?
Bablu
Calculating difference between 2 date fileds (with out weekends) in HH:MM format
Hi, i want to calculate the diefference between 2 date fileds with out weekends (satday and sunday)
Here are my date fields
1. Date/Time opened
2. Accepted date/time
Thanks in advance for your help
Bablu
Here are my date fields
1. Date/Time opened
2. Accepted date/time
Thanks in advance for your help
Bablu
Maybe you need something like this:
As a common practice, if your question is answered, please choose 1 best answer.
But you can give every answer a thumb up if that answer is helpful to you.
Thanks,
Alex
Try this
Thanks,
Alex
these two are date/time fileds
Error: Incorrect parameter type for operator '-'. Expected Number, DateTime, received Date
CASE(MOD( CreatedDate - DATE(1985,6,24),7),
0,CASE( MOD(Case_Accepted_Date__c - CreatedDate,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1,CASE( MOD(Case_Accepted_Date__c - CreatedDate,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2,CASE( MOD(Case_Accepted_Date__c - CreatedDate,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3,CASE( MOD(Case_Accepted_Date__c - CreatedDate,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4,CASE( MOD(Case_Accepted_Date__c - CreatedDate,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5,CASE( MOD(Case_Accepted_Date__c - CreatedDate,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6,CASE( MOD(Case_Accepted_Date__c- CreatedDate,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)+ (FLOOR((Case_Accepted_Date__c - CreatedDate)/7)*5)
Thanks,
Alex
Currently with the above code it is displying in days, but it should disply in HH:MM format. coul dyou please help with this too
Here case accepted date is 28th, so i need to disply the diffence b/w Date/time opened and Case accepted date in Hours: Minutes format
and second formula
But I recond you to use BusinessHours Class. There are method diff(businessHoursId, startDate, endDate) with return difference between a start and end Datetime based on a specific set of business hours. At first you need specify businees hours.
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_businesshours.htm#apex_System_BusinessHours_diff
This method you can use in very simple trigger
Thanks,
Alex
Can we modify the below snippet to convert in HH:MM format ? its not based on Rog business hours. formally i neeed to calculate the difference between the tow dates in HH: MM formats excluding weekeds.
Now the below formula working fine for days (displying diff b/w two dates with out weekend days)
CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 0,CASE( MOD(DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1,CASE( MOD(DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2,CASE( MOD(DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3,CASE( MOD(DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4,CASE( MOD(DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5,CASE( MOD(DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6,CASE( MOD(DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR((DATEVALUE(Case_Accepted_Date__c) - DATEVALUE(CreatedDate))/7)*5)
Thanks you very much for your help, Alex
Babulu
I recomended you to use apex trigger for this requirement.
1. You need setup Business hours. For it go to Setup -> Company Profile -> Business Hours
Click Edit on Default business hours and setup as you want. And do not forgot aboud time zone(Time Zone). Business hours recalculated based on Time Zone in Organization Business Hours.
3. Next create trigger for your sobject
as example
I verified, and this code work correct.
Thanks,
Alex