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?

--
David

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

[Barrett9699]

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

[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

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

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

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

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

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

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

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

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

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

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

[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

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