need to compare dates and count results

[Heather]

I have two columns of dates. The first column is the date the event
actually occurred or is 0 if nothing has been done yet. The second column
is the target date by which the event should happen, the deadline.

I was able to create conditional formatting for the first column. It turns
each date green if the task was complete or if the due date was more than 24
hours from today. The date turns yellow if it is incomplete and the due
date is 24 hours from today. The date turns red if it's incomplete, and
it's target date is today or has passed.

I would now like to be able to add up how many dates are green, how many are
yellow, and how many are red. Can I count cells based on their format color?
I couldn't figure out a way. So I tried creating a countable column, but I
haven't been able to figure out how to combine the three test conditions
into a single counting function or a single function to create a new row of
data I could count like L1.

Here are my 3 formulas, and what I would like the 3rd column to look like if
I can't count the format color changes in J1 directly.
Green =OR(K2 > Today()+1, AND(K2<>(TODAY()+1),OR(J2 = K2,J2<>0)))
Yellow =AND(K2=TODAY()+1,J2 = 0)
Red =AND(K2<=TODAY(),J2 = 0)


J K

Site inspection Site inspection (target)
1/12/2007 1/15/2007
3/08/2007 3/08/2007
0 3/10/2007
0 2/28/2007

L
Compare result
G
G
Y
R
xposted to microsoft.public.excel, but this board seemed more active

 

[Pete_UK]

Heather,

have a look at this link:

http://www.cpearson.com/excel/colors.htm

Chip Pearson shows how you can return the colour of a cell with a user-
defined function, and also explains how to count coloured cells - just
what you are after.

Hope this helps.

Pete

 

[Heather]

Thank you, I'm off to give this a try.
-Heather

Heather,

have a look at this link:

http://www.cpearson.com/excel/colors.htm

Chip Pearson shows how you can return the colour of a cell with a user-
defined function, and also explains how to count coloured cells - just
what you are after.

Hope this helps.

Pete

 

[Heather]

Heather,

have a look at this link:

http://www.cpearson.com/excel/colors.htm

Chip Pearson shows how you can return the colour of a cell with a user-
defined function, and also explains how to count coloured cells - just
what you are after.

Hope this helps.

Pete

Pete,

The bad news is that none of the Pearson solutions work with the "formula
is" style of conditional formatting that I used above, and I can't come up
with any other method to compare the two dates without creating three new
columns, each with one of the above conditions. Since I have 23 pairs of
date columns, and over 1200 rows, adding subtotals and grouping to the
report causes the whole thing to lock up. The number of rows is expected to
exceed 5000 within 8-12 months so I don't expect this to get better.

I'm thinking I need to move the whole thing into MS Access, just because of
the quantity of data.

Any other suggestions?

Thanks,
Heather

 

[Pete_UK]

Hi Heather,

not sure if you are still monitoring this thread - I've been away for
a couple of days.

You could combine your three conditions into one composite formula
like this:

=IF(OR(K2>TODAY()+1,AND(K2<>(TODAY()
+1),OR(J2=K2,J2<>0))),"G",IF(AND(K2=TODAY()
+1,J2=0),"Y",IF(AND(K2<=TODAY(),J2=0),"R",0)))

This will give you "G", "Y" or "R" if the appropriate conditions are
met, or 0 if they are not - you could change the final zero in the
formula to "" if you wanted the cell to appear blank in this
situation. The formula can be copied down to cover your 1200 rows.

Then you could count the number of Greens, Yellows or Reds with three
simple COUNTIF formulae, as follows, assuming you have put the formula
in column L:

=COUNTIF(L2:L1200,"G")
=COUNTIF(L2:L1200,"Y")
=COUNTIF(L2:L1200,"R")

You might like to make the range L2:L5000 if you expect that many rows
eventually.

Hope this helps.

Pete