IF on dates

[MLD]

I am usually a whix with IF statements, but this one has me stumped.
I have a user who wants this:

=IF(A3>6/28/2005,"PAST","FUTURE")

However, it seems that no matter what date we enter, it still shows all
dates as "PAST". I doubl;e checked the formatting of the date column (where
the actual data is supposed to go) and made sure it was a basic date.

This seems so basic, why can't I fix it?

-Monica

[swatsp0p]

Try forcing the date in your formula into a format Excel will interpret as a date, as such:

=IF(A3>DATE(2005,6,25,"Past","Future")

does this work for you?

Bruce

[Niek Otten]

Hi Monica,

Dates are always very sensitive to the way they are entered, different date
systems, etc.

Easiest is to enter both dates to compare with in a cell instead of using
literals.
Your formula would the be something like

=IF(A3>A4,PAST","FUTURE")

By formatting the dates as mmm/dd/yyyy (noye the extra m) you can easily
check if the date system is what you expeceted it to be.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"MLD" <[email protected]> wrote in message
news:%[email protected]...
>I am usually a whix with IF statements, but this one has me stumped.
> I have a user who wants this:
>
> =IF(A3>6/28/2005,"PAST","FUTURE")
>
> However, it seems that no matter what date we enter, it still shows all
> dates as "PAST". I doubl;e checked the formatting of the date column
> (where
> the actual data is supposed to go) and made sure it was a basic date.
>
> This seems so basic, why can't I fix it?
>
> -Monica
>
>

[Niek Otten]

That should have been

=IF(A3>A4,"PAST","FUTURE")

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> Hi Monica,
>
> Dates are always very sensitive to the way they are entered, different
> date systems, etc.
>
> Easiest is to enter both dates to compare with in a cell instead of using
> literals.
> Your formula would the be something like
>
> =IF(A3>A4,PAST","FUTURE")
>
> By formatting the dates as mmm/dd/yyyy (noye the extra m) you can easily
> check if the date system is what you expeceted it to be.
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "MLD" <[email protected]> wrote in message
> news:%[email protected]...
>>I am usually a whix with IF statements, but this one has me stumped.
>> I have a user who wants this:
>>
>> =IF(A3>6/28/2005,"PAST","FUTURE")
>>
>> However, it seems that no matter what date we enter, it still shows all
>> dates as "PAST". I doubl;e checked the formatting of the date column
>> (where
>> the actual data is supposed to go) and made sure it was a basic date.
>>
>> This seems so basic, why can't I fix it?
>>
>> -Monica
>>
>>
>
>

[Ron Rosenfeld]

On Tue, 28 Jun 2005 16:04:47 -0400, "MLD" <[email protected]> wrote:

>I am usually a whix with IF statements, but this one has me stumped.
>I have a user who wants this:
>
>=IF(A3>6/28/2005,"PAST","FUTURE")
>
>However, it seems that no matter what date we enter, it still shows all
>dates as "PAST". I doubl;e checked the formatting of the date column (where
>the actual data is supposed to go) and made sure it was a basic date.
>
>This seems so basic, why can't I fix it?
>
>-Monica
>

6/28/2005 is not being interpreted as a date, but rather as 6 divided by 28
divided by 2005 or a very small number.

Try:

=IF(A3>DATEVALUE("6/28/2005"),"PAST","FUTURE")

Or put your 6/28/2005 in some cell and reference that cell.


--ron