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 wrote in message
...
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.