1

I have the three field like below and I want to calculate the number of out of office hours.

Office hour is : 9:00 to 18:00

Fields :

In Time : date time field Out Time : date time field

No. of Out of Office Hour : ?

ex:

My Office hour is : 9:00 to 18:00

Fields :
In Time : date time field 
Out Time : date time field
No. of Out of Office Hour : ?

Sample Answer : 
In time : 1-1-2014 19:00 
Out time : 1-2-2014 07:00
No. of Out of Office Hour :0 day 12 hrs.

Kindly let us know, how to calculate in apex trigger / class

Improve this question
  • From 7pm on January the 1st to 7am on January the 2nd is only 12 hours. How can "No. of Out of Office Hour" be "1 day 12 hrs 30 mins."? – Daniel Ballinger May 19 '14 at 0:24
  • The image that you edited in seems like a separate question. If so, it should be asked independently of this question. – Daniel Ballinger May 19 '14 at 0:26
  • The answer I've given below gives 12 hours for the example input in and out date times. Is there a problem with the answer? If so, please give an example of the in and out date times and the expected answer. Do you want to exclude Saturday and Sunday hours even though they are included in your sample Business Hours? – Daniel Ballinger May 27 '14 at 19:38
  • 1
    I'm voting to close this question as off-topic because this is too old to migrate. – Tushar Sharma Jun 15 '16 at 11:28
3

Try using the BusinessHours.diff(String, Datetime, Datetime) method.

Returns the difference between a start and end Datetime based on a specific set of business hours.

NOTE : The following examples assume that the current user is in the the same timezone as the BusinessHours. If not, you will need to correct via GMT/UTC.

// Get the default business hours
BusinessHours bh = [SELECT Id, MondayStartTime, MondayEndTime, TuesdayStartTime, TuesdayEndTime, TimeZoneSidKey FROM BusinessHours WHERE IsDefault=true];

Datetime inTime = Datetime.newInstance(2014, 1, 1, 19, 0, 0);
System.assert(!BusinessHours.isWithin(bh.id, inTime));
Datetime outTime = Datetime.newInstance(2014, 1, 2, 7, 0, 0);
System.assert(!BusinessHours.isWithin(bh.id, outTime), outTime.format() + ' GMT: '+ outTime.timeGMT()+ ' expected to be out of business hours' + bh);
long businessMillisecondsDiff = BusinessHours.diff(bh.Id, inTime, outTime);
decimal businessHoursDiff = businessMillisecondsDiff / (1000.0*60.0*60.0);

// The get the time difference and convert from milliseconds to hours.
decimal totalHoursDiff = (outTime.getTime() - inTime.getTime()) / (1000.0*60.0*60.0); 
System.assertEquals(12.0, totalHoursDiff);

decimal outOfBusinessHours = totalHoursDiff - businessHoursDiff;
System.debug('totalHoursDiff:' + totalHoursDiff +
             '\n businessHoursDiff: ' + businessHoursDiff + 
             '\n outOfBusinessHours:' + outOfBusinessHours);

totalHoursDiff:12
businessHoursDiff: 0E+3
outOfBusinessHours:12

The following separate test also works:

BusinessHours bh = [SELECT Id, MondayStartTime, MondayEndTime, TuesdayStartTime, TuesdayEndTime, TimeZoneSidKey FROM BusinessHours WHERE IsDefault=true];

Datetime inTime = Datetime.newInstance(2014, 1, 6, 11, 0, 0);
System.assert(BusinessHours.isWithin(bh.id, inTime));
Datetime outTime = Datetime.newInstance(2014, 1, 6, 20, 0, 0);
System.assert(!BusinessHours.isWithin(bh.id, outTime), outTime.format() + ' GMT: '+     outTime.timeGMT()+ ' expected to be out of business hours' + bh);
long businessMillisecondsDiff = BusinessHours.diff(bh.Id, inTime, outTime);
decimal businessHoursDiff = businessMillisecondsDiff / (1000.0*60.0*60.0);

// The get the time difference and convert from milliseconds to hours.
decimal totalHoursDiff = (outTime.getTime() - inTime.getTime()) / (1000.0*60.0*60.0); 
System.assertEquals(9.0, totalHoursDiff);

decimal outOfBusinessHours = totalHoursDiff - businessHoursDiff;
System.debug('totalHoursDiff:' + totalHoursDiff +
             '\n businessHoursDiff: ' + businessHoursDiff + 
             '\n outOfBusinessHours:' + outOfBusinessHours);

totalHoursDiff:9
businessHoursDiff: 7
outOfBusinessHours:2

Improve this answer
  • @RickyMartin I've made some corrections to how the totalHoursDiff is calculated. – Daniel Ballinger May 17 '14 at 8:49
  • @RickyMartin Did you see my comments on the question above? Your example no longer makes sense and you have asked a separate question. – Daniel Ballinger May 20 '14 at 20:50
  • 1
    @RickyMartin Maybe you could provide more examples of what you are trying to accomplish? Ideally show all time periods involved and what you expect the outcome of the calculation to be. – Daniel Ballinger May 21 '14 at 21:40
  • @RickyMartin From what I can tell you have significantly changed the objective of the question. At this stage I would suggest you rollback your changes and ask a new question about holiday hours. – Daniel Ballinger Aug 1 '14 at 9:35
  • i have posted new question as salesforce.stackexchange.com/questions/45738/… – SFDC LEARNER Aug 1 '14 at 9:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.