+ Start a Discussion
BabluBablu 

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
Alexander TsitsuraAlexander 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;
}

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
BabluBablu
Hi Alexander Tsitsura, i need a formula to do this
Alexander TsitsuraAlexander 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
BabluBablu
Him thanks for your reply. i am getting error message . could you please help.

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 TsitsuraAlexander 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
BabluBablu
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

User-added image

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 TsitsuraAlexander 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 TsitsuraAlexander 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.
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
BabluBablu
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)
User-added image

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 TsitsuraAlexander 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.
User-added image
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) {
    BusinessHours stdBusinessHours = [select id from businesshours WHERE Name = 'Default'];
    for (Case item : Trigger.New) {
        DateTime startDate = item.CreatedDate != null ? item.CreatedDate : DateTime.now();
        DateTime endDate = item.Case_Accepted_Date__c;
        Long diff = BusinessHours.diff(stdBusinessHours.Id, startDate, endDate);
        Long minutes = diff / (60 * 1000);
        item.Time__c = (minutes / 60) + ':' + Math.mod(minutes, 60);
    }
}

I verified, and this code work correct.

Thanks,
Alex