How can I check whether a column of dates are all before today's d

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PaladinWhite - 20 Oct 2007 04:46 GMT

I'm working in Excel 2007.

I've got a column of dates, and I need to check the column as a whole to
ensure that every date is prior to today's date.

E.g. if today's date is 10/19/2007, and my column is the following:
8/17/1940
4/15/2007
10/18/2007
... the data is okay; 0 of the dates violate the rule. However, if the
column is the following:
8/17/1940
4/15/2007
10/20/2007
... then it's not okay, because a date (the third) violates the rule - it
falls after today's date.

I thought I could do it using COUNTIF(A:A,">TODAY()"), where A is the column
of dates, but that function always returns a 0 value, no matter whether the
dates are okay or not. If I replace TODAY() with a date, it works - for some
reason, it just won't recognize and evaluate the TODAY() function.

What am I doing wrong? I appreciate the help!

Reply to this Message

T. Valko - 20 Oct 2007 05:02 GMT

Try this:

=COUNTIF(A:A,"<"&TODAY())=COUNT(A:A)

The result will be either TRUE or FALSE.

Signature

Biff
Microsoft Excel MVP

> I'm working in Excel 2007.
>
[quoted text clipped - 22 lines]
>
> What am I doing wrong? I appreciate the help!

Reply to this Message

PaladinWhite - 21 Oct 2007 06:24 GMT

Works great! (Now I've just gotta figure out WHY!)

Yay, an opportunity to learn something new.

Thanks again!

> Try this:
>
[quoted text clipped - 28 lines]
> >
> > What am I doing wrong? I appreciate the help!

Reply to this Message

T. Valko - 21 Oct 2007 17:52 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Works great! (Now I've just gotta figure out WHY!)
>
[quoted text clipped - 36 lines]
>> >
>> > What am I doing wrong? I appreciate the help!

Reply to this Message