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
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 Amairah
Check 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
Hi,
The dates have been fixed with the same steps I showed you.I guess it didn't work on your side because your Windows date system is in DD/MM/YYYY (UK format) while the dates are created in MM/DD/YYYY (US format).(Please note that the default dates format in Excel always follow the default dates format in the operating system)The cause of the problem is that some of them have hidden characters that make Excel read them as texts.So when you open the workbook, the numeric dates will show up as DD/MM/YYYY whereas the text dates will stay in MM/DD/YYYY which make a mix dates.In my Windows, I have the date system in US format, therefore the solution works in my side.Find the solution in the attached file.Hope that helps- Thank you SO MUCH! its now working as needed!