1

Trying to get some working syntax for calculating the difference in hours between 2 dates, Start Date [9am] and Due Date [5pm] for example.

=TEXT([Due Date]-[Start Date],"h")

This yields the correct hourly difference when both dates are on the same day.

However I need the formula to take the full difference into account!

=(IF(AND((WEEKDAY(B2,2))<(WEEKDAY(A2,2)),((WEEKDAY(A2,2))-(WEEKDAY(B2,2)))>1),(((DATEDIF(A2,B2,"D")+1))-(FLOOR((DATEDIF(A2,B2,"D")+1)/7,1)*2)-2),(((DATEDIF(A2,B2,"D")+1))-(FLOOR((DATEDIF(A2,B2,"D")+1)/7,1)*2)))-1)*8+((TIME(HOUR(B2),MINUTE(B2), SECOND(B2))-TIME(HOUR(A2),MINUTE(A2), SECOND(A2)))*24)

This is the syntax from a previous question, I've tried adapting it to my column names but i'm not sure if this is the correct formula to use or not.

My regional settings have the work day set to 8am til 5pm (9hrs) working day (Mon-Fri)

Any help is greatly appreciated.

Improve this question

2 Answers 2

Reset to default
1

My regional settings have the work day set to 8am til 5pm (9hrs) working day (Mon-Fri)

This will have no impact on a Calculated Column.

To find working hours excluding weekends and assuming a work day from 8AM to 5PM (9 hours) then:

=(INT([Due Date])-INT([Start Date])+1)*9 
 + IF( INT([Due Date])<INT([Start Date])+5-WEEKDAY([Start Date],3),
       0,
       -FLOOR( ((INT([Due Date])-INT([Start Date]))+1 )/7,1) * 2 * 9 )
 -(([Start Date]-INT([Start Date]))*24-8)
 -(17-([Due Date]-INT([Due Date]))*24)

This assumes all entries are M-F between the hours of 9 and 5. It does not work correctly for weekend dates or hours outside of 9 to 5.

enter image description here

To find working hours excluding weekends and assuming a work day from 9AM to 5PM (8 hours) then:

=(INT([Due Date])-INT([Start Date])+1) * 8 
 + IF( INT([Due Date])<INT([Start Date])+5-WEEKDAY([Start Date],3),
       0,
       -FLOOR( ((INT([Due Date])-INT([Start Date]))+1 )/7,1) * 2 * 8 )
 -(([Start Date]-INT([Start Date]))*24-9)
 -(17-([Due Date]-INT([Due Date]))*24)
Improve this answer
4
  • Hi Mike, thanks for the reply. I've clarified my question as i'm trying to find the exact hourly difference based between 2 dates and not the number of 24 hours periods between 2 dates. And those hours are based on the 9am start and 5pm end. So each day is a 9 hour working day.
    – cod3r_jim
    Jun 20, 2018 at 8:31
  • I have updated the answer above.
    – Mike Smith - MCT
    Jun 21, 2018 at 2:58
  • Much appreciated Mike, that's one gorgeous formula! Thank you.
    – cod3r_jim
    Jun 21, 2018 at 8:13
  • Jim, I've been working on a book and a series of blog articles on Calculated Columns, so I've been getting a little practice in writing these... :-) Months, Working days and Hours are the most challenging.
    – Mike Smith - MCT
    Jun 25, 2018 at 16:14
1

To calculate the total hours between two times, using the following formula:

=INT(([Column2]-[Column1])*24)

Reference:

https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v%3Doffice.14)

Improve this answer
1
  • Hi Lisa, appreciate the help. I've clarified my question a bit more as im not looking for the number of 24 hours periods but the actual difference in hours between the 2 timestamped dates. And those hours are based on the 9am start and 5pm end. So each day is a 9 hour working day.
    – cod3r_jim
    Jun 20, 2018 at 8:30

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.