Time Values As Numbers

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�
 
J

Joe User

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�

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�
 
F

FSt1

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top