Adding a colon ( : ) to convert a standard number into a time format

Good afternoon,

Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?

eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.

Other than creating a table and using a vlookup function, I am hoping there is a better way?

Darren

Say you have the time in column G, then, in column H:

Please Login or Register  to view this content.

If you simply want the 1345 to appear as 13:45 you can use a Custom Format of: 00\:00

If as outlined on prior post you want the value to be physically coerced to a Time value in a subsequent column you can adapt the above concept such that:

B1: =0+TEXT(A1,"00\:00")
formatted as hh:mm