changing excel hh:mm time into decimal hours

question

How do I convert a time (like 7:30, seven hours thirty minutes) into a number (ie 7.5) that I can use in an Excel calculation of total hours?

answer

If cell A2 contains the time in hours and minutes (eg 7:30), then this formula is one way to turn that time into its decimal equivalent in hours (ie 7.5):

=INT(A2)*24+HOUR(A2)+ROUND(MINUTE(A2)/60,2)

The inclusion of the ROUND function limits the calculation of the time to two decimal places in this example (ie 7.50).

In this illustration column B has been formatted to three decimal places to illustrate the cut off at two caused by the ROUND:

One of our Technical Notes has a full discussion on rounding.

disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.