Time Values As Numbers

  • Thread starter Thread starter Jim J.
  • Start date Start date
J

Jim J.

How can I change a time value to a regular number? For example, if I enter
start and stop times (Hours-Minutes-Seconds) as 0:02:06 and 0:02:56
(respectively), and subtract the start time from the stop time, I get a
displayed value of 17 seconds, which is equal to an actual value of
0.001458333. (By the way, the start and stop times do not correspond with
the period directly after midnight, but rather are stopwatch values [0:02:06
= 0 hrs, 2 minutes, and 6 seconds into the time period]). I need to work
with the number “17â€, not “0.001458333â€. How can convert this so that Excel
computes using the value “17†rather than “0.001458333�
 
Jim J. said:
I need to work with the number “17â€, not “0.001458333â€.
How can convert this so that Excel computes using the
value “17†rather than “0.001458333�
Click to expand...

If A1 and A2 contain the start and stop times respectively, then

=(A2-A1)*86400

is the time difference in seconds. However, it would be prudent to write:

=ROUND((A2-A1)*86400,0)

to avoid anomalies with the way that Excel does numeric arithmetic.


----- original message -----

Jim J. said:
How can I change a time value to a regular number? For example, if I enter
start and stop times (Hours-Minutes-Seconds) as 0:02:06 and 0:02:56
(respectively), and subtract the start time from the stop time, I get a
displayed value of 17 seconds, which is equal to an actual value of
0.001458333. (By the way, the start and stop times do not correspond with
the period directly after midnight, but rather are stopwatch values [0:02:06
= 0 hrs, 2 minutes, and 6 seconds into the time period]). I need to work
with the number “17â€, not “0.001458333â€. How can convert this so that Excel
computes using the value “17†rather than “0.001458333�
Click to expand...
 
hi
you numbers seem to be a tad off.
0.001458333 = 00:02:06
0.000196759259259259 = 17 seconds
also 0:02:56 - 0:02:06 = 50 seconds.
but
0.000196759259259259 times 60 seconds times 60 minutes times 24 hours = 17

and i don't think excel understands stop watch times. it only understand the
1900 date keeping system. which kinda brings in the after midnight thing.

regards
FSt1
 
Back
Top