Converting decimals to Time

  • Thread starter Thread starter Piers 2k
  • Start date Start date
P

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
 
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
|
|
 
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)
 
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
 
Back
Top