# Number of working hours between two dates and times

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.

• Instead of assuming there's a full time stamp, couldn't you use a Date/Time field to make sure the information exists? Apr 20, 2018 at 15:04
• Yes, it is a date/time field, that's what I meant. Sorry for the bad syntax.
– Ben
Apr 20, 2018 at 15:17
• Can you edit your question to include if this is happening in a calculated field or as part of a workflow? Apr 20, 2018 at 15:45
• Yeah, it's a calculated field, updated that. I'm playing with adding a second calculated field that converts the above to hours, but maybe I'm over complicating it.
– Ben
Apr 20, 2018 at 16:02

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!

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)
``````