Don't want 0.00

[David]

I have a timesheet workbook.
Column N contains hours worked
Columns S-V is where I enter other types of paid leave used

I use this formula in H22 to calculate how much vacation time I used in a
pay period and it works fine with one exception:

=MAX(0,80-(SUM(N8:N12,N15:N19))-SUM(S8:V12,S15:V19))

The exception occurs if I work any total of 80 hours

Problem: with this scenario 0.00 is displayed in H22 rather than remaining
blank (I have 'Window options' > 'Zero values' unchecked).

How can I keep that from happening?

 

[Pete_UK]

You could apply conditional formatting - if the cell contents are zero
then use white for the foreground colour so that it doesn't show if you
have a white background.

Hope this helps.

Pete

 

[David]

David wrote

=MAX(0,80-(SUM(N8:N12,N15:N19))-SUM(S8:V12,S15:V19))

Actually, I had to change the formula to:
=MAX(0,40-(SUM(N7:N13))-SUM(S7:V13))+MAX(0,40-(SUM(N14:N20))-SUM(S14:V20))
because used Annual Time is calculated in seperate weeks

 

[Barrett9699]

How about building a custom number format with "" for the zero value

 

[Barrett9699]

Yeah, select cell H22, go Format> Cells... click Number tab... Catagory:
custom... Type:

#.##; -#.##; ""

Click OK.

Give that a try.

 

[David]

Well, that fixed the half that displays 80.00 if the sheet hasn't had any
hours entered yet, but still get 0.00 if 80 or more hours are worked

I set two conditions: Cell Value Is equal to 80 and Cell Value Is equal to
0 and set Font color (tried Pattern, too) to White.

 

[Pete_UK]

If it isn't exactly 0, then the effect won't happen - you might have
0.00001 but if the cell is formatted as 0.00 you won't see this. Change
the condition to "Is Less than" 0.01 and see if that works.

Pete

 

[David]

Well, that got close. Effect is that it leaves a '.' (decimal point) when
it evaluates to 0.00 hours, and if 8.00 hrs, '8.'

Format I've been using is Number w/2 decimal places

 

[David]

Hmm... Yep, that works. I don't understand how 80.00 hrs or more worked
doesn't result in exactly 0 hrs annual time used, though. I use
increments of 15 minutes for time worked. No partial minutes involved.

 

[Barrett9699]

Oops. Sorry! Try this one:

0.00, -0.00, ""

I tested is this time, and I think it's exactly what you want. Let m
know if it's not

 

[David]

I actually tried that while waiting for further responses - no joy, even
after changing commas to semicolons

Seems to me it would be the same as Number with 2 decimal places anyway,
would it not?

That said, I think I've solved this by adding INT in front of my formula:

=INT(MAX(0,40-(SUM(N7:N13))-SUM(S7:V13))+MAX(0,40-(SUM(N14:N20))-SUM
(S14:V20)))

 

[David]

See my reply to Barrett9699 re: adding INT to beginning of my formula.
Still doesn't explain why it isn't an integer already, though.

 

[Pete_UK]

It may be due to rounding errors - Excel works in binary, so maybe
fractions of an hour can't be expressed with complete accuracy, and
these very small differences will add up over a number of calculations.

Anyway, I'm glad you got it sorted in the end.

Pete

See my reply to Barrett9699 re: adding INT to beginning of my formula.
Still doesn't explain why it isn't an integer already, though.

 

[David]

Oops! Using INT had unwanted result of eliminating any quarter hours
from result. Switched to Round(),2) instead.