Changing Excel hh:mm Time
Into Decimal Hours
Last updated on 2018-08-01 by David Wallis.
‘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?”
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:
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 C has been formatted to two decimal places to correspond to the two places assigned to the ROUND() function via its second argument:
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. Excel Formuale and Formats for Time Differences carries an explanation of how Excel manages times greater than 24 hours.
Rounding Numbers Rumination has a full discussion on rounding in Excel, Access and VBA.
Give DMW’s Tips Your Support
ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.
Thanks, in anticipation.
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.