Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    4 Star Lounger
    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.....

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    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

  5. #5
    5 Star Lounger
    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 W8-V8 which is equivalent to (0-15:15), thus a negative.
    Cell G9 is negative because part of the SUM formula is V9-U9 which is equivalent to (0-14: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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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,L8-K8),max(0,N8-M8),......,max(0,AV8-AU8),max(0,AX8-AW8))


    and in H8 like:
    =SUM(max(0,M8-L8),max(0,O8-N8),....,max(0,AU8-AT8),max(0,AW8-AV8))

    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 (W8-V8) 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, w8-v8) 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

  7. #7
    5 Star Lounger
    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)

  8. #8
    4 Star Lounger
    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 question---whenever 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.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    4 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •