Results 1 to 10 of 10
Thread: Formula returns ######

20100918, 16:07 #1
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
H loungers....I am doing some calculation of time...in the attached sample, I want to total all of the working time and all of the break time.....using a simple SUM formula produces ##### whenever I make an entry....I beleve that the correct formula should have something like ISERROR or some such IS formula, but the HELP files in Excel are almost useless....can someone suggest the proper formula for use in columns G and H...and in column J, I can't get it to round properly....on Jan 4, there were 6 breaks...on Jan 5, there were 5 breaks, and even though I am using a ROUND formula, it won't round to 5....any suggestions? Thank you

20100918, 17:04 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The formula returns a negative number. Custom formats can not deal with negative time since it is meaningless as a time of day or an elapsed time.
If you truly need the custom format to be able to use negative time values you must set the option to use 1904 date system [tools  options  calculation(tab)  check "1904 date system" near the bottom]...
Steve

20100918, 17:12 #3
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Hi Steve...I don't need negative numbers; in fact, there would never be negative numbers...I used the custom format b/c that;s the only way I know how to format time or calculation of time.....

20100918, 17:48 #4
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
If you look at the result of the formula in G9 on your sheet as a number it returns 0.45
Which as Steve points out is a Negative number, because you are using addition and subtraction in your formula,
even though you don't need negative numbers, you are getting them hence the ################# result.
IF you are saying there will be negatives, but you do not want them, then you will need to use an IF to make sure that they
are eliminated
e.g. =IF(Your Formula <0,"",Your Formula) or equivalent
Also you have a Custom Format of hh:mm;@
If the total time is likely to exceed 24 hours and you want it in hours and minutes
then you should choose to use [h]:mm as the time component.
Whatever you do, you need to stop the negative calculations or you will get ##########.Andrew

20100918, 18:48 #5
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hello  As pointed out by both Steve and Andrew, the problem is with calculated negative numbers. The causes of the negative numbers are as follows:
Cell H8 is negative because part of the SUM formula is W8V8 which is equivalent to (015:15), thus a negative.
Cell G9 is negative because part of the SUM formula is V9U9 which is equivalent to (014:00), thus a negative.
Col I is negative because of the negatives in Col G and H.
As for the ROUND issue, on Jan 05 there is a Start of 14:00 with no Stop, thus the .5 part of the calculation. If you want to round down in those situations, try this ....
=ROUNDDOWN(COUNTIF(K9:AX9,">0")/2,0)
Hope that helps.

20100919, 07:00 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Perhaps you are looking for a formula in G8 like:
=SUM(max(0,L8K8),max(0,N8M8),......,max(0,AV8AU8),max(0,AX8AW8))
and in H8 like:
=SUM(max(0,M8L8),max(0,O8N8),....,max(0,AU8AT8),max(0,AW8AV8))
These will get rid of the negative inermediate numbers when the start or stop is blank and you subtract a value from the blank. Since the blank is treated as a zero any number subtracted from zero will be negative. In your example when W8 is blank then (W8V8) is negative. In these cases you do not want to add this to your sum since there is no next start time. These need to be ignored. The Max(0, w8v8) will yield Max(0, negative number) which results in zero. If the difference of the number is greater than zero, than that number will be added in the sum.
Steve

20100919, 09:59 #7
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hello  A general comment ...
It would seem that if a shift has a Start, it must also have a Stop. If not, you would want an error to show in calculation of Column G "TOTAL TIME WORKING" and also in Column I TOTAL TIME.
These formulas should work and can be copied down in each column......
In cell G8 try =SUMIF(K$6:AX$6,"STOP",K8:AX8)SUMIF(K$6:AX$6,"START",K8:AX8)
When that is copied to G9 it will appear as #######, which could indicate a missing STOP time.
In cell H8 try =IF(MOD((COUNTIF(K8:AX8,">0")/2),1),0,MAX(M8:AX8))+SUMIF(M$6:AX$6,"START",M8:AX8 )SUMIF(L$6:AX$6,"STOP",L8:AX8)

20100919, 11:11 #8
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Thank you, all, for your help with this....sorry to be so dense, b/c most of these now seem obvious, but when you're in the swamp full of alligators, as they say...)....I do have a formatting questionwhenever I want to set up to enter phone numbers, I select FORMAT and SPECIAL and choose phone number (which automatically inserts brackets around the area code and the hyphen as needed......is there such a custom or special format that anyone knows of that would permit me to enter a time (eg: 10:36) and will insert the colon b/w the hours and minutes....this isn't a big thing, but it makes it much faster to enter 1036 (and have it shows as 10:36) than have to type the colon....any suggestions? Thanks.

20100919, 13:15 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
There is no builtin way to do this. you can do it with VB code and some examples are at http://www.cpearson.com/excel/DateTimeEntry.htm
But be warned, the worksheet change codes can make a worksheet sluggish and also disable the ability to UNDO.
Steve

20100929, 11:19 #10
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Hi loungers....thank you for your help with this...sorry to be late in replying, but I was travelling......I think that I have everything pretty much sorted out and this thread can now be closed. Thanks again.