Prev: Diff in business days
Next: How do I separate first and last names into two columns?
From: Jim J. on 3 May 2010 16:13
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”?
From: Bob Phillips on 3 May 2010 16:23
Try

=E1*24*60*60

--

HTH

Bob

"Jim J." <JimJ(a)discussions.microsoft.com> wrote in message
news:B4226338-52F0-4D34-8238-1BFF37B9D205(a)microsoft.com...
> 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"?


From: Joe User on 3 May 2010 16:27
"Jim J." wrote:
> 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." wrote:
> 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”?
From: FSt1 on 3 May 2010 16:29
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

"Jim J." wrote:

> 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”?
 | 
Pages: 1
Prev: Diff in business days
Next: How do I separate first and last names into two columns?