Wrong Entered Time As 14.45 Need To Change To 14:45

[rosyherb]

Hi HELP!!

someone has completed a spreadsheet with some timings but have used a full stop instead of a colon between the hr + Min.

I need to load the data in to another piece of software that only recognises time with a colon.

How can I convert with out retyping it all.

Rosy

[mccfluff]

if its an excel spreadsheet, select the column and change the format to the time format you wish, that will change everything in the column

(highlight the column, right click and select format cells, then from a dropdown list select time, then the option you require)

[wolf63]

Could you maybe try highlighting the relevant column and then use find/replace to change all full stops with colons. (Is that how to spell colon? It looks weird).

[beso]

Add another column formatted as Time.

Assuming the original data is Column A, enter the following formula in the top cell of the new column:
=LEFT(A1,2)&":"&RIGHT(A1,2)

Copy and paste this cell to the rest of the new column.

[beso]

mccfluff //select the column and change the format to the time format you wish, that will change everything in the column //

Unfortunately that will corrupt the data. Excel stores time as fractions of a day. Converting what it originally interpreted as a decimal number will have unexpected results.

[beso]

wolf's idea is probably the best.

[rosyherb]

Hi thanks for the advice, Find and replace also corrupted some of the entries but not all.. strangely. The formula worked but left the decimal point in place...
thanks again.
R