Display Times as Hours/Minutes in Excel

istock_roberto_a_sanchez_clock.jpg

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

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.

Replies

Please login or register to join the discussion.

avatar
By Martin
Jun 26th 2015 01:12

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

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

Thank you, you are the life saver.

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

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

Thanks (0)