Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
Hello,
I have a formula here and I don't understand why it doesn't work. The situation is such. I have a cell A1 that has a specfic date in it. If this cell is an exact date that I deem as true then I want it to place the year 2007. If not, then I want it to place the text no. My formula is such: =IF(A1="12-31-2007","2007","No") Unfortunately my formula is wrong because I continue to get a "No" answer when the cell A1 is that exact date of 12-31-2007. When I put a "<=" into the formula, it works. I don't understand. Can someone help? Thanks, Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
Dates in Excel are stored as serial numbers. Dec 31, 2007 is acutally stored
as the serial number 39447. Cell formatting may cause the serial number to display as 12-31-2007, but the actual value of the cell is still 39447. So, when your IF statement compares A1 to "12-31-2007", Excel compares the serial number 39447 to the text string "12-31-2007" which obviously aren't the same. Try one of these formulas: =IF(A1=DATE(2007,12,31),"2007","No") or =IF(TEXT(A1,"mm-dd-yyyy")="12-31-2007","2007","No") The first option compares A1 to the serial number returned by the DATE function. The second option converts A1 to a text string and then compares to the text string "12-31-2007". Either way, this allows you to compare apples to apples, instead of apples to oranges. HTH, Elkar "kanstrup" wrote: Hello, I have a formula here and I don't understand why it doesn't work. The situation is such. I have a cell A1 that has a specfic date in it. If this cell is an exact date that I deem as true then I want it to place the year 2007. If not, then I want it to place the text no. My formula is such: =IF(A1="12-31-2007","2007","No") Unfortunately my formula is wrong because I continue to get a "No" answer when the cell A1 is that exact date of 12-31-2007. When I put a "<=" into the formula, it works. I don't understand. Can someone help? Thanks, Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
Thanks so much Elkar. Make sense.
"Elkar" wrote: Dates in Excel are stored as serial numbers. Dec 31, 2007 is acutally stored as the serial number 39447. Cell formatting may cause the serial number to display as 12-31-2007, but the actual value of the cell is still 39447. So, when your IF statement compares A1 to "12-31-2007", Excel compares the serial number 39447 to the text string "12-31-2007" which obviously aren't the same. Try one of these formulas: =IF(A1=DATE(2007,12,31),"2007","No") or =IF(TEXT(A1,"mm-dd-yyyy")="12-31-2007","2007","No") The first option compares A1 to the serial number returned by the DATE function. The second option converts A1 to a text string and then compares to the text string "12-31-2007". Either way, this allows you to compare apples to apples, instead of apples to oranges. HTH, Elkar "kanstrup" wrote: Hello, I have a formula here and I don't understand why it doesn't work. The situation is such. I have a cell A1 that has a specfic date in it. If this cell is an exact date that I deem as true then I want it to place the year 2007. If not, then I want it to place the text no. My formula is such: =IF(A1="12-31-2007","2007","No") Unfortunately my formula is wrong because I continue to get a "No" answer when the cell A1 is that exact date of 12-31-2007. When I put a "<=" into the formula, it works. I don't understand. Can someone help? Thanks, Chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
=IF(A1=39447,"2007","no")
"kanstrup" wrote: Hello, I have a formula here and I don't understand why it doesn't work. The situation is such. I have a cell A1 that has a specfic date in it. If this cell is an exact date that I deem as true then I want it to place the year 2007. If not, then I want it to place the text no. My formula is such: =IF(A1="12-31-2007","2007","No") Unfortunately my formula is wrong because I continue to get a "No" answer when the cell A1 is that exact date of 12-31-2007. When I put a "<=" into the formula, it works. I don't understand. Can someone help? Thanks, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|