I have a great spreadsheet that's really been a life-saver. Just one thing
I'd like to fine-tune. In column G, specifically in cell G3, I have the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny in one
way: to show "negative" hours in results, I changed to the 1904 system for
this workbook as per advice from this ng. So that works beautifully. When
less time is worked during day, the total regular hours worked show as a
negative which is then taken into account in the "overtime". The actual
overtime worked, then, shows "real" overtime worked and not just a result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as 0h00m and
sometimes it shows up as -0h00m. I haven't figured out why it sometimes
puts that negative sing in. Ultimately, the mathematical end result of "0"
is the same, of course, but that makes this column awkward. It was this in
part that prompted me to wish this formula displayed results differently.

Since this is a visual check calculation only in this cell so that I can see
if a workday has been worked in its entirety, it actually would be better if
the above code only displayed for any other value other than "0". Is there
a way, then, to incorporate that into the above formula? i.e., that the
cell will be also be completely blank unless there is a resulting value
other than 0h00m or -0h00m?

Thanks much! :oD

This reason for negative zeros may be something to do with rounding - i.e.
although it says -0.00 it may actually be -0.00000001 -- Try copying the
cell and doing paste special values and then increase the decimal places.

I think this is likely to be the reason as it is mathematically impossible
to have a negative zero and I presume that Excel is governed by this
mathematical principal as well.

With regards to your second problem try this in cell G3

=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver. Just one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I have the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny in one
way: to show "negative" hours in results, I changed to the 1904 system
for
this workbook as per advice from this ng. So that works beautifully.
When
less time is worked during day, the total regular hours worked show as a
negative which is then taken into account in the "overtime". The actual
overtime worked, then, shows "real" overtime worked and not just a result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as 0h00m and
sometimes it shows up as -0h00m. I haven't figured out why it sometimes
puts that negative sing in. Ultimately, the mathematical end result of
"0"
is the same, of course, but that makes this column awkward. It was this
in
part that prompted me to wish this formula displayed results differently.

Since this is a visual check calculation only in this cell so that I can
see
if a workday has been worked in its entirety, it actually would be better
if
the above code only displayed for any other value other than "0". Is
there
a way, then, to incorporate that into the above formula? i.e., that the
cell will be also be completely blank unless there is a resulting value
other than 0h00m or -0h00m?

Thanks much! :oD

I actually mentioned the -0 in case it might make a difference to the
formula in keeping column G empty unless value is not 0 (whether negative or
not <g). Do you think I can just leave it as is? The workbook seems to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however. About the
only thing I figured out to do is to fix the word wrap. I read all the
paragraphs in help as prompted under "I have an error in my formula." but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with rounding - i.e.
although it says -0.00 it may actually be -0.00000001 -- Try copying the
cell and doing paste special values and then increase the decimal
places.

I think this is likely to be the reason as it is mathematically impossible
to have a negative zero and I presume that Excel is governed by this
mathematical principal as well.

With regards to your second problem try this in cell G3


=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver. Just one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I have the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny in
one
way: to show "negative" hours in results, I changed to the 1904 system
for
this workbook as per advice from this ng. So that works beautifully.
When
less time is worked during day, the total regular hours worked show as a
negative which is then taken into account in the "overtime". The actual
overtime worked, then, shows "real" overtime worked and not just a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as 0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it sometimes
puts that negative sing in. Ultimately, the mathematical end result of
"0"
is the same, of course, but that makes this column awkward. It was this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so that I can
see
if a workday has been worked in its entirety, it actually would be
better
if
the above code only displayed for any other value other than "0". Is
there
a way, then, to incorporate that into the above formula? i.e., that the
cell will be also be completely blank unless there is a resulting value
other than 0h00m or -0h00m?

Thanks much! :oD

SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't reproduce
your spreadsheet so I'm not sure, but this might be what you're looking for:

=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to the
formula in keeping column G empty unless value is not 0 (whether negative
or
not <g). Do you think I can just leave it as is? The workbook seems to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however. About
the
only thing I figured out to do is to fix the word wrap. I read all the
paragraphs in help as prompted under "I have an error in my formula." but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try copying the
cell and doing paste special values and then increase the decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by this
mathematical principal as well.

With regards to your second problem try this in cell G3


=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver. Just one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny in
one
way: to show "negative" hours in results, I changed to the 1904 system
for
this workbook as per advice from this ng. So that works beautifully.
When
less time is worked during day, the total regular hours worked show as
a
negative which is then taken into account in the "overtime". The
actual
overtime worked, then, shows "real" overtime worked and not just a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as 0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end result of
"0"
is the same, of course, but that makes this column awkward. It was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so that I
can
see
if a workday has been worked in its entirety, it actually would be
better
if
the above code only displayed for any other value other than "0". Is
there
a way, then, to incorporate that into the above formula? i.e., that
the
cell will be also be completely blank unless there is a resulting value
other than 0h00m or -0h00m?

