none
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 08, 2009 4:42 PM

Answers

All replies

  •  Should be able to do something like this:

    =TEXT(Column2-Column1,"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
    Sunday, February 08, 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?

    Parvez
    Friday, 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?

    Parvez
    Friday, 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/en-us/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
    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 04, 2010 2:38 PM
  • Thanks, this was very helpful
    Thursday, August 30, 2012 1:02 PM
  • This actually worked great for me too. 
    Tuesday, July 08, 2014 6:22 PM
  • Thanks man this is awesome formula

    Tuesday, July 08, 2014 6:33 PM