In Apple Numbers, Version 6.1, I want to convert a decimal value expressing a number of hours into a duration value consisting of hours and minutes, displayed as hh mm. My table looks like this:
Lines Billing rate Money earned Time I can afford to spend (Money / 35) Time in hh mm
100 1.5 /line 150 4.29 ??
I’m using a MBP 2016 running Mojave.
—–
Trish
You don't have to convert anything to display hours and minutes. Just change the cell formatting to Duration and then choose Custom Units and hr and min selected if that is what you want to see.
Once you set it to that format, entering 4.29 into that cell will show 4 hours and 11 minutes. Try and and see.
Thanks. Keeping my reply succinct as I don’t want to exceed 500 characters or break your 2-comment rule: (1) 4.29 does not equal 4h 11m. It equals 4h 17m. (2) Numbers will not show 4.29 as a duration if the number is the result of a calculation in the field. (3) I get the right duration only when (a) I enter the number in the field myself and (b) the field is already formatted as a duration. If I have to change to duration, I get duration x 24. 4h 11m is an interesting result. Are you using 4.61
Sorry, yes, you are right. I must have mistyped. 4h 17m. If you do a calculation based on cells that have non-duration contents, the result won't be a duration. So make sure all of the cells you are using are also durations. Actually, it doesn't have to be all -- for instance, if A1 is 1h 7m and B1 is 3 and C1 is =A1*B1 you will get 3h 21m.
Good to know, thx. Related question: now the only way I can get my duration is by manually copying the decimal value into a new field (preformatted as duration), either by using Paste Formula Results or by entering the value myself. But what I would like is for all the fields to update automatically based on what I enter in the Lines and Billing Rate columns. To do that, I would need a calculation that copies from one field and pastes the formula result into itself. Can that be done?
Trish: Sure. Since a number multiplied by a duration gets you a duration, then just multiply by a duration. This also allows you to specify what 1.0 equals. For instance, if 1.0 is 1 hour, and you have a number in cell C3, then in D3 you could have =C3*DURATION(0,0,1). This puts the result 1h into D3. If you put 4.1 in C3, then D3 automatically updates to 4h 6m. Look up the DURATION function to see that 0,0,1 represents 0 weeks, 0 days, 1 hour. You could do 0,0,0,1 for 1.0 to equal one minute, etc.
Thank you very much! That was really helpful and I'm consistently getting the right results in my table now. Using duration properly, though, is quite complicated. I experimented a little and it appears that the units that are selected under duration (or that are there by default) before a number is entered will affect the conversion (whether you get 102 h or 4 h). Changing those units afterwards will not change the value, just the way it's represented in units. It's hard to figure out.