Thanks much! :oD

Hi!

Well, that certainly fixed the error. That nested IF seems to have been
needed. Unfortunately, the additions didn't work as all "0" values still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial formula I
posted has a small incorrect value that I can't explain as I didn't type in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even once fixed,
all values are still being displayed instead of just the ones resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're looking
for:


=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however. About
the
only thing I figured out to do is to fix the word wrap. I read all the
paragraphs in help as prompted under "I have an error in my formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try copying
the
cell and doing paste special values and then increase the decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by this
mathematical principal as well.

With regards to your second problem try this in cell G3



=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver. Just one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny in
one
way: to show "negative" hours in results, I changed to the 1904
system
for
this workbook as per advice from this ng. So that works beautifully.
When
less time is worked during day, the total regular hours worked show
as
a
negative which is then taken into account in the "overtime". The
actual
overtime worked, then, shows "real" overtime worked and not just a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end result
of
"0"
is the same, of course, but that makes this column awkward. It was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so that I
can
see
if a workday has been worked in its entirety, it actually would be
better
if
the above code only displayed for any other value other than "0". Is
there
a way, then, to incorporate that into the above formula? i.e., that
the
cell will be also be completely blank unless there is a resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

SFFW,

I forgot to mention that, as Andi said earlier in this thread, the values
are probably .0000001 or something so they don't evaluate to zero. You
sometimes get these types of errors in Excel (and other programs). It has
to do with the way doubles are calculated or something like that. Try
googling "double precision error excel". To fix your formula, you could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to have been
needed. Unfortunately, the additions didn't work as all "0" values still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial formula I
posted has a small incorrect value that I can't explain as I didn't type
in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even once
fixed,
all values are still being displayed instead of just the ones resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're looking
for:


=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however. About
the
only thing I figured out to do is to fix the word wrap. I read all the
paragraphs in help as prompted under "I have an error in my formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try copying
the
cell and doing paste special values and then increase the decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by this
mathematical principal as well.

With regards to your second problem try this in cell G3



=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver. Just
one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny
in
one
way: to show "negative" hours in results, I changed to the 1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours worked show
as
a
negative which is then taken into account in the "overtime". The
actual
overtime worked, then, shows "real" overtime worked and not just a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end result
of
"0"
is the same, of course, but that makes this column awkward. It was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so that I
can
see
if a workday has been worked in its entirety, it actually would be
better
if
the above code only displayed for any other value other than "0".
Is
there
a way, then, to incorporate that into the above formula? i.e., that
the
cell will be also be completely blank unless there is a resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

Okay, I _think_ I understand. I _do_ have to fix this -0 value after all so
good thing I mentioned it. I'm guessing that once I do, that secondary
formula will work (?). Okay. Unfortunately, the doubles correction formula
you provide below isn't working either <damn. I added a "=" in front of
the "IF(Round..." because without it, the entire formula is all that is
displayed in the cell rather than any numerical result. But after adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula, and then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work on a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the values
are probably .0000001 or something so they don't evaluate to zero. You
sometimes get these types of errors in Excel (and other programs). It has
to do with the way doubles are calculated or something like that. Try
googling "double precision error excel". To fix your formula, you could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to have been
needed. Unfortunately, the additions didn't work as all "0" values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial formula I
posted has a small incorrect value that I can't explain as I didn't type
in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even once
fixed,
all values are still being displayed instead of just the ones resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're looking
for:



=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however.
About
the
only thing I figured out to do is to fix the word wrap. I read all
the
paragraphs in help as prompted under "I have an error in my formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by this
mathematical principal as well.

With regards to your second problem try this in cell G3




=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver. Just
one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny
in
one
way: to show "negative" hours in results, I changed to the 1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours worked
show
as
a
negative which is then taken into account in the "overtime". The
actual
overtime worked, then, shows "real" overtime worked and not just a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end
result
of
"0"
is the same, of course, but that makes this column awkward. It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so that
I
can
see
if a workday has been worked in its entirety, it actually would be
better
if
the above code only displayed for any other value other than "0".
Is
there
a way, then, to incorporate that into the above formula? i.e.,
that
the
cell will be also be completely blank unless there is a resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

I'm like a dog with a bone <g ... I couldn't let this one go. I don't
know if what I've done is correct, but I kept reading a bit and then went
back to fiddle with the formula and, so far, every test I've run plugging in
different times has worked. No more -0h00m, they're all showing up as 0h00m
when applicable yet also, when previous cells are blank, the cells with this
calculation are blank, too. So far, win-win. Here is the formula as I
changed it that shows up in cell G2, for example:

=IF(F2<"",ROUND(F2-SUM(D2-C2)-TIME(7,30,0),5),"")

