Convert a time stored as number to time format

[AdRock]

I need to convert a time which is written as 851 to 8:51 but stored in a time format. At the moment it is stored in a general format.

What i want to do is subtract times so i can get a total number of minutes which i can do once all these times are stored in a time format.

Is there a way i can convert a number like that into hh:mm?

 

[Arcticwarrio]

=if(g7>2359,"unknown time",if(len(g7)=3,left(g7,1)&":"&right(g7,2),if(len(g7)=4,left(g7,2)&":"&right(g7,2),"unknown time")))

 

[frolicols]

Try this in a module:

Function TimeConvert(Target As Range) As Date

TimeConvert = TimeSerial(Left(Target.Value, Len(Target.Value) - 2), Right(Target.Value, 2), 0)

End Function

 

[AdRock]

Thanks for your suggestions

Arcticwarrio's solution worked fine. Saved me hours of work

Many thanks guys

 

[AdRock]

One last question.

It works spot on but is there a way i can have a leading zero for hours between midnight and 9am so my time would look like 09:56 instead of 9:56?

Many thanks

 

[frolicols]

One last question.

It works spot on but is there a way i can have a leading zero for hours between midnight and 9am so my time would look like 09:56 instead of 9:56?

Many thanks

If you use my UDF function above, it will convert it to an Excel Time Serial so you can custom format it in Cell Formatting.

 

[AdRock]

If you use my UDF function above, it will convert it to an Excel Time Serial so you can custom format it in Cell Formatting.

I've never used modules before in Excel.

How would i use it?

 

[frolicols]

Hit Alt+F11 to go into VBA.

Click Insert - Module and you will see "Module 1" appear in your Project window on the left

Double click Module1 to open it and paste my code above in.

Go back into Excel and you now have a custom function to use like any other function

in a cell type =TimeConvert(A1)
(where A1 is your time) and it will convert it to an excel time serial. Format the cell to hh:mm and hey presto!

 

[Arcticwarrio]

=IF(G7>2359,"unknown time",IF(LEN(G7)=3,"0"&LEFT(G7,1)&":"&RIGHT(G7,2),IF(LEN(G7)=4,LEFT(G7,2)&":"&RIGHT(G7,2),"unknown time")))

 

[Norie]

This worked for 815:

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

Still need to format as time though.

If you actually want 8:15 then remove the --.