logical formula for date

[davyfulla]

Hi, new to the forum and looking for some help please. Sure it's a really simple solution as it's just something simple I'm trying to do. In column N I have a list of dates. In column Z I want to display either "Yes" where the date in column N has already passed or "No" if the date in column N has not passed. The formula I'm using in column Z is =IF(N2<TODAY(),"Y","N") however it just gives me "No" on every row regardless of whether date is passed or not.

[etaf]

that should work
is the date in column N actually a Date ? or text

can you attach a sample sheet

[martindwilson]

most likely your dates are text that look like dates

[davyfulla]

checked the formatting on column N and that column is definitely formatted as dates. is there any way i can upload the spreadsheet on to this thread?

[martindwilson]

to Attach a sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[davyfulla]

there we go. cheers

[etaf]

if you do N2+1 what result do you get
also try
=Cell("format",N2)
do you get a result starting with D

you can upload a spreadsheet here
go advanced
click on the paperclip symbol
and upload the spreadsheet

[davyfulla]

if you do N2+1 what result do you get
also try
=Cell("format",N2)
do you get a result starting with D

you can upload a spreadsheet here
go advanced
click on the paperclip symbol
and upload the spreadsheet

I've just changed the format of the cells to number to see if that made a difference. doing the avbove comes back with an answer of F2????

[etaf]

click on column N
then goto
DATA
Text to columns
Delimited
next, next

now it will work, as the date is now seen as a date and not text

[martindwilson]

they are text
select column N
then data tab/text to columns/delimited then click finish that should sort them out

[davyfulla]

brilliant. that has worked for column z. however, the dates in column N are now displayed as decimal numbers 41236.00 etc. how can i fix them? I'd never heard of this delimited thing. i thought you formatted just by right click then change to date in the drop down list???

edit: sorted the column N issue by right click and change to date in drop down. thanks all for the quick fix. what is this delimited thing though? never heard of it

[sktneer]

Another way to do this when you have formatted a column as Date but Dates are not behaving like Dates but text. While the column is formatted as Date, select the first cell in that column -->Ctrl+Shift-->Down Arrow Key to select whole range --> press Ctrl+Enter and that will fix the issue.
I did the same with your attached sample workbook in col. N and the issue was resolved immediately.