(And, yes, me too I preferred to use the ROUND rather than fiddling with
settings in Excel for the spreadsheet.)

When I used the "2" in the above formula as given earlier, the calculations
were always off when not a "0" result. Using the "5" as the last digit in
the formula above instead of 2 has resulted in correct values in each test
done so far. Don't know if using that 5 was the correct thing to do but
obviously the incorrect results were unacceptable <g.

So if the above is as correct as it seems to be so far, _then_ it's just a
question of figuring out how to change the formula above so that _only_
non-0 values display. Hopefully now that there aren't any -0h00m anywhere,
any coding will now work <g.

:oD

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value after all
so
good thing I mentioned it. I'm guessing that once I do, that secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in front of
the "IF(Round..." because without it, the entire formula is all that is
displayed in the cell rather than any numerical result. But after adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula, and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work on a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero. You
sometimes get these types of errors in Excel (and other programs). It
has
to do with the way doubles are calculated or something like that. Try
googling "double precision error excel". To fix your formula, you could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to have
been
needed. Unfortunately, the additions didn't work as all "0" values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial formula
I
posted has a small incorrect value that I can't explain as I didn't
type
in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:




=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to
the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however.
About
the
only thing I figured out to do is to fix the word wrap. I read all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by
this
mathematical principal as well.

With regards to your second problem try this in cell G3





=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are
funny
in
one
way: to show "negative" hours in results, I changed to the 1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours worked
show
as
a
negative which is then taken into account in the "overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end
result
of
"0"
is the same, of course, but that makes this column awkward. It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so
that
I
can
see
if a workday has been worked in its entirety, it actually would
be
better
if
the above code only displayed for any other value other than
"0".
Is
there
a way, then, to incorporate that into the above formula? i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

SFFW,

The code that I showed in the last post was just a snippet that you would
substitute into the larger formula:

=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value after all
so
good thing I mentioned it. I'm guessing that once I do, that secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in front of
the "IF(Round..." because without it, the entire formula is all that is
displayed in the cell rather than any numerical result. But after adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula, and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work on a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the values
are probably .0000001 or something so they don't evaluate to zero. You
sometimes get these types of errors in Excel (and other programs). It
has
to do with the way doubles are calculated or something like that. Try
googling "double precision error excel". To fix your formula, you could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to have
been
needed. Unfortunately, the additions didn't work as all "0" values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial formula I
posted has a small incorrect value that I can't explain as I didn't
type
in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even once
fixed,
all values are still being displayed instead of just the ones resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:



=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however.
About
the
only thing I figured out to do is to fix the word wrap. I read all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by
this
mathematical principal as well.

With regards to your second problem try this in cell G3




