excel - changing a time into a decimal value

question

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

answer

If cell A1 contains the time (eg. 7:30), then this formula is one way to turn that time into its decimal equivalent:

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

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

Technical note has a full discussion on rounding.

disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any of our Technical Tips solutions are applied. Back up your data; test thoroughly before using on live data.