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

"Piers 2k" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| 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
|
|

Genius!

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)

"Piers 2k" <(E-Mail Removed)> wrote in message
news:etEAPs#(E-Mail Removed)...
> Genius!
>
>

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


"Niek Otten" <(E-Mail Removed)> wrote in message
news:eYVEEH#(E-Mail Removed)...
> Hi Piers,
>
> =TIME(INT(A1),MOD(A1,1)*100,0)
>
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Piers 2k" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> | 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
> |
> |
>
>