Forum Discussion

Des_C2340's avatar
Des_C2340
Copper Contributor
Jan 02, 2020

Calculate difference between two times over multiple days

Hi

I have a generated excel report that shows day and time in this format:

2019/11/30 16:32:062019/12/01 08:44:15

I want to calculate the difference between the two, giving the result in hh:mm:ss

Col B - Col A works when the hours are in the same day (simple enough), but doesn't return the correct number if the dates are different, as I assume it;s looking at the time stamp only.

So I'm looking for a formula that looks at both the date and time stamp and returns the difference between the two.

Any formula suggestions would be much appreciated.

Thanks

Des C

 

excel

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 02, 2020

    Des_C2340 

    The formula

    = periodEnd - periodStart

    is perfectly correct and does take the number of days into account (your example is a period of 16hrs so will not illustrate the point).  If you apply the number format

    [h]:mm:ss

    elapsed times will show hours in a format that may exceed 24.

     

    • Des_C2340's avatar
      Des_C2340
      Copper Contributor
      Jan 02, 2020
      Hi Peter
      Perfect. Thank you so much. Knew it had to be simple. Much appreciated.
      Have a good day.
      Des

Resources