Using "IF" and todays date

**Slongy** · 07-20-2010, 08:23 AM

Hi.

I can't quite make this work.

Basically I want to be able to return a result based on whether the date in a cell is in the past or not.

So, Cell I5 will have a date in it. I need Cell J5 to return a result of "Closed" if the date is prior to today, or just return the date in Cell I5 if it is either today or in the future.

I tried something like this: =IF(I5<TODAY(),"Closed") but I can't get it right

Any suggestions?

Cheers

Andy

**NBVC** · 07-20-2010, 08:26 AM

Is it not giving the "Closed" or not giving the item in I5?

Try:

=IF(I5<TODAY(),"Closed",I5)

**Slongy** · 07-20-2010, 10:26 AM

Originally Posted by NBVC

Is it not giving the "Closed" or not giving the item in I5?

Try:

=IF(I5<TODAY(),"Closed",I5)

Hi. Thanks very much for your quick reply.

I tried this on several rows where the date was in the past, it's just returning the date in I5....

It doesn't seem to be validating it against the <Today()

**NBVC** · 07-20-2010, 10:34 AM

Is I5 really a date or is it a text sting?

Does this work?

=IF(I5+0<TODAY(),"Closed",I5+0)

**Slongy** · 07-20-2010, 11:18 AM

Originally Posted by NBVC

Is I5 really a date or is it a text sting?

Does this work?

=IF(I5+0<TODAY(),"Closed",I5+0)

You beauty, that works prefectly

Thanks very much for your help

Andy

**NBVC** · 07-20-2010, 11:29 AM

That means the date in I5 was entered in a cell that was preformatted as Text or it was entered with an preceding apostrophe.

To fix, you can select the column of dates (if they are in column I) and go to data|Text to columns and just click Finish. Then while still selected go to Format|Cells and select the proper date format from the Number tab.