Forum Home
Press F1
Thread ID: 118693	2011-06-17 02:59:00	comparing dates in excel	Buff_K (691)	Press F1

Post ID	Timestamp	Content	User
1209911	2011-06-17 02:59:00	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!	Buff_K (691)
1209912	2011-06-17 03:46:00	Try putting the format into a proper excel formula =date(yyyy.mm,dd)	SolMiester (139)
1209913	2011-06-17 04:34:00	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.	ManUFan (7602)
1209914	2011-06-17 04:53:00	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)	MushHead (10626)
1