Forum Discussion
calculating elapsed time - unique cell formula dd hh:mm:ss
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.
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.
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.
Hi, this is very helpful for me. But how about when the days difference is more than 1 please?
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.
