Forum Discussion
INDEX & MATCH interrupted by Inconsistent Dates
Hi,
The formula you use isn't correct, and some dates in column N are treated as text not as numeric dates.
Please check out the fix in the attached file.
Regards
Thank you for the Formula Fix!
Follow-up: In this example i have like 30 rows of data, but in my actual spreadsheet i have around 6000... how can i fix ALL the dates to be treated as "Date" or Text" in one swoop without selecting all changing their format and than manually retyping date for 6000 rows? Haytham Amairah
What steps did you EXACTLY take to fix the dates in the sample i sent you?
I've been told the solution to my dates problem before and no matter what I do even following yours and Allan's lead right now i'm unable to fix the dates problem aside from individually formatting the cells and than retyping in the correct date which is not feasible for 6000 rows... you have the 30 rows from the sample, what did you to in those 30 rows as i see their Syntax is still the same Haytham AmairahCheck out this link to see how I fix the dates:
https://send.firefox.com/download/98dda8067df5553f/#pdhegKMozYWC8ddr4XK6sg
If that doesn't help, the reason may be that there are inconsistent dates in your complete data.
If you can provide us with a sufficient sample to see what the problem is.
Here is a more complete worksheet so you can see what i'm dealing with 🙂 Haytham Amairah
shade206I have written an article here which describes different techniques - http://bit.ly/2Hvig1h
For your data, I selected the problematic dates (MM-DD-YYYY ones). Clicked Data > Text to Columns. Bypassed the first two steps as insignificant. On Step 3 I changed the Date option to M-D-Y.
Thank you sir, that does most definitely work in the sample, but sadly those same steps will not correct the issue in my actual worksheet.... its odd because i deliberately copied the dates from my actual worksheet into the sample... Alan_Murray
