6

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.

Improve this question
1
  • result in minutes is not in right format. IN Akash post any Help
    – user86732
    Sep 27, 2019 at 18:47

3 Answers 3

Reset to default
7

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

Improve this answer
2
  • Did you even read what I wrote in question?
    – Danilo
    Mar 20, 2017 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, 2017 at 14:25
0

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

Improve this answer
1
  • 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, 2017 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.