Time question

[Tony Moffat]

Hello..how does one convert 01:39.3 (one minute 39 point 3 seconds) into
99.3 seconds???..anyone??

Tony

 

[Jazzer]

Hi,

make a formula in another cell like this: =A1*24*60*60 and forma
this cell as general. In this case your time 01:39.3 should be in cel
A1.

- Asse

 

[Frank Kabel]

Hi
if this is stored as Excel time multiply it with 24*60*60.
e.g.
=A1*24*60*60

 

[Tony Moffat]

Jazzer, thanks for replying.. does not quite work for me..in A1 I have the
time 01:39.3 and I want to convert that to read 99.3 seconds (which is one
minute 39 point 3 seconds)..can you suggest something else..there is a whole
long list of serials numbers in there now too..messy


Tony

 

[Jazzer]

Hi Tony,

If I understand you correctly, you want to change the time valu
(1:39.3) to something else (99.3), but you want to do it in the sam
cell, without using any formulas in another cell?

This you can do by using Multiply operation in Paste Special menu. D
it like this.

- In some empty cell type the value 86400 (which is 24*60*60 that i
number of second in a day)
- Select that cell and copy it (just select and press ctrl+c)
- Now select the cell(s) that you want to convert and from Edit -men
choose Paste Special... (or you can just right click on the selecte
area and pick it from the list that opens)
- Select Multiply operation and press OK.

If after this the value in A1 is still 1:39.3, then the value in A
is not in time format. It is just plain text and you have to us
TIMEVALUE -function to convert it in to a correct format. But in tha
case you have to use some other cell to help doing this conversion
After that you can use the same technique I descriped in the begining.

- Asse

 

[Tony Moffat]

Thanks Jazzer..nearly there I think..simply I have the time value 1:39.3
(which I read as 1 minute 39.3 seconds) which I want to convert to 99.3
seconds (being 1 minute + the 39.3)..I have 1100 rows of these in a
spreadsheet and I want to convert them to the seconds value(s) to work with
other formulas..

I don't want to place the formula in the same cell, just change the cell
value....where 1:39.3 is now I want to change to read 99.3 (and I want to do
it 1100 times to various time values!!!)
Tony (cold here tonight in West Australia)

 

[Tony Moffat]

No wait Jazzer..that last one works..eg 24*60*60 (enter)(copy) select target
cell(s) edit/paste special-multiply and it works

Thanks heaps

Tony (cold but happy)

Thanks Jazzer..nearly there I think..simply I have the time value 1:39.3
(which I read as 1 minute 39.3 seconds) which I want to convert to 99.3
seconds (being 1 minute + the 39.3)..I have 1100 rows of these in a
spreadsheet and I want to convert them to the seconds value(s) to work with
other formulas..

I don't want to place the formula in the same cell, just change the cell
value....where 1:39.3 is now I want to change to read 99.3 (and I want to do
it 1100 times to various time values!!!)
Tony (cold here tonight in West Australia)

 

[Jazzer]

Great that it works.

It's cold (12° C) here in Finland too and all the time getting colder
Winter is coming Well, have a nice summer there down under. :