# Thread: Showing minus values (2003 SP2)

1. ## 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

2. ## 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))

3. ## 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

4. ## 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!!!!)

#### Posting Permissions

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