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
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
  • 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 '18 at 8:31
  • I have updated the answer above. – Mike Smith - MCT - MVP Jun 21 '18 at 2:58
  • Much appreciated Mike, that's one gorgeous formula! Thank you. – cod3r_jim Jun 21 '18 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 - MVP Jun 25 '18 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
  • 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 '18 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.