microsoft excel - Formula to format minutes value into "N hours, M minutes" string

I'm referring to this question respectively to this edited answer. There is a column with time periods in minutes. The time period of three hours and two minutes would be 182. How to conditionally format the cells using a formula to get the following results?

0 → 0 minutes

1 → 1 minute

2 → 2 minutes

60 → 1 hour

120 → 2 hours

181 → 3 hours, 1 minute

63 → 1 hour, 3 minutes

Do I need to do some (rounded) division and some modulo calculation? Or is there a built-in function for things like that?

Answer

=IF(A1=0,"0 minutes",IF(A1>=60,FLOOR(A1/60,1) & " hour" & IF(A1>=120,"s",""),"") & IF(MOD(A1,60)>0,IF(A1>=60,", ","") & MOD(A1,60) & " minute" & IF(MOD(A1,60)>1,"s",""),""))

According to the comments you are using German Excel, which uses semi-colons to separate function parameters, so replace the commas with semi-colons:

=IF(A1=0;"0 minutes";IF(A1>=60;FLOOR(A1/60;1) & " hour" & IF(A1>=120;"s";"");"") & IF(MOD(A1;60)>0;IF(A1>=60;", ";"") & MOD(A1;60) & " minute" & IF(MOD(A1;60)>1;"s";"");""))

(Note: there is still one comma in there because that is in the output string.)

0      0 minutes
1      1 minute
2      2 minutes
59     59 minutes
60     1 hour
61     1 hour, 1 minute
62     1 hour, 2 minutes
119    1 hour, 59 minutes
120    2 hours
121    2 hours, 1 minute
122    2 hours, 2 minutes
179    2 hours, 59 minutes
180    3 hours
181    3 hours, 1 minute
182    3 hours, 2 minutes
1439   23 hours, 59 minutes
1440   24 hours