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?

--
David

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 wrote:
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?

--
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

--
David

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


--
Barrett9699
------------------------------------------------------------------------
Barrett9699's Profile: http://www.excelforum.com/member.php...o&userid=37573
View this thread: http://www.excelforum.com/showthread...hreadid=573298

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

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

Click OK.

Give that a try.


--
Barrett9699
------------------------------------------------------------------------
Barrett9699's Profile: http://www.excelforum.com/member.php...o&userid=37573
View this thread: http://www.excelforum.com/showthread...hreadid=573298

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.

--
David

Pete_UK wrote

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 wrote:
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?

--
David

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 wrote:
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.

--
David

Pete_UK wrote

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 wrote:
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?

--
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

Barrett9699 wrote


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

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

Click OK.

Give that a try.

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.

--
David

Pete_UK wrote

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 wrote:
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.

--
David

Pete_UK wrote

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 wrote:
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?

--
David

Oops. Sorry! Try this one:

0.00, -0.00, ""

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


--
Barrett9699
------------------------------------------------------------------------
Barrett9699's Profile: http://www.excelforum.com/member.php...o&userid=37573
View this thread: http://www.excelforum.com/showthread...hreadid=573298

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

Barrett9699 wrote


Oops. Sorry! Try this one:

0.00, -0.00, ""

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

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

--
David

Pete_UK wrote

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 wrote:
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.

--
David

Pete_UK wrote

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 wrote:
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?

--
David

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

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

--
David

Pete_UK wrote

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 wrote:
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.

--
David

Pete_UK wrote

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 wrote:
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?

--
David

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

--
David

Pete_UK wrote

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

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

--
David

Pete_UK wrote

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 wrote:
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.

--
David

Pete_UK wrote

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 wrote:
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?

--
David