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 interpre
as a date, as such:

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

does this work for you?

Bruc

 

[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

 

[Niek Otten]

That should have been

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

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Ron Rosenfeld]

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