Number of working hours between two dates and times

Question

I'm using this formula in a calculated field to calculate the number of work days between 2 dates, but I need it to be more specific and calculate the number of work hours:

=IF(NOT(ISBLANK([Return date])),IF(AND((WEEKDAY([Return date],2))<(WEEKDAY([Beginning Date],2)),((WEEKDAY([Beginning Date],2))-(WEEKDAY([Return date],2)))>1),(((DATEDIF([Beginning Date],[Return date],"D")+1))-(FLOOR((DATEDIF([Beginning Date],[Return date],"D")+1)/7,1)*2)-2),(((DATEDIF([Beginning Date],[Return date],"D")+1))-(FLOOR((DATEDIF([Beginning Date],[Return date],"D")+1)/7,1)*2))),"")

Assumptions: 8hr work day, "Beginning Date" and "Return Date" are date and time fields. Need to be able to calculate partial days.

Answer 1

My recommendation would be to find the number of Days, then the number of hours. Your first field will use this formula to return the number of non-weekend days:

=IF(AND((WEEKDAY([End Date Calendar],2))<(WEEKDAY([Start Date Calendar],2)),((WEEKDAY([Start Date Calendar],2))-(WEEKDAY([End Date Calendar],2)))>1),(((DATEDIF([Start Date Calendar],[End Date Calendar],"D")+1))-(FLOOR((DATEDIF([Start Date Calendar],[End Date Calendar],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date Calendar],[End Date Calendar],"D")+1))-(FLOOR((DATEDIF([Start Date Calendar],[End Date Calendar],"D")+1)/7,1)*2)))

Then you can multiply this by 8 (hours in a work day) in another calculated column field and add the time difference between the start and end dates.

NOTE: Remember to adjust the column references to match your column names!

Answer 2

Yeah, I agree that using 2 columns is the best way to go. Thanks! In case anyone comes across this looking for something similar, here is the formula I used in the second column (converting the number of workdays in the range and adding the partial-day time):

=(TIME(HOUR([Return date]),MINUTE([Return date]),0)-(TIME(HOUR([Beginning Date]),MINUTE([Beginning Date]),0)))*24+([work days]*8)