Time format

Hey guys just a quick one, but its frustrating me. How can I type a value and have it returned as a time, eg. if I type 1430, it must return as 14:30, if I use the time format it keeps returning to 00:00, I still have to use the ':' to have it display the time correctly. Is there any way not to have to type the ':' ?

A b c
1 1430 14:30 =concatenate(left(a1,2),":",right(a1,2))

But that won't work if morings are entered as 930, so try

=CONCATENATE(LEFT(A1,LEN(A1)-2),":",RIGHT(A1,2))

toemaar Willem myne ook nie! Thanx guys but I found a more simple solution, I just formatted the block as 00":"00 and it works like a bomb!

now, if I want to type in a block the month, ie Jan it must return Jan-14???? Hhhm cant get this one to work.... Help!

=TEXT(LEFT(B14,LEN(B14)-2)&":"&RIGHT(B14,2),"hh:mm")

try this

yup it worked thanx!