Excel problem

[Cookie]

Hi!

I have 2 columns in a spreadsheet

One is a list of dates (it's a list of warranty expiries for hardware). The other is empty. In the empty one, I want to do a lookup on the first column and compare the date mentioned to todays date, and put either Yes or No, dependant on whether the date is in the past or future

Is it possible?

 

[SC03OTT]

In column A have your dates. In column B type "=TODAY()" (remove the quotes) . In column C, type =IF(A2<B2,"YES","NO"). IS that what you're after?

Edit: I'm not as clever as Mace

 

[mace¬]

=IF(A1<TODAY(),"Yes","No")

Where A1 is your date

 

[Cookie]

=IF(A1<TODAY(),"Yes","No")

Where A1 is your date

That's just marking everything with no

Maybe my fields are fcuked..

Looks like every one of my fields in the first column has a space for the first character.. best way of removing it without doing it manually for 1800 rows?

 

[Sym0n]

I'd do it through VBA personally (as I already have the script written), but you could go pikie and use =TRIM(A1) in another column and copy it down to the last row entry. Then select the row with your new data in, copy and then paste special values back in to column A.

 

[mace¬]

Have you formatted the date column to make them DATE?

 

[Gardus]

What's the format of the 1st columns cells?

 

[Cookie]

Have you formatted the date column to make them DATE?

Yeah, just done that

 

[mace¬]

Ummmm don't know thats my knowledge used up. I will have a play....

so some are " 12/1/2011" and some "12/1/2011"? (if that makes sense?)

 

[Sym0n]

T
R
I
M

 

[Cookie]

Sorted it now

TRIM + Notepad copy n pasting

Cheers all