How to take number input and convert to time in excel

Question

I am making an airplane reservation system on java. i got a sample database from online. only problem i have is the way the time is inputted.

and some times are 917 , or 22 , or 2. there is no 0 so i cannot just try and put a semicolon after 2 spaces.

Any one know if their is a way to do this, or some work around.

Answer 1

You can try:

=--TEXT(A1,"00\:00")

and format the result as "Time"

Edit:
I note you have changed your specifications to include that the times may be entered as the hour only: eg: 2 or 22. Try the revised formula below to handle that:

=--TEXT(IF(A1<=24,A1&"00",A1),"00\:00")

Answer 2

You can use the following formula:

=TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),)

A2 = the arrival time cell that you want to convert.

Also, make sure to change your output column to whatever time format you're looking for. Screenshot of results in action included.