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

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
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

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

    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 

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

        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 

    • Alan_Murray's avatar
      Alan_Murray
      MVP
      Dec 20, 2019

      shade206The dates in this example were stored as text because they were in a MM-DD-YYYY and not a DD-MM-YYYY format.

      The easiest way to convert them is to select them and use Text to Column. The final step provides a option to convert dates quickly.

Resources