Calculate Networkdays and Hours (Specific Business Hours)

[eraymond]

Excel 2013, I use the following formula to calc business days to nearest hundredth between 2 dates: =(NETWORKDAYS(H2,I2)-2)*24+(1-MOD(H2,1))*24+(MOD(I2,1)*24)

Cell: H = start date
Cell: I = end date

I manually back out holidays

Need to know how to layer deduction of holidays from time calc into the formula, as well as only considering specified business hours within calc. I have a client with 6am to 8pm business hours, and another client with 7am to 7pm, and one other with 8am - 5pm.

 

[barry houdini]

Hello eraymond, welcome to MrExcel

Will you have time and date in H2 and I2? Assuming you do then with holidays listed in Z2:Z10 you can use this formula to calculate the business hours

=24*((NETWORKDAYS(H2,I2,Z$2:Z$10)-1)*(K2-J2)+MOD(I2,1)-MOD(H2,1))

where J2 and K2 contain the start and end times of the business day

I'm assuming that H2 and I2 will always be within business hours, if not then you need a more complex formula....

 

[eraymond]

Thank you Barry,

time (including min/ sec) in H2 and I2, and these fields will not always be within business hours. They can occur during any point within the 24 hr period