Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Showing minus values (2003 SP2)

    Good morning

    In my post of yesterday (begining at <post#=685,387>post 685,387</post#> lots of you pitched in and helped me change a formula, whilst testing though I notice I no longer get negative numbers in Column I, In the attached example the driver started at 08:00 (E6) and finished at 17:00 (F6) which means he actually worked 9 hours (G6) and his actual shift hours should have been 10 (H6) so in I6 I used to get -1 and in I13 the total O/T hours taking into account the - and + hours from I6:I12 (ignoring any weekend hours). Likewise the result in I12 would have shown -4

    Can I change anything in the formula in I6:I12 to take this into account, if not it doesn't matter because I can manually adjust at the end of each month based on the figures in G and H13 but I though it would be nicer if it was automated.

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Showing minus values (2003 SP2)

    Hi Steve, here's my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> again.

    The formula from yesterday worked perfectly except that Hans and (other) Steve did not know to take negatives into account. The MAX function they used in the formula yesterday elimimates negatives, ie. 0 > -1, so 0 is the answer, not -1.

    I have gone back to the 'old' formula which seems to work OK agin if negatives must be allowed. There may be a better way as I am so proven many times, but in my testing on your sheet, this seems to work 'for now' <img src=/S/smile.gif border=0 alt=smile width=15 height=15> :
    =IF(E6="",0,IF(AND((G6-H6)*24<4,WEEKDAY(D6,2)>5),4,(G6-H6)*24))
    Regards,
    Rudi

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing minus values (2003 SP2)

    Hi Rudi

    Thanks for the quick response, your 2 cents are always worth it, and this works perfectly, I like to try and follow the thinking process when you and Jezza and the others try different things because it gives me a better insight into how different things work / work together even though they may not be the final or elegant end result it broadens my understanding

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Showing minus values (2003 SP2)

    I totally agree. I often jump in quickly to try and provide a solution. It it works for the poster... <img src=/S/joy.gif border=0 alt=joy width=23 height=23>. But as you say, I will always return to see the other answers to the query. Its interesting to see the variations and one can learn a heck of a lot with input from the lounge gurus! (I am one who knows this well!!!!)
    Regards,
    Rudi

Posting Permissions

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