Forum Discussion

shade206's avatar
shade206
Brass Contributor
Dec 20, 2019
Solved

INDEX & MATCH interrupted by Inconsistent Dates

Hello So I have TWO Issues. Issue #1: My formula in columns F(Outbound), G(Inbound), and H(Total) is only working properly for "Jennifer" AND only up until there is a interruption of chronological...
BI & Data Analysis
excel
Formulas and Functions
office 365
training
  • Haytham Amairah's avatar
    Haytham Amairah
    Dec 20, 2019

    shade206

     

    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

Alan_Murray's avatar
Alan_Murray
MVP
Dec 21, 2019

shade206Yes, you have some inconsistent dates. Some UK format and others in US. I am using a UK system format. So mine recognises the D-M-Y an stores anything else as text.

 

In the attached file, I used a formula in the last column to check if the date was seen as numeric or text. Then I filtered the list t only show the non-numerics.

 

Then I selected those, used Go To Special to be sure that visible cells only was selected. And did the Text to Columns approach mention on previous replies.

 

You have some dates that do not appear in the source producing #N/A errors. You can use the IFNA or IFERROR formula to replace these with something more useful.

 

I hope this helps.

 

Alan

  • Alan_Murray's avatar
    Alan_Murray
    MVP
    Dec 23, 2019
    Excellent!
  • shade206's avatar
    shade206
    Brass Contributor
    Dec 23, 2019

    Thank you SO MUCH! its now working as needed!Alan_Murray