Convert time to a number

[hollywood]

Please note that I am a novice to excel, so please reply in a manner that I
will understand. My problem is, I would like to convert time to a number that
I can use in a simple program I have created with excel. At present I am
copying from a PDF format and pasting to excel. The following time is what I
am copying and pasting (2:05) where the 2 = minutes and the 5 = seconds,
There is also fifth of a seconds involved at times, but is not critical for
the application, so it could be left out of the solution if necessary. At
present I am converting the time manually, 2:05 = 125, etc. with each time
that is posted. This becomes very time consuming. If there is a solution to
my problem it would cut my posting time in halve. If there is a solution, do
I post the time in cell A1 and the function in cell B1.

 

[Gary''s Student]

=86400*A1 and format the cell as Number

 

[Sean Timmons]

And the below would be entered into B1.

Please note, Excel treats a day as 1. So, an hour is 1/24, a minute is
1/1440 and a second is 1/86400.

 

[Pete_UK]

You need to be a bit careful with this. Although you might interpret
2:05 as meaning 2 minutes and 5 seconds, if that is copied into Excel
it might treat it as a text value, or as a time showing h:mm. Click on
Format | Cells | Number tab and see what format is applied to the
cell. If it is a time with h:mm format then you will need to use 1440
rather than 86400 as the multiplier in the solutions given.

If the format shows Text or General, then you could use this formula:

=VALUE("0:"&A1)*24*60*60

(I prefer to use the individual multipliers)

Hope this helps.

Pete

 

[hollywood]

When I use =86400*A1 I get 7500.00 formatted as a number. When I use 1400 I
get 121.5 Getting close but it does not =125 when 2 minutes 5 seconds is
converted manually to seconds. The A1 cell is shown formatted as "time" H:MM
I thank you all for your efforts. I guess I just have to change it manually.
Any other ideas would be appreciated

 

[JoeU2004]

When I use 1400 I get 121.5 Getting close but it does not =125 when
2 minutes 5 seconds is converted manually to seconds. The A1 cell is
shown formatted as "time" H:MM

That's because the correct multiplier is 1440, not 1400. 1440 is 24*60
because there 24 hours in a day and 60 minutes in an hour.

But it would be prudent to round the result. The following should work
reliably:

=ROUND(A1*1440, 0)


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

 

[JP Ronse]

Hi Hollywood,

You have in A1 00:02:05, correct? Try in B1: =A1.

You will first have the same value in B1. Now, format, cells, custom: [ss]

This gives 125.

You can of course format column A directly in the same way.

Wkr,

JP

 

[JoeU2004]

You have in A1 00:02:05, correct? Try in B1: =A1.
[....] Now, format, cells, custom: [ss]
This gives 125.

Let's not confuse things. The fact that A1*1400 [sic] gives almost the
right answer should tell you that, no, he does not have 00:02:05 in the
cell.

Moreover, formatting only changes the appearance of a number. In this case,
it does not change the fact that the number 02:05 is a decimal fraction.

First, as Hollywood explains at the outset: "I am copying from a PDF format
and pasting to excel. The following time is what I am copying and pasting
(2:05) where the 2 = minutes and the 5 = seconds".

So he is entering time as xx:yy. He wants to interpret that as mm:ss. But
Excel interprets any number of the form xx:yy as hh:mm, even if we preformat
the cell as mm:ss.

That is why he must use A1*1440 instead of A1*86400. He really has minutes
as a decimal fraction, not seconds. That is, when he entered 2:05, he
effectively entered 2 hr 5 min instead of 2 min 5 sec.

(Aside.... I just discovered that if he entered 2:05.0, Excel would
interpret that as mm:ss.0, even if we preformat the cell as hh:mm. But I
digress....)

Second, as Hollywood explains at the outset: "I would like to convert time
to a number [...]. At present I am converting the time manually, 2:05 =
125".

So he wants integer seconds, not a decimal fraction. That is why a simple
format change is not sufficient.


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

Hi Hollywood,

You have in A1 00:02:05, correct? Try in B1: =A1.

You will first have the same value in B1. Now, format, cells, custom: [ss]

This gives 125.

You can of course format column A directly in the same way.

Wkr,

JP

When I use =86400*A1 I get 7500.00 formatted as a number. When I use 1400
I
get 121.5 Getting close but it does not =125 when 2 minutes 5 seconds is
converted manually to seconds. The A1 cell is shown formatted as "time"
H:MM
I thank you all for your efforts. I guess I just have to change it
manually.
Any other ideas would be appreciated

 

[JoeU2004]

Errata....

Now, format, cells, custom: [ss]
This gives 125.

[....]
So he wants integer seconds, not a decimal fraction.
That is why a simple format change is not sufficient.

Well, that's not so obvious; sorry for being so dismissive.

Perhaps the custom format "[m]" (without quotes) would indeed satisfy
Hollywood's requirements. That's for him to decide. Bear in mind that the
time is actually a decimal fraction, despite appearances.


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

You have in A1 00:02:05, correct? Try in B1: =A1.
[....] Now, format, cells, custom: [ss]
This gives 125.

Let's not confuse things. The fact that A1*1400 [sic] gives almost the
right answer should tell you that, no, he does not have 00:02:05 in the
cell.

Moreover, formatting only changes the appearance of a number. In this
case, it does not change the fact that the number 02:05 is a decimal
fraction.

First, as Hollywood explains at the outset: "I am copying from a PDF
format and pasting to excel. The following time is what I am copying and
pasting (2:05) where the 2 = minutes and the 5 = seconds".

So he is entering time as xx:yy. He wants to interpret that as mm:ss.
But Excel interprets any number of the form xx:yy as hh:mm, even if we
preformat the cell as mm:ss.

That is why he must use A1*1440 instead of A1*86400. He really has
minutes as a decimal fraction, not seconds. That is, when he entered
2:05, he effectively entered 2 hr 5 min instead of 2 min 5 sec.

(Aside.... I just discovered that if he entered 2:05.0, Excel would
interpret that as mm:ss.0, even if we preformat the cell as hh:mm. But I
digress....)

Second, as Hollywood explains at the outset: "I would like to convert
time to a number [...]. At present I am converting the time manually,
2:05 = 125".

So he wants integer seconds, not a decimal fraction. That is why a simple
format change is not sufficient.


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

Hi Hollywood,

You have in A1 00:02:05, correct? Try in B1: =A1.

You will first have the same value in B1. Now, format, cells, custom:
[ss]

This gives 125.

You can of course format column A directly in the same way.

Wkr,

JP

When I use =86400*A1 I get 7500.00 formatted as a number. When I use
1400 I
get 121.5 Getting close but it does not =125 when 2 minutes 5 seconds is
converted manually to seconds. The A1 cell is shown formatted as "time"
H:MM
I thank you all for your efforts. I guess I just have to change it
manually.
Any other ideas would be appreciated
:

=86400*A1 and format the cell as Number