=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver. Just
one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are
funny
in
one
way: to show "negative" hours in results, I changed to the 1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours worked
show
as
a
negative which is then taken into account in the "overtime". The
actual
overtime worked, then, shows "real" overtime worked and not just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end
result
of
"0"
is the same, of course, but that makes this column awkward. It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so
that
I
can
see
if a workday has been worked in its entirety, it actually would
be
better
if
the above code only displayed for any other value other than "0".
Is
there
a way, then, to incorporate that into the above formula? i.e.,
that
the
cell will be also be completely blank unless there is a resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed the
last digit of "2" to "5" and the values then show up correctly (in my very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you would
substitute into the larger formula:


=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value after
all
so
good thing I mentioned it. I'm guessing that once I do, that secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in front
of
the "IF(Round..." because without it, the entire formula is all that is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula, and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero. You
sometimes get these types of errors in Excel (and other programs). It
has
to do with the way doubles are calculated or something like that. Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to have
been
needed. Unfortunately, the additions didn't work as all "0" values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial formula
I
posted has a small incorrect value that I can't explain as I didn't
type
in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:




=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to
the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however.
About
the
only thing I figured out to do is to fix the word wrap. I read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by
this
mathematical principal as well.

With regards to your second problem try this in cell G3





=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell G3, I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are
funny
in
one
way: to show "negative" hours in results, I changed to the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours worked
show
as
a
negative which is then taken into account in the "overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not
just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end
result
of
"0"
is the same, of course, but that makes this column awkward. It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so
that
I
can
see
if a workday has been worked in its entirety, it actually would
be
better
if
the above code only displayed for any other value other than
"0".
Is
there
a way, then, to incorporate that into the above formula? i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed the
last digit of "2" to "5" and the values then show up correctly (in my very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you would
substitute into the larger formula:


=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value after
all
so
good thing I mentioned it. I'm guessing that once I do, that secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in front
of
the "IF(Round..." because without it, the entire formula is all that is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula, and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work
on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero.
You
sometimes get these types of errors in Excel (and other programs). It
has
to do with the way doubles are calculated or something like that. Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to have
been
needed. Unfortunately, the additions didn't work as all "0" values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I didn't
type
in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:




=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to
the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however.
About
the
only thing I figured out to do is to fix the word wrap. I read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by
this
mathematical principal as well.

With regards to your second problem try this in cell G3





=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell G3,
I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are
funny
in
one
way: to show "negative" hours in results, I changed to the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours worked
show
as
a
negative which is then taken into account in the "overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not
just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why it
sometimes
puts that negative sing in. Ultimately, the mathematical end
result
of
"0"
is the same, of course, but that makes this column awkward.
It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so
that
I
can
see
if a workday has been worked in its entirety, it actually
would
be
better
if
the above code only displayed for any other value other than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

2 things that help me:

instead of looking for 0, I look for <0.01

another is in the If
If(x=y,z,)

notice the blank after z,
this tends to leave the cell nicely blank in appearance and in value.

--
steveB

Remove "AYN" from email to respond
"Doug Glancy" wrote in message
...
Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed the
last digit of "2" to "5" and the values then show up correctly (in my
very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you
would
substitute into the larger formula:


=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value after
all
so
good thing I mentioned it. I'm guessing that once I do, that
secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in front
of
the "IF(Round..." because without it, the entire formula is all that
is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula,
and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work
on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero.
You
sometimes get these types of errors in Excel (and other programs).
It
has
to do with the way doubles are calculated or something like that.
Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to have
been
needed. Unfortunately, the additions didn't work as all "0" values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I didn't
type
in
the values. The F4 below should be F3, as can logically be seen in
hindsight <g. Still, that didn't affect the end result. Even
once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:




=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference to
the
formula in keeping column G empty unless value is not 0 (whether
negative
or
not <g). Do you think I can just leave it as is? The workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work, however.
About
the
only thing I figured out to do is to fix the word wrap. I read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is mathematically
impossible
to have a negative zero and I presume that Excel is governed by
this
mathematical principal as well.

With regards to your second problem try this in cell G3





=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell G3,
I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are
funny
in
one
way: to show "negative" hours in results, I changed to the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours
worked
show
as
a
negative which is then taken into account in the "overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not
just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why
it
sometimes
puts that negative sing in. Ultimately, the mathematical end
result
of
"0"
is the same, of course, but that makes this column awkward.
It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell so
that
I
can
see
if a workday has been worked in its entirety, it actually
would
be
better
if
the above code only displayed for any other value other than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

Steve,

If I use an If as you describe with the False argument not specified, I get
a zero in the cell.

Doug

"STEVE BELL" wrote in message
news:fRaLe.1312$Xw5.910@trnddc02...
2 things that help me:

instead of looking for 0, I look for <0.01

another is in the If
If(x=y,z,)

notice the blank after z,
this tends to leave the cell nicely blank in appearance and in value.

--
steveB

Remove "AYN" from email to respond
"Doug Glancy" wrote in message
...
Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed
the
last digit of "2" to "5" and the values then show up correctly (in my
very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you
would
substitute into the larger formula:


=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value after
all
so
good thing I mentioned it. I'm guessing that once I do, that
secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in
front
of
the "IF(Round..." because without it, the entire formula is all that
is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula,
and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work
on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero.
You
sometimes get these types of errors in Excel (and other programs).
It
has
to do with the way doubles are calculated or something like that.
Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to
have
been
needed. Unfortunately, the additions didn't work as all "0"
values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I
didn't
type
in
the values. The F4 below should be F3, as can logically be seen
in
hindsight <g. Still, that didn't affect the end result. Even
once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I
didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:




=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference
to
the
formula in keeping column G empty unless value is not 0
(whether
negative
or
not <g). Do you think I can just leave it as is? The
workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work,
however.
About
the
only thing I figured out to do is to fix the word wrap. I read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is
mathematically
impossible
to have a negative zero and I presume that Excel is governed
by
this
mathematical principal as well.

With regards to your second problem try this in cell G3





=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell
G3, I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are
funny
in
one
way: to show "negative" hours in results, I changed to the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours
worked
show
as
a
negative which is then taken into account in the "overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not
just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column
displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why
it
sometimes
puts that negative sing in. Ultimately, the mathematical
end
result
of
"0"
is the same, of course, but that makes this column awkward.
It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell
so
that
I
can
see
if a workday has been worked in its entirety, it actually
would
be
better
if
the above code only displayed for any other value other than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

Doug,

This is true (forgot that). But it is a clean zero and won't affect some
calculations the way
"" in a cell can.

Also - if you don't want to display 0 you can always set Tools Options to
not display zero values.

This gets around the problem of values less than 0.01 from displaying +/- 0
values.

Just my 0.001 cents worth...

--
steveB

Remove "AYN" from email to respond
"Doug Glancy" wrote in message
...
Steve,

If I use an If as you describe with the False argument not specified, I
get a zero in the cell.

Doug

"STEVE BELL" wrote in message
news:fRaLe.1312$Xw5.910@trnddc02...
2 things that help me:

instead of looking for 0, I look for <0.01

another is in the If
If(x=y,z,)

notice the blank after z,
this tends to leave the cell nicely blank in appearance and in value.

--
steveB

Remove "AYN" from email to respond
"Doug Glancy" wrote in message
...
Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed
the
last digit of "2" to "5" and the values then show up correctly (in my
very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you
would
substitute into the larger formula:


=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value
after
all
so
good thing I mentioned it. I'm guessing that once I do, that
secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in
front
of
the "IF(Round..." because without it, the entire formula is all that
is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula,
and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I
work on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero.
You
sometimes get these types of errors in Excel (and other programs).
It
has
to do with the way doubles are calculated or something like that.
Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to
have
been
needed. Unfortunately, the additions didn't work as all "0"
values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I
didn't
type
in
the values. The F4 below should be F3, as can logically be seen
in
hindsight <g. Still, that didn't affect the end result. Even
once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I
didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:




=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference
to
the
formula in keeping column G empty unless value is not 0
(whether
negative
or
not <g). Do you think I can just leave it as is? The
workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work,
however.
About
the
only thing I figured out to do is to fix the word wrap. I
read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is
mathematically
impossible
to have a negative zero and I presume that Excel is governed
by
this
mathematical principal as well.

With regards to your second problem try this in cell G3





=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in
message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell
G3, I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results
are
funny
in
one
way: to show "negative" hours in results, I changed to the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours
worked
show
as
a
negative which is then taken into account in the
"overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not
just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column
displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why
it
sometimes
puts that negative sing in. Ultimately, the mathematical
end
result
of
"0"
is the same, of course, but that makes this column awkward.
It
was
this
in
part that prompted me to wish this formula displayed
results
differently.

Since this is a visual check calculation only in this cell
so
that
I
can
see
if a workday has been worked in its entirety, it actually
would
be
better
if
the above code only displayed for any other value other
than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

Just my 0.001 cents worth...

heh heh.

Thanks,

Doug

Well, I've worked with the spreadsheet for a few days now. It's going well.
The rounding out now seems to make all formulas work properly.

I just learned how to use cell formatting for negative values and changing
colour, so when a solution to this issue came to me after this thread, I
felt I'd have saved a lot of grief if I'd know of it and used this solution
before! :oD See, when I made the changes given in this great cell for
results ending in "0", they completely knocked out the final formula as this
crucial cell was now "empty" of value, if I can call it that. But an
extremely simple solution occurred to me that just needs to be tweaked in a
way I don't know how to do. I changed the cell formatting so that the font
colour is the same as the background while I also changed the negative
values to "cyan", which shows up great against the dark grey. So when
there's a negative value, I see a blue figure whereas the "0" doesn't stand
out at all. Yet the final formula operates because the "0" value is still
actually there (brilliant, I thought <g!). This part is perfect. However,
is there a way to further enhance the colour feature to show numbers greater
than zero as yet _another_ colour? One of the standard Excel colours would
be fine, though perhaps red is best. This will be great for me to see if I
accidentally mis-type a number, otherwise, my real overtime will not be
correctly reflected and I won't get paid what I should! <vbg Not a
desirable thing!

So, in a nutshell, the rounding off is perfect and the formulas all seem to
be working fine.
When I then get the target value of "0", the font colour is hidden by the
background colour. If I didn't work a full day, I get a negative value in
this cell which shows up as blue which allows me to see how much will go
against overtime I work (not a day goes by when I don't have to work
overtime <g!). However, if I accidentally mis-type a figure in the "before
overtime" cells, they won't show up either just like "0" values as the font
for these is the same colour as the "0's" so I need to have the positive
values in this cell, i.e. the +0 values, to show up in red (preferably).

Can this be done?


My formulas are these:

1) Cell G14 which has the formula that worries about the "0" values looks
like this -

=IF(F14<"",ROUND(F14-SUM(D14-C14)-TIME(7,30,0),5),"")

2) the format of the cells is this so that negative values show up as
cyan -

h"h"mm"m";[Cyan]-h"h"mm"m"

Thanks so much!


"Doug Glancy" wrote in message
...
Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed
the
last digit of "2" to "5" and the values then show up correctly (in my
very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you
would
substitute into the larger formula:



=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value after
all
so
good thing I mentioned it. I'm guessing that once I do, that
secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in
front
of
the "IF(Round..." because without it, the entire formula is all that
is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula,
and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I work
on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero.
You
sometimes get these types of errors in Excel (and other programs).
It
has
to do with the way doubles are calculated or something like that.
Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to
have
been
needed. Unfortunately, the additions didn't work as all "0"
values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I
didn't
type
in
the values. The F4 below should be F3, as can logically be seen
in
hindsight <g. Still, that didn't affect the end result. Even
once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I
didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:





=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference
to
the
formula in keeping column G empty unless value is not 0
(whether
negative
or
not <g). Do you think I can just leave it as is? The
workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work,
however.
About
the
only thing I figured out to do is to fix the word wrap. I read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is
mathematically
impossible
to have a negative zero and I presume that Excel is governed
by
this
mathematical principal as well.

With regards to your second problem try this in cell G3






=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell
G3,
I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are
funny
in
one
way: to show "negative" hours in results, I changed to the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours
worked
show
as
a
negative which is then taken into account in the "overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not
just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column
displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why
it
sometimes
puts that negative sing in. Ultimately, the mathematical
end
result
of
"0"
is the same, of course, but that makes this column awkward.
It
was
this
in
part that prompted me to wish this formula displayed results
differently.

Since this is a visual check calculation only in this cell
so
that
I
can
see
if a workday has been worked in its entirety, it actually
would
be
better
if
the above code only displayed for any other value other than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

On Sat, 13 Aug 2005 14:42:46 GMT, "STEVE BELL"
wrote:

Doug,

This is true (forgot that). But it is a clean zero and won't affect some
calculations the way
"" in a cell can.

Also - if you don't want to display 0 you can always set Tools Options to
not display zero values.

This gets around the problem of values less than 0.01 from displaying +/- 0
values.

Just my 0.001 cents worth...

Hi! Sorry, these messages didn't show up at work where I'm using OE
(Agent at home) so only just found them now.

"" in the cell I guess is the problem I've been having in formulas
later in the row. Since there is no value at all, I get errors in the
formulas. The value might just be a "0", but the formulas need even
that to work, and I'm nowhere near advanced enough in Excel to figure
out a workaround so figuring out how to get "0" values to not show up,
i.e., via a colours solution as stated in my earlier post of today,
seems ideal. But I will check this out now.

I would recommend that you look at conditional formatting
(FormatConditional Formatting). This allows you to choose 3 conditions and
associate different formatting for each condition. Have a quick read of the
help file as well.

I have always used conditional formatting - it never occured to me to use
your method - very inventive.

"StargateFanFromWork" wrote in message
...
Well, I've worked with the spreadsheet for a few days now. It's going
well.
The rounding out now seems to make all formulas work properly.

I just learned how to use cell formatting for negative values and changing
colour, so when a solution to this issue came to me after this thread, I
felt I'd have saved a lot of grief if I'd know of it and used this
solution
before! :oD See, when I made the changes given in this great cell for
results ending in "0", they completely knocked out the final formula as
this
crucial cell was now "empty" of value, if I can call it that. But an
extremely simple solution occurred to me that just needs to be tweaked in
a
way I don't know how to do. I changed the cell formatting so that the
font
colour is the same as the background while I also changed the negative
values to "cyan", which shows up great against the dark grey. So when
there's a negative value, I see a blue figure whereas the "0" doesn't
stand
out at all. Yet the final formula operates because the "0" value is still
actually there (brilliant, I thought <g!). This part is perfect.
However,
is there a way to further enhance the colour feature to show numbers
greater
than zero as yet _another_ colour? One of the standard Excel colours
would
be fine, though perhaps red is best. This will be great for me to see if
I
accidentally mis-type a number, otherwise, my real overtime will not be
correctly reflected and I won't get paid what I should! <vbg Not a
desirable thing!

So, in a nutshell, the rounding off is perfect and the formulas all seem
to
be working fine.
When I then get the target value of "0", the font colour is hidden by the
background colour. If I didn't work a full day, I get a negative value in
this cell which shows up as blue which allows me to see how much will go
against overtime I work (not a day goes by when I don't have to work
overtime <g!). However, if I accidentally mis-type a figure in the
"before
overtime" cells, they won't show up either just like "0" values as the
font
for these is the same colour as the "0's" so I need to have the positive
values in this cell, i.e. the +0 values, to show up in red (preferably).

Can this be done?


My formulas are these:

1) Cell G14 which has the formula that worries about the "0" values looks
like this -

=IF(F14<"",ROUND(F14-SUM(D14-C14)-TIME(7,30,0),5),"")

2) the format of the cells is this so that negative values show up as
cyan -

h"h"mm"m";[Cyan]-h"h"mm"m"

Thanks so much!


"Doug Glancy" wrote in message
...
Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed
the
last digit of "2" to "5" and the values then show up correctly (in my
very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you
would
substitute into the larger formula:




=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value
after
all
so
good thing I mentioned it. I'm guessing that once I do, that
secondary
formula will work (?). Okay. Unfortunately, the doubles
correction
formula
you provide below isn't working either <damn. I added a "=" in
front
of
the "IF(Round..." because without it, the entire formula is all
that
is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula,
and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I
work
on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero.
You
sometimes get these types of errors in Excel (and other programs).
It
has
to do with the way doubles are calculated or something like that.
Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to
have
been
needed. Unfortunately, the additions didn't work as all "0"
values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I
didn't
type
in
the values. The F4 below should be F3, as can logically be seen
in
hindsight <g. Still, that didn't affect the end result. Even
once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I
didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:






=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference
to
the
formula in keeping column G empty unless value is not 0
(whether
negative
or
not <g). Do you think I can just leave it as is? The
workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work,
however.
About
the
only thing I figured out to do is to fix the word wrap. I
read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 --
Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is
mathematically
impossible
to have a negative zero and I presume that Excel is governed
by
this
mathematical principal as well.

With regards to your second problem try this in cell G3







=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in
message
...
I have a great spreadsheet that's really been a
life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell
G3,
I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results
are
funny
in
one
way: to show "negative" hours in results, I changed to
the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours
worked
show
as
a
negative which is then taken into account in the
"overtime".
The
actual
overtime worked, then, shows "real" overtime worked and
not
just
a
result
based on the fact that I worked till such-and-such an
hour.

However, the odd thing is that sometimes that column
displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out
why
it
sometimes
puts that negative sing in. Ultimately, the mathematical
end
result
of
"0"
is the same, of course, but that makes this column
awkward.
It
was
this
in
part that prompted me to wish this formula displayed
results
differently.

Since this is a visual check calculation only in this cell
so
that
I
can
see
if a workday has been worked in its entirety, it actually
would
be
better
if
the above code only displayed for any other value other
than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

THAT'S PERFECT!! <lol Sorry for the caps but I was so excited. I have
used conditional formatting a lot but in only one way, in changing row
colours. That's why it never occurred to me to use it in this scenario as
it's something new. This is absolutely brilliant, thanks. Since it accepts
3 conditions as you say, it accepted the exact 3 I need. When greater than
0, the colour of the font is orange (turns out red doesn't show up well on
the dark grey) and I set the background to dark grey. The background needs
to be set in the conditional formatting, too, I found out. The 2nd
condition is when value is less than 0, the font is cyan and the background
dark grey. That last is when value is exactly 0, both font and background
are dark grey. Totally awesome!! And now all formulas work as there are no
cells empty of value yet I don't see the ones I'm not concerned with as the
font/background same colour.

Well, as to the "inventiveness", it's more of accidentally stumbling upon
something <g - when one is a newbie there is one thing that's in one's
favour, we're not bound by the usual approaches and things occur to us
because we don't know any better <lol!!!

Can't believe how elegant this spreadsheet as it's now completely easy to
use. Without my having to calculate _anything_ at all, it takes care of my
overtime. I have only to enter 5 times each day, that's it: my day start
time, day end time, lunch hour start and lunch hour end and when I finish
for the day. It calculates overtime on a daily basis and tallies up as I go
along and then a final cell in the sheet rounds to nearest quarter hour for
me! The reason this is necessary is because each day is always different
for me in this job and it was a problem each day to deal with it. When
you're the bottom of the totem pole, you never know when you'll go for lunch
or when you leave for the day and I always have overtime. Also if I have,
say, a dentist appointment etc. it's taken into account and I don't have to
calculate anything myself ever again. I just tack on the appointment's time
to my lunch hour time and all is calculated by the spreadsheet itself.

Thanks so much for everyone's kind help. This is a winner now and it seems
that I can go full steam ahead and use it. :oD

"Andibevan" wrote in message
...
I would recommend that you look at conditional formatting
(FormatConditional Formatting). This allows you to choose 3 conditions
and
associate different formatting for each condition. Have a quick read of
the
help file as well.

I have always used conditional formatting - it never occured to me to use
your method - very inventive.

"StargateFanFromWork" wrote in message
...
Well, I've worked with the spreadsheet for a few days now. It's going
well.
The rounding out now seems to make all formulas work properly.

I just learned how to use cell formatting for negative values and
changing
colour, so when a solution to this issue came to me after this thread, I
felt I'd have saved a lot of grief if I'd know of it and used this
solution
before! :oD See, when I made the changes given in this great cell for
results ending in "0", they completely knocked out the final formula as
this
crucial cell was now "empty" of value, if I can call it that. But an
extremely simple solution occurred to me that just needs to be tweaked
in
a
way I don't know how to do. I changed the cell formatting so that the
font
colour is the same as the background while I also changed the negative
values to "cyan", which shows up great against the dark grey. So when
there's a negative value, I see a blue figure whereas the "0" doesn't
stand
out at all. Yet the final formula operates because the "0" value is
still
actually there (brilliant, I thought <g!). This part is perfect.
However,
is there a way to further enhance the colour feature to show numbers
greater
than zero as yet _another_ colour? One of the standard Excel colours
would
be fine, though perhaps red is best. This will be great for me to see
if
I
accidentally mis-type a number, otherwise, my real overtime will not be
correctly reflected and I won't get paid what I should! <vbg Not a
desirable thing!

So, in a nutshell, the rounding off is perfect and the formulas all seem
to
be working fine.
When I then get the target value of "0", the font colour is hidden by
the
background colour. If I didn't work a full day, I get a negative value
in
this cell which shows up as blue which allows me to see how much will go
against overtime I work (not a day goes by when I don't have to work
overtime <g!). However, if I accidentally mis-type a figure in the
"before
overtime" cells, they won't show up either just like "0" values as the
font
for these is the same colour as the "0's" so I need to have the positive
values in this cell, i.e. the +0 values, to show up in red (preferably).

Can this be done?


My formulas are these:

1) Cell G14 which has the formula that worries about the "0" values
looks
like this -

=IF(F14<"",ROUND(F14-SUM(D14-C14)-TIME(7,30,0),5),"")

2) the format of the cells is this so that negative values show up as
cyan -

h"h"mm"m";[Cyan]-h"h"mm"m"

Thanks so much!


"Doug Glancy" wrote in message
...
Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again
changed
the
last digit of "2" to "5" and the values then show up correctly (in
my
very
last post I mention that the values were off by a few minutes until
I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you
would
substitute into the larger formula:





=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value
after
all
so
good thing I mentioned it. I'm guessing that once I do, that
secondary
formula will work (?). Okay. Unfortunately, the doubles
correction
formula
you provide below isn't working either <damn. I added a "=" in
front
of
the "IF(Round..." because without it, the entire formula is all
that
is
displayed in the cell rather than any numerical result. But
after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the
formula,
and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I
work
on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread,
the
values
are probably .0000001 or something so they don't evaluate to
zero.
You
sometimes get these types of errors in Excel (and other
programs).
It
has
to do with the way doubles are calculated or something like
that.
Try
googling "double precision error excel". To fix your formula,
you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to
have
been
needed. Unfortunately, the additions didn't work as all "0"
values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I
didn't
type
in
the values. The F4 below should be F3, as can logically be
seen
in
hindsight <g. Still, that didn't affect the end result.
Even
once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I
didn't
reproduce
your spreadsheet so I'm not sure, but this might be what
you're
looking
for:







=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in
message
...
I actually mentioned the -0 in case it might make a
difference
to
the
formula in keeping column G empty unless value is not 0
(whether
negative
or
not <g). Do you think I can just leave it as is? The
workbook
seems
to
work just fine despite this the negative and positive
values.

Re the second, thanks for the formula! It didn't work,
however.
About
the
only thing I figured out to do is to fix the word wrap. I
read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in
message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 --
Try
copying
the
cell and doing paste special values and then increase
the
decimal
places.

I think this is likely to be the reason as it is
mathematically
impossible
to have a negative zero and I presume that Excel is
governed
by
this
mathematical principal as well.

With regards to your second problem try this in cell G3








=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in
message
...
I have a great spreadsheet that's really been a
life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in
cell
G3,
I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results
are
funny
in
one
way: to show "negative" hours in results, I changed to
the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours
worked
show
as
a
negative which is then taken into account in the
"overtime".
The
actual
overtime worked, then, shows "real" overtime worked and
not
just
a
result
based on the fact that I worked till such-and-such an
hour.

However, the odd thing is that sometimes that column
displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out
why
it
sometimes
puts that negative sing in. Ultimately, the
mathematical
end
result
of
"0"
is the same, of course, but that makes this column
awkward.
It
was
this
in
part that prompted me to wish this formula displayed
results
differently.

Since this is a visual check calculation only in this
cell
so
that
I
can
see
if a workday has been worked in its entirety, it
actually
would
be
better
if
the above code only displayed for any other value other
than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD

"Andibevan" wrote in message
...
I would recommend that you look at conditional formatting
(FormatConditional Formatting). This allows you to choose 3 conditions
and
associate different formatting for each condition. Have a quick read of
the
help file as well.

I have always used conditional formatting - it never occured to me to use
your method - very inventive.

Actually, this technique I stumbled upon thanks to your help has been
working extremely well in a couple of other applications since then.

One other advantage has occurred to me. Using cell conditions also means
that the user can't easily or inadvertently corrupt anything. So, then, we
have the fact that this way the user is less likely to corrupt the
spreadsheet as well as the original huge advantage of having complete result
values available for other formulas in other cells. This was something not
always true when we tried programmatically changing how the cell results
were displayed rather than just with colour control of font and background.
End results are the same anyway, just one is a heck of a lot easier to do
than the other! <g

To recap, cell condition was used for when cell values are equal to 0, as
one case. Here, the cells were formatted with font colour and cell
background a certain way, usu. same colour so that "0" values don't show up
to the user. Then the 2 other conditions also dealt with with different
font and cell backgrounds for cell values greater than 0 and cell values
less than 0.

Thanks. :oD