Forum Discussion

zaxxon's avatar
zaxxon
Copper Contributor
Jun 14, 2020

calculating elapsed time - unique cell formula dd hh:mm:ss

hi,   i need to calculate elapsed time between cells.  total time that has passed in hours:mins:ss   the cells are formatted like this: dd hh:mm:ss   i tried a custom format on the cell but its...
excel
zaxxon's avatar
zaxxon
Copper Contributor
Jun 14, 2020

SergeiBaklan  here is a sample file

 

any help would be greatly appreciated

 

I need to calculate the elapsed time between columns.  what is throwing me off is the first 2 digits are DD.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 14, 2020

zaxxon 

Thank you for the sample.

 

In left columns are actually texts which represent the date and time. Thus we need to parse these texts and calculate the difference. Simple formula will be

=LEFT(B2,2)-LEFT(A2,2)+RIGHT(B2,8)-RIGHT(A2,8)

It couldn't work for overnight time since we don't know, for example for 28 23:50:57 is that Feb 28 or May 28 or what. Thus if next time is 29 00:40:16 we will have correct days difference and if 01 00:40:16 wrong negative time.

We may correct the formula as

=(LEFT(B2,2)-LEFT(A2,2))*(+LEFT(B2,2)>+LEFT(A2,2))+(+LEFT(B2,2)<+LEFT(A2,2))+RIGHT(B2,8)-RIGHT(A2,8)

assuming days difference is not more than 1.

  • eclipse25's avatar
    eclipse25
    Copper Contributor
    Oct 19, 2022

    SergeiBaklan 

     

    Hi, this is very helpful for me. But how about when the days difference is more than 1 please? 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 20, 2022

      eclipse25 

      I guess it shall work if dates are within same month. If not we shall to know bit more, in which months (and years taking into account leap years) these dates are.

Resources