Errors in IF Statement when using a copied date.

**jjenk** · 08-23-2012, 06:42 PM

I have a column of dates that are a v-lookup. I copied that column to a new column using the paste special and values. I tried using that column (s) in an if statement but getting incorrect results.

I tried =IF(S8="","TTL",IF(S8<=$F$1,"NA","x")) where F1 is a date that I'm comparing to. I get x in my results even where the date in column S is less than F1.

I tried various options that I see on the forums including:
=IF(S8<"10/01/2011"+0,"NA","x")
and
=IF(S8<=DATE(2011,9,30),"NA","x")
each with the same result. However, If I manually type the date in column S it come out with the correct results.

There are too many rows to manually type this and we want to re-use the spreadsheet over and over.
Can someone help me with this?
Thanks!

**vlady** · 08-23-2012, 07:23 PM

two questions..

when you click a cell in Column S what is in the formula bar?
when you click in column F1 what is in the formula bar?

**jjenk** · 08-23-2012, 07:28 PM

I only see the date in both S and F1. for instance S8 shows 9/24/2010. F1 shows 9/30/2011.

**vlady** · 08-23-2012, 07:40 PM

i suspect that the copied dates are text - not really dates.
you can do a text-to-column in column S
text-to-column
delimeted
next
date - choose - MDY
click OK

**jjenk** · 08-23-2012, 07:43 PM

You were correct, that worked perfectly. Thanks!

**vlady** · 08-23-2012, 07:49 PM

you are very much welcome.
if you encounter other problems please do post here.

may i request you to mark this thread "SOLVED" if no further question.

go to your first post, click edit then follow the steps -look below my signature.

thanks for your time.

regards,
vladimir