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