converting custom number h:mm into time

Question

I have time values entered in Excel as a custom number h:mm. I would like to convert these to a format (still displayed as h:mm) which will allow me to perform calculations using the data. Any ideas?

Answer 1

The proper way to solve this problem would be to format the cells to time format (so Excel treats them as times).

If you cannot do that for some odd reason this will extract the hour and minutes out of the time and create a time value from cell A1:

=TIME( LEFT(A1, FIND(":",A1)-1), RIGHT(A1, LEN(A1)-FIND(":",A1)), 0 )

Answer 2

I agree with Trey. You should really use the h:mm internal time format that Excel uses natively. Converting a custom time format to the native format (especially when they are visually the same) just complicates your spreadsheet and will make maintenance more difficult in the future.

Excel's time/dates are just numbers formatted to look like times and/or dates. The interval 0 - 1 represents one full day. So an hour is 1/24, a minute is 1/3600 and a second is 1/86400. You can do your calculations based on this logicand/or you can use the inbuilt time/date functions.

Dates are handled like times and start from 1900-01-01. So 12:00 PM 1900-01-02 is represented internally as 2.5

You can use Number formats to display the time/date in almost any combination you care to think of.