ShowAll Questionssorted byDate Posted
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

Bablu
Alexander Tsitsura
Hi Bablu,

Maybe you need something like this:
```public static Integer differentWithoutWeekends(Datetime opened, Datetime accepted) {
Integer days = opened.date().daysBetween(accepted.date());

// remove all weekends days
days -= ((Integer) Math.floor((days / 7)) * 2);

if (opened.format('E') != 'Sat') {
days -= 1;
} else if (opened.format('E') != 'Sun') {
days -= 2;
}

return days;
}```

Thanks,
Alex
Bablu
Hi Alexander Tsitsura, i need a formula to do this
Alexander Tsitsura
Hi

Try this
```CASE(MOD(  OPENED_DAY__c - DATE(1985,6,24),7),
0 , CASE( MOD( ACCEPTED_DAY__c - OPENED_DAY__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( ACCEPTED_DAY__c - OPENED_DAY__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( ACCEPTED_DAY__c - OPENED_DAY__c,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( ACCEPTED_DAY__c - OPENED_DAY__c,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( ACCEPTED_DAY__c - OPENED_DAY__c,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( ACCEPTED_DAY__c - OPENED_DAY__c,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( ACCEPTED_DAY__c - OPENED_DAY__c,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( ACCEPTED_DAY__c - OPENED_DAY__c )/7)*5)```

Thanks,
Alex
Bablu

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)

Alexander Tsitsura
Ok, you can convert DateTime to Date, for this use DATEVALUE(Date Time value)

```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,
Alex
Bablu
Thanks for the solution, Alexander.  alomst got it except HH:MM format.

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
Alexander Tsitsura
Sorry but you can not create formula for time data type. But you can modify exsisting formula for determina actual minutes and the create new formula with type text that have format total hours: total minutes.
Alexander Tsitsura
First formula fot output HH:MM - should be text type
`TEXT( ROUND(diff__c / 60, 0)) & ':' &  TEXT(MOD(diff__c,60))`

and second formula

```8 * 60 *
CASE(MOD(  DATEVALUE(CreatedDate) - DATE(1985,6,24),7),
0, CASE( MOD( date_2__c - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( date_2__c - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( date_2__c - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( date_2__c - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( date_2__c - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( date_2__c - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( date_2__c - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( date_2__c - date_1__c )/7)*5)```

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.

This method you can use in very simple trigger

Thanks,
Alex
Bablu
Hi Alex, 1st,2nd  fromulas are  not working for me. is there any other way apart from writing class/trigger ?

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
Alexander Tsitsura
Hi Bablu,

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.
2. Next create new text field(maybe Time) on you object(trigger will be populate this field).
3. Next create trigger for your sobject
as example
```trigger CalcBusinessHours on Case (before insert, before update) {