Calculating Time worked across days

[jackson715]

I'm trying to calculate how many hours and minutes are worked on a job that goes across more than one day. The inputs are the start and stop times on a job. Standard work hours are 8:00-4:30 M-F with lunch at 12:00-12:30. How do I account for company holidays as well?

Example:
start time: 2/12/08 11:15 AM
end time: 2/15/08 2:30 PM

Answer should be 26:45, right? Any help walking me through the appropriate formula would be greatly appreciated!

[daddylonglegs]

Assuming your job start time date is in A2 and end time/date in B2, and that both of these will always be within working hours you can use this formula

=(NETWORKDAYS(A2,B2,$Z$1:$Z$10)-1)/3+MOD(B2,1)-MOD(A2,1)+((MOD(B2,1)<0.5)-(MOD(A2,1)<0.5))/48

format result cell as [h]:mm

where Z1:Z10 contains a list of holiday dates

Note: Unless you're using Excel 2007 NETWORKDAYS is an Analysis ToolPak function. If you don't have the addin you can enable with Tools > Addins > tick "Analysis ToolPak"

[stevekirk]

hi,

welcome to the forum,

see attached file

steve

[jackson715]

stevekirk and Daddylonglegs,

Thanks for the direction. Both of your explanations work! I did not have the Analysis ToolPak installed, so I did not see =NETWORKDAYS previously. I appreciate your help.

Jack