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 not working.  

 

as you can see from the sample data there are some cells that have about 2 days + hours in elapsed time:

 

Sample data:

30 17:10:4030 17:53:58
28 21:00:5930 17:54:26
14 22:25:0716 17:31:29
27 02:29:0127 02:30:15
27 02:20:5227 03:36:26
03 19:43:4104 17:54:34

 

please help!

excel

7 Replies

  • hynguyen's avatar
    hynguyen
    Iron Contributor
    Jun 14, 2020

    zaxxon Take a look at the attached workbook. The result column is for your presentation purpose of hh:mm:ss so please note that it is not recommended to use that column for any calculation. Instead use column E if you need further calculation.

    Your Start and End cells contain only DD but not information about month, year so I assume same month, same year and just calculate the time difference. If this assumption is not correct, the formula will need to be revised.

    • megamonker's avatar
      megamonker
      Copper Contributor
      Oct 05, 2021

      hynguyen 

      Really cool stuff you've done here! Curious what the revision to the formula might look like if these are not all the same month. For example, several rows produce a negative time column (when the assumption is made). Pls help

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 14, 2020

    zaxxon 

    In general it's not important which format is applied to source data, it's important which values are entered. Thus sample file will give more information.

     

    Here we have not elapsed time in cells, we have datetime. If to calculate the difference and show it as elapsed time, when =B1-A1 with format [hh]:mm:ss.  

    • 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.

Resources