Forum Discussion
If Condition incorrectly evaluating flat file Date Column
Hello,
I have the following file that has the Date field coming from a Red Hat Linux environment and comes into excel as a.csv extension. I saved the file as an excel workbook and am trying to evaluate a IF statement where the Date will be ignored if the date in the cell is >Nov 2025 or <April 2025. Even though I have changed the format in Column A to be a Date and custom format of "mmm yy" it is still not evaluating properly. I have gone ahead and created a test worksheet in this workbook and typed in sample dates and this time the IF statement evaluates correctly.
How do I then either a) mass convert the native Date Column to a Date format or b) create a helper Column B pointing to Column A: (text(A2, "MMM YY"), copying and pasting as values and then changing it to Date format with custom date convention of mmm yy. Here is the link to the file:
https://docs.google.com/spreadsheets/d/1zYTGXGtvwCDWhhZp6yoDfnyH4zSDTYAk/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true
Is there a workaround to the Date column so that excel evaluates is as a true date (serial) data? Thank you.
Regards,
Shams.
so it appears to be working in that google sheet.
that said, if the cell is interpreted as text then changing the format of the cell to be DATE (custom format or not) will NOT convert the TEXT to be a date it will only show a value that excel sees as a date in that format.
To convert you can use "Text to Columns" under the Data tab.
To use a helper column/formula you want DATEVALUE() not TEXT(). you might even be able to have excel convert it by putting it in an equation. In your case you can try:
=IF(OR(DATEVALUE(A2)>$F$2,DATEVALUE(A2)<$F$3),"Ignore","Okay")
or you can even try
=IF(OR(--A2>$F$2,--A2<$F$3),"Ignore","Okay")
where that "--" before the A2 forces a calculation and then excel usually does the conversion automatically
1 Reply
so it appears to be working in that google sheet.
that said, if the cell is interpreted as text then changing the format of the cell to be DATE (custom format or not) will NOT convert the TEXT to be a date it will only show a value that excel sees as a date in that format.
To convert you can use "Text to Columns" under the Data tab.
To use a helper column/formula you want DATEVALUE() not TEXT(). you might even be able to have excel convert it by putting it in an equation. In your case you can try:
=IF(OR(DATEVALUE(A2)>$F$2,DATEVALUE(A2)<$F$3),"Ignore","Okay")
or you can even try
=IF(OR(--A2>$F$2,--A2<$F$3),"Ignore","Okay")
where that "--" before the A2 forces a calculation and then excel usually does the conversion automatically