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 Commented Sep 27, 2019 at 18:47

3 Answers 3

Reset to default
6

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? Commented 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. Commented 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. Commented 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.