Convert text to time

[Nicknock_85]

I am having trouble converting a text cell to a time cell. Please help as this is driving me mad! I have found several fixes online but none of them specifically address my problem.

I have time inputted as text, such as 8.40 to indicate 8 hours and 40 minutes. However, if I format the cell as a time cell, it changes the time to 9:36. This is not right. I just want it to display as 08:40. If I go 'find' and 'replace' the full stop with a colon, it doesn't work either, it returns 08:04 which is also wrong. Any help will be very much appreciated PLEASE!!!

N

[martindwilson]

it cant be text as find replace would work fine,i just tried it, also formatting as anything else would not change it
try
=TIMEVALUE(INT(A1)&":"&TEXT(100*(A1-INT(A1)),"00")) cell formatted as time

[Nicknock_85]

Brilliant! Thanks! That has worked! Relief!! Thanks so much :-)

[Haseeb Avarakkan]

Try also,

=SUBSTITUTE(A1,".",":")+0

format as time

[DonkeyOte]

@Haseeb, that would return 8:04 rather than 8:40

FWIW, another variant:

Please Login or Register  to view this content.

[martindwilson]

see there's always a shorter way!

[kisanvikas2015]

I was searching for excel formula to Convert Text to Time Value in excel & this is really a good one.

Regards,
Vikas