# Difference between two dates in hours

In SP list there are two date/time columns, column A and B.
I want to calculate difference in hours between those two dates, but only on working hours. My working hours are between 8AM (08:00) and 4PM (16:00).

Example:
If column A is 19.03.2017. 15:00 and column B is 20.03.2017. 09:00, difference should be 2 hours.

I have this column that calculates calendar time:

``````=IF(ROUND((([B]-[A])*24-ROUNDDOWN(([B]-[A])*24,0))*60,0)=60,(ROUNDDOWN((([B]-[A])*24),0)&": 00"),(ROUNDDOWN((([B]-[A])*24),0)&":"&ROUND((([B]-[A])*24-ROUNDDOWN(([B]-[A])*24,0))*60,0)))
``````

and with it I get suspected incorrect result 18 hours.

Any help is much appreciated.

• result in minutes is not in right format. IN Akash post any Help – user86732 Sep 27 '19 at 18:47

The formula for calculating hours between two times is `=TEXT([Column2]-[Column1],"h")`

• Did you even read what I wrote in question? – Danilo Mar 20 '17 at 15:37
• Hi! Thanks so much for your inquiry. :) I did read your question! And the answer could be reached by combining what I gave you with what you already had. I guess I could have helped you combine them if you had asked, for example, for me to do so. – Erin L Mar 22 '17 at 14:25

After dozen of calculated columns created, I solved problem with difference between two workdays in hours.

1st created a column DayDifference to check if start day and end day are same.

``````=IF(AND((WEEKDAY(DateLastDay,2))<(WEEKDAY(Date1stDay,2)),((WEEKDAY(Date1stDay,2))-(WEEKDAY(DateLastDay,2)))>1),(((DATEDIF(Date1stDay,DateLastDay,"D")+1))-(FLOOR((DATEDIF(Date1stDay,DateLastDay,"D")+1)/7,1)*2)-2),(((DATEDIF(Date1stDay,DateLastDay,"D")+1))-(FLOOR((DATEDIF(Date1stDay,DateLastDay,"D")+1)/7,1)*2)))-1
``````

After that created columns to get WorkHours for 1st and last workday.
for 1st day I subtracted time from 1st day from 16:00h, as my work hours are 08-16h.
For last day I subtracted 8 from work hours of last day.

After this, I got DayDifference-1 to get full working days.
Explanation with example:
If I want to get work hours between 09.03.2017. 12h and 16.03.2017. 9h,
I will have to get 1st day hours, at this example it is 4h (16h-12h), similar goes for last day, it is 1h (9h-8h).
With DayDifference column I would get 5 days, as I don't need last day as it wasn't full day therefore I subtracted 1 day more from it.

So it is 4 workdays and 1h+4h, 4*8h+1h+4h=37h

• I'm really excited you were able to solve this all by yourself in a day! I've worked for days on problems before asking other people to do my work for me. – Erin L Mar 22 '17 at 14:27

Apologies for digging up an old thread but wanted to thank you for your work on the formula and share my additions. I adapted it slightly to allow for calculating the number of working hours between two time stamps eg "A2=26/09/2017 08:00" and "B2=27/09/2017 12:30" based on a working day of 08:00-16:00. I am using this to calculate working hours for jobs in sharepoint.

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

I hope this helps someone else! The output is a decimal time eg 12.5 to the above answer which means 12:30 hours.