Formula calculating differently after the number 4...

**Slodey** · 10-18-2011, 10:44 AM

Hi, my maiden post on the board! Although I imagine I'll be here quite a lot from now on!

I'm trying to set up a time sheet that will calculate the remaining time by summing two collums and subtracting them from each other. The formula I'm using looks like this:

=IF(SUM(D8:G8)=0,"",IF((ROUNDDOWN(H7,0))+D8-F8+((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60)>=0,(ROUNDDOWN(H7,0))+D8-F8+INT(((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60))+(((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60)-INT((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60))*0.6,IF((-0.6+1+(((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60)-INT((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60))*0.6)=0.4,(ROUNDDOWN(H7,0))+D8-F8+INT(((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60))+(-(((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60)-INT((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60))*0.6),(ROUNDDOWN(H7,0))+D8-F8+INT(((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60))+(-0.6+1+(((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60)-INT((((H7-ROUNDDOWN(H7,0))*100)+E8-G8)/60))*0.6))))

I know it's a long formula, but it's imperative that the whole calculation is done in one cell! This formula uses the cell above to create a running total down in one column including new numbers from the columns to the left.

The formula works well...until it reaches the number four, at which point instead of adding the hour onto the total and resetting the decimal to 0, it keeps the decimal as 60 and doesn't add it on as it was for 3 and below. As far as I can see the formula is exactly the same in each cell and I've got absolutely no idea how it's happening.

Has anyone encountered this before, or does anybody know of a fix? The only workaround I can think of is an IF that checks for the 0.6 and converts it to a 1, but I think I'll run out of characters trying that!

Thanks in advance for any help

**JBeaucaire** · 10-18-2011, 10:54 AM

Hi Slodey. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook so we can SEE your formula failing. Point out the cell if it's not evident, show us in another column what the results "should be" based on your goals.

**Slodey** · 10-18-2011, 11:16 AM

Hi, thanks for replying!

I've attached a copy of my sheet. It's column H that the problem is occuring in, I've marked the occurences in red. Column I is the same formula without the *0.6 that I've used to get the minute reading. Column J is what the result should be.

Thanks for any help you can give!

**daddylonglegs** · 10-18-2011, 11:34 AM

I would recommend that you consider using real time values rather than splitting into minutes and hours, that makes all your calculations more convoluted, as you've discovered.

Having said that....this formula in H8 copied down should work without the problems you are seeing

=IF(COUNT(D7:G7)=0,"",SUM(D$7:D7)-SUM(F$7:F7)+INT((SUM(E$7:E7)-SUM(G$7:G7))/60)+MOD((SUM(E$7:E7)-SUM(G$7:G7)),60)/100)

**Slodey** · 10-19-2011, 04:02 AM

Thanks for replying. That formula has solved my problem! In future I'll look into using more efficient/less clunky formulas.

Thanks again! I'll probably be back soon with other issues! Haha

**Slodey** · 10-19-2011, 04:49 AM

Hi again,

In the formula I was originally using, when minus numbers were displayed I added a few lines to keep them displaying as a time value (ie: -0.15 instead of -0.85, -0.30 instead of -0.70). Is there any way that I could apply this to the new formula?

Thanks again for all your help!