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