Convert minutes to hours and minutes

[eidahobill]

I have a spread sheet that has two fields for time in minutes in each data row, I need to convert the sum of those two cells into hours:minutes in a new cell. I am currently using the following formula that appears to be working but instead of displaying the correct hours:minutes it is dropping the leading “0” in the minutes section (should be 3:08 but is displayed as 3:8):
=INT((F19+I19)/60)&":"&MOD((F19+I19),60)
F19 and I19 are the cells with the minutes entered by the user, the formula is entered in J19 were the hours:minutes are displayed, again the issue is it is dropping the leading zero for the 8 minutes.

The spread sheet has about 20 lines for logging time, the user enters the minutes in F and I, the spread sheet converts the minutes to hours:minutes and totals the time at the bottom of the column.
Thank You!

[mikekaganski]

… instead of displaying…

So the idea is to show the hours+minutes in cell?

Then use the formula

=(F19+I19)/24/60

and format the cell using this format string:

[h]:mm

Or use formula

=TEXT((F19+I19)/24/60;"[h]:mm")

The former approach has the advantage that the result may be used in following time calculations.

[keme]

Two viable approaches:

• =TIME(0;F19+I19;0) will convert your minute count to “proper” time value. The use of the TIME() function implies time context, which will make the standard format render it as HH:MM:SS. You can reformat the cell with format string [H]:MM to accumulate hours beyond 24 and drop the carryover to days.
• =TEXT((F19+I19)/1440;"[H]:MM") will also first convert minute count to a proper time value (unit =day ; 24 hours makes 1440 minutes). Then the value is converted to text using the same format string indicated above.

If you need further time calculations based on the minute sums, the first suggestion is better. If you have formulas manipulating those sums as strings, the second is the one you need.