Difference Between 2 Dates (number of hours beyond 24 hours)

2018-06-19 02:43:40

Trying to get some working syntax for calculating the difference in hours between 2 dates, Start Date and Due Date.

=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.

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

This will h

  • 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 total hours between two dates:

    =([Due Date]-[Start Date])*24

    I'm not sure what you are trying to do with this:

    =(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)

    But probably just search and replace "A2" with [your enddate column] and "B2" with [your start date column]

    2018-06-19 03:39:58