DMW logo
tel 01732 833085
tonbridge · kent · UK

How to Convert Microsoft® Excel hh:mm Times Into Decimal Hours

How to convert times, e.g. 7:30, (seven hours, thirty minutes), into a decimal hour value in Excel. A formula to convert a time expressed in hh:mm format into hours and fractions of an hour

Last updated on 2020-05-26 by David Wallis.


Time Conversion Formula

If cell B2 contains the time in hours and minutes, for example 7:30, then this formula is one way to turn that time into its decimal equivalent in hours, that is 7.50:

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

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

In this illustration Column C has been formatted to two decimal places to correspond to the two places assigned to the ROUND() function via its second argument:

Excel Time Conversion

Note the application of the [h]:mm custom number format to the times in Column B. If you don't apply that format you might get some funny results.

My article Excel Formulae and Formats for Time Differences has an explanation of how Excel manages times greater than 24 hours.

Rounding Numbers has a full discussion on rounding in Excel, Access and VBA.


Your Support for dmw TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification


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.

“How I wish time was decimalised.”

Nancy (2007)