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

Copper Contributor

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!

7 Replies

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

@Sergei Baklan  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.

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

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

@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

@Sergei Baklan 

 

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

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