Converting decimals to Time

[Piers 2k]

Hi folks,

For thousands of rows, time has been entered as 10.15 or 9.30. These are
correct times, as in quarter-past-ten and nine thirty. To perform
calculations, I obviously need to have a : (semi-colon) not a . (full-stop)

Find and replace won't work because 9.3 (nine thirty) comes up as 9:03.

I can write a macro, but trying to avoid since it's for someone else.

Any ideas?

TIA,

Piers

 

[Niek Otten]

Hi Piers,

=TIME(INT(A1),MOD(A1,1)*100,0)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi folks,
|
| For thousands of rows, time has been entered as 10.15 or 9.30. These are
| correct times, as in quarter-past-ten and nine thirty. To perform
| calculations, I obviously need to have a : (semi-colon) not a . (full-stop)
|
| Find and replace won't work because 9.3 (nine thirty) comes up as 9:03.
|
| I can write a macro, but trying to avoid since it's for someone else.
|
| Any ideas?
|
| TIA,
|
| Piers
|
|

 

[Piers 2k]

Genius!

 

[Bob Phillips]

Another way

=--TEXT(A21*100,"00\:00\:00")

and format as time

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Arvi Laanemets]

Another one
=TIMEVALUE( SUBSTITUTE(TEXT(A1,"00.00"),".",":"))

And yet another
Select the range with data.
Edit>Replace
Search for period, replace with colon, replace all

Arvi Laanemets