How to calculate the time between two dates, excluding business hours and holidays, using Excel or in Salesforce platform?
Assuming the earlier date is in A1 and the later in B1 then:
=24*(B1-A1-1)-8*(NETWORKDAYS(A1,B1)-2)-16*(NETWORKDAYS(A1,B1)-2 -(NETWORKDAYS(A1,B1,holidays)-2))
I have not condensed the above as you may wish to make adjustments.
The part starting 24* is the total number of hours (excluding shifts in clocks) between the two dates. Between does not count either -after the earlier and before the later.
The part starting 8* is the total number of hours that are to be excluded on the basis that they are working hours. Here I have assumed an eight hour day. What counts as a weekend (standard non-working days) is as decided by Microsoft. For more flexibility about that use NETWORKDAYS.INTL, if available.
The part starting 16* is the total number of hours to be excluded (that have not already been) on the basis that they are holidays. These days are calculated as the difference between working days assuming no holidays and working days excluding holidays, with the latter excluded through the optional third function parameter which here is a range named holidays.
The result I get from A1 = 31/12/2014 and B1 = 12/01/2105 is 192, assuming Saturdays and Sundays are weekend days and 1/1/15 is in holidays, which may be calculated as four weekend days (96 hours) plus six work days (@16 hours = 96).