If Statement and Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Toys - 19 Dec 2005 12:34 GMT

I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
12/31/05. I want to use a conditional if statement to evaluate if the date
is less than a given day, for example: =IF(A1<12/15/05,"Less","Greater").
Therefore the first 14 should result in "Less" and the last 17 should result
in "Greater". My reslut is returning "Greater" for all of them. Any ideas
why? Two additional things: 1st, if I put the date in column B and then do
=IF(A1<B1,"Less","Greater") then it works fine, its only a problem when I
enter it manually. 2nd, this would be fine, but I'm trying to use a Macro
with an Input box where I enter the date (a variable) which slots into my If
statement. Any suggestions?

Thanks.

Reply to this Message

Ron Rosenfeld - 19 Dec 2005 12:47 GMT

>I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
>12/31/05. I want to use a conditional if statement to evaluate if the date
[quoted text clipped - 8 lines]
>
>Thanks.

The problem is that, when parsing your IF statement, Excel does not know if you
mean a date, or if you mean 12 divided by 15 divided by 5. Since you have no
quote marks around your date representation, Excel assumes you mean the
arithmetic process and not a date.

Try:

A1<"12/15/05"

Most unambiguous would be

A1<DATE(2005,12,15)

--ron

Reply to this Message

Toys - 19 Dec 2005 12:57 GMT

Thanks for the quick response. I tried with quotes, but I think it
recognizes it as text because the opposite problem occurs, it evaluates it as
all true ("Less"). I'll try your other suggestion and let you know.

Toys

> >I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
> >12/31/05. I want to use a conditional if statement to evaluate if the date
[quoted text clipped - 23 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 19 Dec 2005 23:39 GMT

>Thanks for the quick response. I tried with quotes, but I think it
>recognizes it as text because the opposite problem occurs, it evaluates it as
>all true ("Less"). I'll try your other suggestion and let you know.
>
>Toys

The DATE(yr,month,day) is certainly the most unambiguous; but you could try:

DATEVALUE("12/01/2005")

>> >I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
>> >12/31/05. I want to use a conditional if statement to evaluate if the date
[quoted text clipped - 23 lines]
>>
>> --ron

--ron

Reply to this Message

Toys - 21 Dec 2005 08:15 GMT

Date(yyyy,mm,dd) worked perfectly. I had no problems using it with my macro
either. Thanks

> >Thanks for the quick response. I tried with quotes, but I think it
> >recognizes it as text because the opposite problem occurs, it evaluates it as
[quoted text clipped - 35 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 21 Dec 2005 12:30 GMT

>Date(yyyy,mm,dd) worked perfectly. I had no problems using it with my macro
>either. Thanks

You're very welcome. Thanks for the feedback.
--ron

Reply to this Message