Index/Match with a Date -- Date isn't a true date

I'm brining in a date with an index match formula. The date is coming in and displaying as a date, but I'm not able to utilize this field as a date. For example, if I want to filter that column to "in the past" - it's not picking up on it because it's just coming into the cell like it's text.

column is a date column.

Tags:

Answers

  • Shubham
    Shubham ✭✭✭✭

    Hi Kayla 

    I hope you're doing well. 

    As per your requirements, I tried. It is working properly. Can you share screenshot for better understanding.

    Thanks  

    Shubham Umale, Smartsheet Engineer, Ignatiuz Software 

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kayla

    I agree with @Shubham!

    As long as you're entering the formula into a Date type of column, it should be read correctly.

    Can you copy/paste exactly the formula you're using, and also post some screen captures? (But block out sensitive data).

    Cheers,

    Genevieve

  • Kayla
    Kayla ✭✭✭✭✭

    I figured it out! I was indexing from a date column, to a date column. But my source date column was a formula itself that was pulling a date and converting it to text. Whew -- had me stumped until I drilled all the way down. I was assuming there was an issue somewhere along the way. This is what happens when I try and work later than I should on a Friday and stare at formulas all day.

    Thanks for your help everyone!!

  • Genevieve P.
    Genevieve P. Employee Admin

    Wow, well-done finding the root of the issue! I'm glad you got it sorted out. 🙂

  • Hi Kayla,

    How did you resolve the issue. I am doing a metrics sheet formula pulling in via references... My tracker sheet due date is pulling in from a source that is a formula even though it displays as a date in the tracker sheet, the metrics is seeing it as text.

    How did you get it to not see it as text (even though column is a date column?

    Here is my formula on metrics sheet:

    =COUNTIFS({Status}, “Active”, {Tracker1DueDate}, >=DATE(2023, 1, 31), {Tracker1DueDate}, <=DATE(2023, 3, 31))

    The result is 0 but it should be pulling a count of 35

    Thanks!

  • Kayla
    Kayla ✭✭✭✭✭

    Hi @Kimberly Jarvis. If I recall, I had to update the formula in my date column. I think I had a + "" at the end of it, which converted it to text.

    Is your 'Tracker1DueDate' referencing an actual date or a date in text? It might be your formula in the source 'Tracker1DueDate' field that needs to be looked at and not this formula on your metrics sheet.

    I can always take a look at your sheets if needed.

  • Kimberly Jarvis
    edited 03/14/23

    Ah. Yes, I have "" in the source formula.

    =IFERROR(INDEX({CompleteDate}, MATCH{ID}@row, {ROWID}, 0)), " ")

  • Kayla
    Kayla ✭✭✭✭✭

    That wouldn't cause the issue because this would just result in a blank IF there is an error. Is your 'CompleteDate' reference to a date column and is that date field a formula? If so, what's that formula?

  • I figured it out... created a date conversion text to date and it works. Thanks :-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!