Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dates for x-axis values as string instead of creating a scale | Charts and Charting in Excel | |||
How do I get Excell to sort dates that range from 1800 to 1900's | New Users to Excel | |||
How sort dates just by day/month and NOT year if all 3 given in ce | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel |