converting time from "hundred hours" to actual time format time

Hello Excel Forum,

This is my first post here, so let me know if this is the wrong forum.

I have a list of numbers in a column that represent time, but they aren't formatted as time (and can't be). I need the numbers changed from their existing format to time format. The following is an example of my issue:

Current Format Needs to be
400 4:00
1130 11:30
1200 12:00
630 6:30
500 5:00
100 1:00

Is there a way I can change these to be formatted as time? I hope my message is clear enough for you to understand my issue.

Thanks in advance!

Originally Posted by ChemistB

Here's one way; Format the cell as time and

=(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))+0

Does that work for you?

Yes this works just fine. Thanks a lot!

Originally Posted by daddylonglegs

With 400 in A1 you can convert in B1 with this formula

=TEXT(A1,"00\:00")+0

format as time

This works too... Thanks.