calculating elapsed time and speed

[Pete]

I have a database with time of day formatted such that 8:37 AM appears
as "837" or 10:06 PM appears as "2206" (without the ""). I wish to
calculate the elapsed time between successive entries in seconds (for
example, A1 -> 837, A2 -> 856, B2 -> 1140).

I can do this by using the following formula to convert my time of day
data as follows:
=IF(LEN(A1)=4,CONCATENATE(LEFT(A1,2),":",RIGHT(A1,2)),CONCATENATE(LEFT(A1,1),":",RIGHT(A1,2)))
This converts, for example, "837" to "8:37". Excel correctly finds a
difference between these times if I use the custom cell format ""
for the A1-A2 formula cell.

But then I run into trouble! Simply dividing the distance (in meters)
by the seconds yields wrong results. For example, 2487 m / 1140 s gets
me 188488.4211, whether that cell is formatted as General, Text or
Number! (The answer should be 2.18 m/s.) Clearly (?!) the simple math
formula is not treating my 'number of seconds' data as the number of
seconds!

Is there some kind of WYSIWYG formula to "convert" my seconds data?
Thanks in advance, Pete

 

[JE McGimpsey]

Is there some kind of WYSIWYG formula to "convert" my seconds data?

First, cell format has ZERO effect on how the underlying values are
handled in calculations (with the exception that Text is generally
ignored unless coerced).

So A2-A1=900 seconds is stored as 900/86400 (where 86400 is the number
of seconds in 1 day, since XL stores times as fractional days).

So one way, assuming B2 = length, A1 = start time, A2 = end time:

=B2/((TEXT(A2,"00\:00")-TEXT(A1,"00\:00"))*86400)

 

[incognito]

Hi Pete,
There is a straight forward way to get the right result:
Use the function 'TIMEVALUE' as follows
=TIMEVALUE(IF(LEN(A1)=4,CONCATENATE(LEFT(A1,2),":",RIGHT(A1,2)),CONCATENATE(
LEFT(A1,1),":",RIGHT(A1,2))))
This converts the time of day of A1 into a decimal fraction (of a 24 hours
day).
Do the same with A2. Using your numbers for A1 and A2, the decimal
difference becomes: 0.01319444444.
Multiply this number by 24 * 60 * 60 gives you the correct difference in
seconds, i.e. 1140 [sec]. Entering your distance of 2487m and the division
by the calculated interval in seconds yields the right speed in [m/sec]. In
your case s = 2.18157895 [m/sec].
I hope this helps you along.
Rudolf H.


Pete wrote on 3/1/07 3:09 PM

 

[PhilD]

But then I run into trouble! Simply dividing the distance (in meters)
by the seconds yields wrong results. For example, 2487 m / 1140 s gets
me 188488.4211, whether that cell is formatted as General, Text or
Number! (The answer should be 2.18 m/s.) Clearly (?!) the simple math
formula is not treating my 'number of seconds' data as the number of
seconds!

Further to what others have said:

You ARE getting the right answer, it's just in m/day, not m/s.
Therefore, divide by 86400 (the number of seconds in a day) to scale
it to the units you want.

PhilD

 

[Pete]

Thank you all...Pete