Display Times as Hours/Minutes in Excel

How to convert time values into hours/minutes in Excel
iStock/Roberto A Sanchez

Working with time values in Excel often gives users fits. This is because Excel stores times as a decimal portion of a day. Thus 12 hours is the same as 0.5. Three and a half-hours is represented as 0.145833333333333. Understanding this decimal concept along with Excel’s time-based number formats will help you whip time-based values into shape.

Let’s first say that you have the value 181 in a worksheet cell, and you wish to show this as 3:01 for 3 hours and one minute. To do so, we’ll need to divide 181 by 1440 to convert 181 minutes to its decimal equivalent: 1440 is derived from 24 hours in a day, and 60 minutes per hour, thus 24*60=1440. As shown in Figure 1, 181 divided by 1440 is 0.12569.

Figure 1: Convert minutes into their decimal format by dividing by 1440 and then applying a custom number format.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.


Please login or register to join the discussion.

By Martin
Jun 26th 2015 01:12

Excel-lent, thank you - sorry just had to throw that in ;-)

Thanks (0)
By Mayur Patel
Jun 26th 2015 01:12

Thank you, you are the life saver.

Thanks (0)
By Cbhihe
Sep 29th 2015 10:32

Very well, but what happens when you deal with hourly amounts greater than 24 ?

Thanks (1)
to Cbhihe
Feb 24th 2018 14:39

All you have to do is replace h"hours and "m"minutes" with [hh]"hours and "m"minutes". This includes the excess above 24 hours.

Thanks (0)
By nadruzz
Apr 18th 2018 07:11

Really helpful

Thanks (0)