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

Kayla

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.

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.

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

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.

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

Kimberly Jarvis

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

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

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

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

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?

Kimberly Jarvis

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