3

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.

5

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
1

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

  • 1
    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
0

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.

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.