Excel Format Time

[brucey54]

Hi Folks, need some help here, I am using Excel 2007.

How do you change the following format 20.30 to a time format!

If I try to change the format using the Format Cell function option Custom hh:mm, the time changes from 20.30 to 07:26

Any help would much appreciated

[amy_d2]

Hi Brucey,

this happened because your time is in numerical format to change this to time format pls see below step

suppose your time 20.30 is in Cell A1 & whole in column A

then select Cell A1 or Column A (below action will shift cell dat in to next column , hence ensure you have column B is blank)

go to Text to column ---- delimited---tick on "other" & in side box put . (dot) ---then click on finish.........this will separate two values

then in column c put below formula
=CONCATENATE(IF(LEN(A1)=1,0&A1,A1),":",IF(LEN(B1)=1,B1&0,B1))

this will attached cell again in time format (still it will be in general format)

copy formula & paste special to values ---- format cell or column to HH:MM

Select whole column C go to Text to column & click on finish

i hope above steps will clear you problem , there may be other ways for it , but this works

[popipipo]

An alternative formula:
=TIME(LEFT(A1,FIND(".",A1)-1),RIGHT(A1,2),)