locked
Time Difference Calculation for a Custom List RRS feed

  • Question

  • I have a custom list that is a log of import/export completion times. One column contains the start time, a second column contains the end time and a third column calculates the time difference between the two. It's the most basic calculation:

    =TEXT(Finished-Started,"h:mm")

    This works perfectly for rows where both the start and finish time are on the same day. However, I run into a problem when they are on different days.

    How can I run an accurate calculation under a situation like this:

    Start: 23:25 (11:25PM 1/17/11)
    Finish: 3:04 (3:04AM 1/18/11)

    Using the basic calculation from above, the result is 20:21 on the list. But it should be 4:21.

    Any ideas?
    Monday, February 21, 2011 8:46 PM

Answers

  • Hi derekr44,

    I did a test using TEXT(Finised-Started,”h:mm”) formula. And I had got the right result 3:39.
    TEXT formula is used to get hors and minutes between two times, and it would
    ignore the Date part, and only calculate the different between Time part.
    For example:
    Start: 1/17/11 12:00 AM Finish: 1/18/11 12:00 AM, it will return 0 using TEXT formula.
    But it will still get right result when finished time is AM and started time is PM.

    If you want to get the difference between two dates and calculate dates include Date part, please try the following formulas:

    Start: 23:25 (11:25PM 1/17/11)
    Finish: 3:05 (3:04AM 1/18/11)

    =Finished-Started, it will return a number of days between the dates.
    Return: 0.152777778 (days)

    =(Finished-Started)*24, it will return a number of hours between the dates.
    Return: 3.666666667 (hours)

    You could also use the mathematical formulas to subtract the decimals and convert the decimals to minutes. Please try the follow formula.
    =IF(ROUND(((Finished- Started)*24-ROUNDDOWN((Finished- Started)*24,0))*60,0)=60,(ROUNDDOWN(((Finished - Started)*24),0)&": 00"),(ROUNDDOWN(((Finished- Started)*24),0)&":"&ROUND(((Finished- Started)*24-ROUNDDOWN((Finished- Started)*24,0))*60,0)))

    Reture: 3: 40

    For more formulas used in calculate column, please read this reference.
    http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA010105479.aspx

    Thanks & Regards,
    Emir

    • Marked as answer by Emir Liu Wednesday, March 2, 2011 2:26 AM
    Wednesday, February 23, 2011 3:11 AM

All replies

  • Hi,

    It looks a bit tricky but its actually not.

    Here are some links which will guide you.

    http://abstractspaces.wordpress.com/2009/05/02/common-date-time-formulas-for-sharepoint-calculated-fields/

    Alternate link

    http://msdn.microsoft.com/en-us/library/bb862071.aspx

     

    ~Anshul

     

    • Proposed as answer by Anshul_SP2009 Tuesday, February 22, 2011 4:38 AM
    • Unproposed as answer by Anshul_SP2009 Tuesday, February 22, 2011 4:45 AM
    • Proposed as answer by Anshul_SP2009 Tuesday, February 22, 2011 4:45 AM
    • Unproposed as answer by Mike Walsh FIN Tuesday, February 22, 2011 8:56 AM
    Tuesday, February 22, 2011 4:38 AM
  • Hi derekr44,

    I did a test using TEXT(Finised-Started,”h:mm”) formula. And I had got the right result 3:39.
    TEXT formula is used to get hors and minutes between two times, and it would
    ignore the Date part, and only calculate the different between Time part.
    For example:
    Start: 1/17/11 12:00 AM Finish: 1/18/11 12:00 AM, it will return 0 using TEXT formula.
    But it will still get right result when finished time is AM and started time is PM.

    If you want to get the difference between two dates and calculate dates include Date part, please try the following formulas:

    Start: 23:25 (11:25PM 1/17/11)
    Finish: 3:05 (3:04AM 1/18/11)

    =Finished-Started, it will return a number of days between the dates.
    Return: 0.152777778 (days)

    =(Finished-Started)*24, it will return a number of hours between the dates.
    Return: 3.666666667 (hours)

    You could also use the mathematical formulas to subtract the decimals and convert the decimals to minutes. Please try the follow formula.
    =IF(ROUND(((Finished- Started)*24-ROUNDDOWN((Finished- Started)*24,0))*60,0)=60,(ROUNDDOWN(((Finished - Started)*24),0)&": 00"),(ROUNDDOWN(((Finished- Started)*24),0)&":"&ROUND(((Finished- Started)*24-ROUNDDOWN((Finished- Started)*24,0))*60,0)))

    Reture: 3: 40

    For more formulas used in calculate column, please read this reference.
    http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA010105479.aspx

    Thanks & Regards,
    Emir

    • Marked as answer by Emir Liu Wednesday, March 2, 2011 2:26 AM
    Wednesday, February 23, 2011 3:11 AM
  • I have a custom list name Leave Taken. One column contains the Start Date, a second column contains the End Date and i want to get difference between the Start Date and End Date in the coloum Leave Taken how to do this calculation

    What i did is i have taken Leave Taken Column as Calculated and writte formula as  Leave Taken (Calculate [Start Date] + [End Date])

    but i get the value like

      Star Date        End Date       Leave Taken
        6/8/2012        6/10/2012        82,136

    Which formula should i use to get result like below

      Star Date        End Date       Leave Taken
        6/8/2012        6/10/2012        2

    Friday, June 8, 2012 9:40 AM
  • I have a custom list name Leave Taken. One column contains the Start Date, a second column contains the End Date and i want to get difference between the Start Date and End Date in the coloum Leave Taken how to do this calculation

    What i did is i have taken Leave Taken Column as Calculated and writte formula as  Leave Taken (Calculate [Start Date] + [End Date])

    but i get the value like

      Star Date        End Date       Leave Taken
        6/8/2012        6/10/2012        82,136

    Which formula should i use to get result like below

      Star Date        End Date       Leave Taken
        6/8/2012        6/10/2012        2
    Friday, June 8, 2012 9:40 AM
  • Hi Emir,

    Thank you so much for posting this formula, but do you know how I should modify it to suite my needs?  I am trying to simply find the difference between two times which will not span midnight.  I need the answer to be an integer so instead of displaying the time as 3:40, I would like to display the times as 4.5 3.0 40, etc.

    Example:

    3:30-5:00 = 1.5

    5:00 - 7:00 = 2.0

    I have tried using just =INT(([End Date]-[Start Date])*24) but often the answer is incorrect.  Any help would be greatfully appreciated.

    Thanks Again,
    Dan

    Thursday, June 6, 2013 10:20 PM
  • I was looking for this function for hours. Thanks. Works perfectly for me :)
    Wednesday, January 2, 2019 1:56 PM