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.

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

Thanks (0)

Thank you, you are the life saver.

Thanks (0)

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

Thanks (0)