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.