number string to time with am/pm

[smansberger]

How do you convert 01241 to 1:24 am - hh:mm 1 for am 2 for pm
01241 (hhmm#) to 1:24 am - hh:mm 1 for am 2 for pm
02452 (hhmm#) to 2:45 pm - hh:mm 1 for am 2 for pm
12142 (hhmm#) to 12:14 pm - hh:mm 1 for am 2 for pm
08451 (hhmm#) to 8:45 am - hh:mm 1 for am 2 for pm

I want to have data entry that they only need to input the 5 numbers and it will transfer the numbers input to hh:mm with am or pm.

 

[Tetra201]

Try using the following formula; format the result as h:mm am/pm

=--(TEXT(LEFT(A1,4),"00\:00 ")&MID("AP",RIGHT(A1),1))