Summing a decimal time with a hh:mm time

Hi. I have searched for a few hours and have tried several methods all without luck. I have a cell (P12) that has a decimal time, say 3.1 and I need to sum that decimal time with another cell (J12) that has time displayed in as HH:MM. This calculation would be displayed in K12. Example below:

J12 = 09:06
P12 = 3.1 (3 hours 6 minutes)
K12 = would have the sum of J12 + P12 displayed as hh:mm

Anyone know how this can be accomplished? Thank you in advance!

What spreadsheet app? Numbers? Excel? Something else?

I just tried both Excel and Numbers and neither one allows "3.1" as a format for time. If you must use decimal format and not hours:minutes, you'll have to manipulate the decimal into the hh:mm format, at least for Numbers and Excel.

Divide p12 by 24 and format the result as HH:MM ... that will show you 03:06
Excel uses a 24 hour clock.

Cheers ... McBie

As I said, you have to manipulate the 3.1 to turn it into hours and minutes, McBie's solution does that. However, when I did that, Excel summed 9:06 and 3?06 as 1:10, quite a bit off, so something is being lost in the translation. I did put 9:06 in one cell and 3:06 in another and summed to 12:12, so Excel does add time properly. I think it's something in the formatting of the various cells.

Instead of formatting the result as HH:MM put the HH between square brackets and make sure your local settings allow for a 24 hour clock.

Cheers ... McBie

MacInWin said:

What spreadsheet app? Numbers? Excel? Something else?

Click to expand...

Silly me. I should have mentioned that in my post. I'm using Numbers. Thank you MacInWin!

McBie said:

Divide p12 by 24 and format the result as HH:MM ... that will show you 03:06
Excel uses a 24 hour clock.

Cheers ... McBie

Click to expand...

I tired to do this but it gave a result of "0.1". When I went to change the format it just forced the formatting back to Automatic and returned the same value of "0.1" I should have mentioned I was using Numbers. Thank you for your reply McBie!

Ok, this seemed to work. Format P12 as Duration, then under units, select Custom and un-highlight Days, leaving hours and minutes highlighted. Now if you enter 3.1, you'll see 3h 6m in the box and the math will work out correctly. BTW, that's using the latest version of Numbers, 3.6.

McBie said:

Instead of formatting the result as HH:MM put the HH between square brackets and make sure your local settings allow for a 24 hour clock.

Cheers ... McBie

Click to expand...

Thanks for the reply McBie. Where in the formula do you place teh square brackets?

MacInWin said:

Ok, this seemed to work. Format P12 as Duration, then under units, select Custom and un-highlight Days, leaving hours and minutes highlighted. Now if you enter 3.1, you'll see 3h 6m in the box and the math will work out correctly. BTW, that's using the latest version of Numbers, 3.6.

Click to expand...

Thanks for the reply Jake! I tried that and Numbers wont let me change the formatting of P12 (the cell that has the value of 3.1) Maybe this is because that cell contains a formula that returns the value of 3.1?

Here is the exactly what I am trying to do. Cell P12 contains a simple formula that sums a couple of other cells and returns a number value. In this case 3.1 which represents 3 hours 6 minutes. In cell J12 I have a manually entered time value of 09:06. In this case 09:06 AM set in 24 time format. What I am trying to do is have Numbers take J12 (time of day) and sum it with P12 (hours taken to complete a task) and return that value in hh:mm in cell K12 which will also be time a time of day in 24 hour format. I'm not sure if this is even possible in Numbers. I wish I could use excel but this company only uses Numbers. Thank you in advance for any further suggestions.

Ok, I tried it with a formula and it won't accept Duration as a format. But it will if all the cells from which the sum is made are also formatted as duration. So change the source cells for the sum to duration and then it should work. I tried it with b7 as 9:06, c2 = 1h, c3=1h, c4=1h, c5=0h 6m (having entered 1,1,1, and .1), then c7 summed them all to 3h 6m. Then d7 had the sum of b7 and c7 and came to 12:12. Each duration cell was formatted for just hours and minutes, not days or seconds.

EDIT, corrected math

MacInWin said:

Ok, I tried it with a formula and it won't accept Duration as a format. But it will if all the cells from which the sum is made are also formatted as duration. So change the source cells for the sum to duration and then it should work. I tried it with b7 as 9:06, c2 = 1h, c3=1h, c4=1h, c5=0h 6m (having entered 1,1,1, and .1), then c7 summed them all to 3h 6m. Then d7 had the sum of b7 and c7 and came to 12:12. Each duration cell was formatted for just hours and minutes, not days or seconds.

EDIT, corrected math

Click to expand...

Thanks Jake! That does work! I'm that much closer to solving this equation. Unfortunately for me the source cells have to stay in a number format for the purpose of accounting. Ugh. Thank you SO much for taking the time to help me work this out.

PilotPat said:

Hi. I have searched for a few hours and have tried several methods all without luck. I have a cell (P12) that has a decimal time, say 3.1 and I need to sum that decimal time with another cell (J12) that has time displayed in as HH:MM. This calculation would be displayed in K12. Example below:

J12 = 09:06
P12 = 3.1 (3 hours 6 minutes)
K12 = would have the sum of J12 + P12 displayed as hh:mm

Anyone know how this can be accomplished? Thank you in advance!

Click to expand...

I finally got it! For anyone who is interested here is the formula that worked. Thank you to all that helped me out!!!

=J12+DURATION(,,P12)

Well done! Glad you got it sorted out.

MacInWin said:

Well done! Glad you got it sorted out.

Click to expand...

Thank you for all of your time Jake! May I ask one last question? How can I subtract .1 of a hour from a time value? basically, i'd like to take the 9:06 and subtract 6 minutes from it. I think i have over thought this all now and I'm having a brain fart.

Wouldn't the same formula work? So it would be 9:06 - Duration(,,.1)? You may need to fiddle with the number to get to where the number is interpreted as 6 minutes.

MacInWin said:

Wouldn't the same formula work? So it would be 9:06 - Duration(,,.1)? You may need to fiddle with the number to get to where the number is interpreted as 6 minutes.

Click to expand...

It sure did. I've been looking at this spreadsheet to long. Thanks again Jake!

Any idea on how to hide the error indication that is given until a value is set? The below formula calculates a similar time as the one previously discussed. However, it returns a error indication until a value has been set for N12. This only happens at the end of the week. is there a way to hide that error indication so it doesn't print/show on the spreadsheet? I know there is the "IFERROR" function but I can't seem to figure out how to use it in the same string as the formula.
=I12+DURATION(,,N12)

Sorry for all these questions. I am so used to MS Excel and I find that Numbers is quite different in a lot of regards and I'm trying to relearn a lot.

Well, there is a function called ISBLANK, so you could try to do an IF(ISBLANK(cell),whatever you want when it's empty, your formula for what it is when it's not blank). You'll need to fiddle with it and look up what you can put in the final two positions of the IF function. Or you can reverse and use If(ISBLANK(cell)<>TRUE,yada, yada) and have the first yada expression be what's there. I tried to sort it out but couldn't figure out the syntax to get past the error in the basic IF statement I put in. Maybe start with something simple like IF(ISBLANK(cell),0,1) just to get the If logical, then maybe do the calculation somewhere else and make it IF(ISBLANK(cell1),cell2,cell3) where cell2 and cell3 contain the value you want when it's blank (maybe a zero) and the final calculation. That way Cell3 may show an error if cell1 is blank, but it won't show where you need to view it. You can even move the calculations to a different sheet if it gets messy.