CODEWITHSUNDEEP
google-sheetsformatduration
aaa90210
aaa90210

Reputation: 12083

Format number of minutes into a Duration of HH:MM?

I have a column that produces a fractional number of minutes, e.g.

71.5

I would like another column to format that into a Duration of the form HH:MM, rounding up minutes, i.e.

1:12

Or 

1h 12m

Upvotes: 1

Views: 100

Answers (2)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

=round(divide(A1-mod (A1,60),60)) & " h " & round(mod (A1,60)) & " m"

would do it with rounding 11.5 minutes up but 11.25 down. I will think about what if even that should say 12.

EDIT: There is a subtle case the above misses on. IT says 179.5 is 2 hours and 60 minutes.

Better is

=round(divide(round(A1)-mod (round(A1),60),60)) & " h " & (mod (round(A1),60)) & " m"

The first round should not be needed, but I want no floating point madness.

If you really want 71.25 to say 1 hr 12 minutes, go with

=round(divide(ceiling(A1)-mod (ceiling(A1),60),60)) & " h " & (mod (CEILING(A1),60)) & " m"

Upvotes: 1

pnuts
pnuts

Reputation: 59442

Please try:

=roundup(A1,0)/1440

and format:

[h]:mm

Upvotes: 2

Related Questions