formula to find and flag dates older than 30 days

What I need is function/formula that will look at a column of dates (format
is 99/99/99), compare each cell's value to a cell containing a report date
(or todays date), and then mark the cell thats 30 days older or more out of
range in a color, preferrably salmon.
Then I need another formula that will total up the number of cells in that
column that are out of the date range (and will be a salmon color) at the
bottom of the column

I can also add a column just to the right of the data to use as a marking
"out of 30 day range" indicator and use the counta function on that column.

I was thinking about something like the following:

if celldate <= (todaysdate - 31), then adjcolumncell = x, else
adjcolumncell = blank
but that will only put an x in the appropriate adjacent column (if I
could get the proper syntax worked out) - I would still have to do the counta
on the adj column. And im not sure if I can compare dates like numbers - I
dont know how to do a conversion on a cell containing a date to the numeric
value of the date.

You can use conditional formatting with a formula of

=(b2-a2)>30

where B2 and a2 are the dates, and you can choose the colour

To get a total, use a formula of

=SUMPRODUCT(--((B2:B100-A2:A100)>30))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Will G." <Will [email protected]> wrote in message
news:[email protected]...
> What I need is function/formula that will look at a column of dates
(format
> is 99/99/99), compare each cell's value to a cell containing a report date
> (or todays date), and then mark the cell thats 30 days older or more out
of
> range in a color, preferrably salmon.
> Then I need another formula that will total up the number of cells in that
> column that are out of the date range (and will be a salmon color) at the
> bottom of the column
>
> I can also add a column just to the right of the data to use as a marking
> "out of 30 day range" indicator and use the counta function on that
column.
>
> I was thinking about something like the following:
>
> if celldate <= (todaysdate - 31), then adjcolumncell = x, else
> adjcolumncell = blank
> but that will only put an x in the appropriate adjacent column (if I
> could get the proper syntax worked out) - I would still have to do the
counta
> on the adj column. And im not sure if I can compare dates like numbers - I
> dont know how to do a conversion on a cell containing a date to the
numeric
> value of the date.