PDA

View Full Version : comparing dates in excel



Buff_K
17-06-2011, 02:59 PM
Dear god I need some help on this, this is doing my head in....

I have extensively googled this and tried various approaches to no avail.

I have a column of dates in general format, I want to compare them with todays date say, if its greater than returns 1, less than returns 0.

the latest attempt:
=IF(G2>AB2,"y","n")
just returns 'n' no matter what I make AB2

If I try and change the cells to Date format it then just returns a "Y'.

I dunno what I'm doing wrong.

The date column is imported from another system thats why its in general format as 02.03.2011.

would really appreciate any help.
Thanks!

SolMiester
17-06-2011, 03:46 PM
Try putting the format into a proper excel formula =date(yyyy.mm,dd)

ManUFan
17-06-2011, 04:34 PM
Try a find & replace (CTRL + H) on the 02.02.2011 etc (replace . with / to give a 'proper' date format) becomes 02/02/2011.

This worked for me. Would not work with the date format you have (02.02.2011).

Formula I used was: =if(A1>today(),"yes","no") where A1 contains the date to compare to today.

MushHead
17-06-2011, 04:53 PM
If you have no control over the imported text & you have to re-import it (so doing the Ctrl+H thing every time is impractical), you can dynamically convert to date format before doing the comparison using the formula:

DATEVALUE(SUBSTITUTE(A1,".","/"))

So in your example, you'd end up with

=IF(DATEVALUE(SUBSTITUTE(G2,".","/"))>AB2,"y","n")

(assuming G2 was your imported date)