Jun 14 2020 09:08 AM - edited Jun 14 2020 09:09 AM
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:40 | 30 17:53:58 |
28 21:00:59 | 30 17:54:26 |
14 22:25:07 | 16 17:31:29 |
27 02:29:01 | 27 02:30:15 |
27 02:20:52 | 27 03:36:26 |
03 19:43:41 | 04 17:54:34 |
please help!
Jun 14 2020 09:49 AM
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.
Jun 14 2020 09:56 AM - edited Jun 14 2020 09:59 AM
@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.
Jun 14 2020 12:11 PM
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.
Jun 14 2020 12:36 PM
@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.
Oct 04 2021 10:46 PM
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
Oct 18 2022 05:26 PM
Hi, this is very helpful for me. But how about when the days difference is more than 1 please?
Oct 20 2022 12:54 PM
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.