1

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.

Improve this question
4
  • Instead of assuming there's a full time stamp, couldn't you use a Date/Time field to make sure the information exists?
    – vanblart
    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?
    – vanblart
    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

2 Answers 2

Reset to default
0

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!

Improve this answer
1

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)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.