How to take number input and convert to time in excel

user_17197180 0
Viewed 41

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.

enter image description here

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.

excel database
2 Answers

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.

Formula to convert 3 or 4 digit number to time format

user_172166780

You can try:

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

and format the result as "Time"

enter image description here

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")
user_28729220
Related