IF on dates
 

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

=IF(A36/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

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

=IF(A3DATE(2005,6,25,"Past","Future")

does this work for you?

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=382934

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(A3A4,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" wrote in message
...
I am usually a whix with IF statements, but this one has me stumped.
I have a user who wants this:

=IF(A36/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

That should have been

=IF(A3A4,"PAST","FUTURE")

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"Niek Otten" wrote in message
...
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(A3A4,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" wrote in message
...
I am usually a whix with IF statements, but this one has me stumped.
I have a user who wants this:

=IF(A36/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

On Tue, 28 Jun 2005 16:04:47 -0400, "MLD" wrote:

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

=IF(A36/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(A3DATEVALUE("6/28/2005"),"PAST","FUTURE")

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


--ron