Answered by:
Calculating date time difference
Question

Hi, I have a list with 2 columns (Start Time, End Time) that contains date & time information and a third column (Duration) that is calculated field. I wanted to calculate the difference between the first 2 columns and show it in the 3rd field. It's working fine, but in some cases, the result is incorrect. Below are the details:
I wanted to show the Duration column values like "2 hours 45 minutes". If it's 0 minutes, then just show "2 hours". The complete formula I used is:
=CONCATENATE(IF(INT(([End Time][Start Time])*24)>1,INT(([End Time][Start Time])*24)&" hours",IF(INT(([End Time][Start Time])*24)=1,INT(([End Time][Start Time])*24)&" hour","")),IF(MINUTE([End Time][Start Time])>0,MINUTE([End Time][Start Time])&" minutes",""))
In the above formula the part that calculates the hour difference is:
INT(([End Time][Start Time])*24)
If the start time is at 9:00am and the end time is at 1:00pm, the result is 3 hours although it should be 4 hours. If the start time is 10:00am and end time is 1:00pm, the result is 3 hours, which is right. But if the start time is 11:00am and end time is 1:00pm, the result is 1 hour, but it should be 2 hours. Also, if the start time is 12:00pm and the end time is 1:00pm, the result is 0 hours. Interestingly, if the start time is 9:00am and the end time is 1:05pm, the result is 4 hours.
Below is a table where I've put all my sample data and you can see that some results are correct, but where I have end time is 1:00pm, sometimes it's incorrect. Just compare the result of the last 4 rows with. The last row's Duration is empty because the calculation result is 0 hour.
Can you shed any light on this? Is it a bug in sharepoint or is it the formula that I am using? Is there any simpler formula than the one I'm using but will give me the same result?
Start Time
End Time
Duration 1/24/2009 7:00 AM
1/24/2009 3:00 PM
8 hours
1/24/2009 8:00 AM
1/24/2009 6:00 PM
9 hours
1/25/2009 6:00 AM
1/25/2009 8:00 PM
14 hours
2/7/2009 8:00 AM
2/8/2009 5:35 PM
33 hours 35 minutes
2/8/2009 7:00 AM
2/9/2009 5:00 PM
34 hours
2/10/2009 8:00 AM
2/10/2009 8:30 AM
30 minutes
2/8/2009 9:00 AM
2/8/2009 1:00 PM
3 hours
2/11/2009 10:00 AM
2/11/2009 1:00 PM
3 hours
2/14/2009 11:00 AM
2/11/2009 1:00 PM
1 hour
2/16/2009 12:00 PM
2/16/2009 1:00 PM
Sunday, February 8, 2009 4:42 PM
Answers

Should be able to do something like this:
=TEXT(Column2Column1,"h:mm")
This will return the difference between the two in hours and minutes.
John
SharePoint911: SharePoint Consulting
Blog: http://www.rossonmoss.com
Twitter: JohnRossJr
MOSS Explained: An Information Workers Deep Dive into Microsoft Office SharePoint Server 2007 Marked as answer by Lionel Chen  MSFT Monday, February 16, 2009 2:49 AM
Sunday, February 8, 2009 7:40 PM
All replies

Should be able to do something like this:
=TEXT(Column2Column1,"h:mm")
This will return the difference between the two in hours and minutes.
John
SharePoint911: SharePoint Consulting
Blog: http://www.rossonmoss.com
Twitter: JohnRossJr
MOSS Explained: An Information Workers Deep Dive into Microsoft Office SharePoint Server 2007 Marked as answer by Lionel Chen  MSFT Monday, February 16, 2009 2:49 AM
Sunday, February 8, 2009 7:40 PM 
Hi John,
Thanks for your reply. I think your TEXT formula will just show in numbers, for example "1:30". Can the TEXT formula be modified to show it as "1 hour 30 minutes". Does it need to be combined with some other formula?
ParvezFriday, April 24, 2009 12:53 AM 
Hi John,
I was just trying out the TEXT forumal you showed above. It's working great, but for some reason the result is "0" if the hour difference is 24 or 48 or 72 hours. I didn't add anything to it. Simply used it as you had. Am I missing something?
ParvezFriday, April 24, 2009 2:45 PM 
I just found the Microsoft website showing examples of the same formula to calculate the difference between two times. Below is the link:
http://office.microsoft.com/enus/sharepointtechnology/HA011609471033.aspx
=TEXT([End Time][Start Time],"h:mm")
But it seems to work only if the expected duration is less than 24 hours. In my case, The column data is like this
Start Time: 4/25/2009 7:00 AM
End Time: 4/26/2009 7:00 AM
Calculated Column: 0:00 (it should be 24 hours)
If I change the End Time to 6:00 AM then the calculated column will show 23:00, which is correct. And if I change the End Time to 12:00PM, the result is 5:00 instead of 29:00.
So, the text formula doesn't seem to be working as expected if the duration is over 24 hours. And the hour gets reset again if the duration is over 48 hours and 72 hours and so on... So, if the duration is supposed to be 49 hours, the above formula will show 1 hour and ofcourse for 48 hours, it will show 0 hours, just like the duration of 24 hours or 72 hours. Any thoughts?
I had the calculated column as "single line of text" format, but I changed it to "Date and Time" format, but it shows the exact same result.
Regards,
Parvez Proposed as answer by Taylanator2000 Thursday, August 21, 2014 5:33 PM
Friday, April 24, 2009 9:12 PM 
=DATEDIF([Start Date],[End Date](MOD([Start Date],1)>MOD([End Date],1)),"d")&" days, "&TEXT(MOD([End Date][Start Date],1),"hh "" hrs, "" mm "" mins""")
I used the above calculation to get the duration between 2/27/2009 9:35 AM and 3/3/2009 4:35 PM
Output looks like this: 4 days, 07 hrs, 00 mins
Basically, it a slight modification to the code found here > http://www.mvps.org/dmcritchie/excel/datetime.htm
Under the heading "Age in Years, Months, Days using DATEDIF Worksheet Function (#age)(#datedif)"
Hope it helps, and yes, I now this thread is quite old :)
Thanks
Rich Proposed as answer by DeNae L Friday, August 10, 2012 2:52 PM
Thursday, February 4, 2010 2:38 PM 
Thanks, this was very helpfulThursday, August 30, 2012 1:02 PM

This actually worked great for me too.Tuesday, July 8, 2014 6:22 PM

Thanks man this is awesome formula
Tuesday, July 8, 2014 6:33 PM 
I'm creating a customized list for staff to input their working hours, all columns are working except my Overtime calculated column if my total week hours are over 40. Columns are:
1.Week WH = Week Working Hours formula: =TEXT([Mon WH]+[Tue WH]+[Wed WH]+[Thu WH]+[Fri WH],"[h]:mm")
2.Week OWH = Week Overtime Working Hours formula: =IF([Week WH]>="40:00",TEXT([Week WH]"40:00","hh:mm"),"00:00")
My total "Week WH" columns is giving me a total of "46:30", so I need to calculate the "Week OWH" to be the difference after "40:00" and the result should be "6:30"
Tuesday, July 5, 2016 7:34 PM 
Rock on man! This was fast and easy. Thanks!Friday, June 29, 2018 12:04 AM