IF function

[Tflight]

I am linking one cell in worksheet B with a cell in worksheet A. I want the
cell in worksheet B to display an X if the cell in worksheet A has Oct-09 in
it.

In worksheet A, I have formatted the cell so that it says Oct-09 when I type
in 10/2009. However, when I click on that cell in the function line at the
top it displays 10/1/2009 instead of 10/2009. (I can't figure out how to
change it so that it says 10/2009. Any suggestions?)

In worksheet B, the function I have to get an X in that cell is:
=IF('Worksheet A'!M6="Oct-09","X")
The cell displays False instead of X. Also, when I change the "Oct-09" to
10/1/2009 it still says False.

Please help.

 

[TomPl]

When you enter "10/2009" in the cell, excel converts that to a number and
displays it in date format.

One option would be to format the cell M6 to text format. The change you
formula from "Oct-09" to "10/2009" and you will be comparing text to text and
it should work.

Tom

 

[Stu]

Hi
Excel converts dates to numbers so that it can manipulate them 'behind the
scenes'. To do this it needs a complete date - day month and year. Where
you are entering 10/2009 in sheet A, it takes this as an incomplete date and
assumes that you mean the 1st of the month.

To get your 'X' to display, the easy thing is to format the cell M6 on sheet
A to be TEXT and enter Oct-09. Your formula on Sheet B is looking for a
text string Oct-09 and will then find it.

If you really want to enter your date as 10/2009, you need to change the
formula on sheet B to be { =IF(WorksheetA!M6=DATEVALUE("Oct-09"),"X") }

This will evaluate true for the value 10/1/2009 on sheet A and your 'X' will
appear as if by magic.

HTH

Stu

 

[Tflight]

Thanks for the advice. When I changed it to TEXT it worked and when I used
the DATEVALUE function it worked. However, when I used the DATEVALUE function
it didn't work when I typed Oct-09, but it did work when I typed 10/1/2009.

 

[Tflight]

Thanks for your help!