Date Formatted Column and Index Match to an Exact Date

I have a column that others fill in with a date, date column type is the easiest to prevent having to do a lot of clean up as rows are edited over time and dates may push to another month. I can also restrict the column to a date.

If I am trying to use the index match formula below on a date column type it returns "blank" however if I change the Estimated Close Month column type to text it will work. My next thought was to have 2 columns for Estimated Close Month, one the team would edit which would be a date column type and the second a formula to equal the team edited column as a text column type. The results ended up with an error if both columns were not the same column type.

Anyone have any suggestions on a workaround for this?

Formula used- =IFERROR(INDEX([Monthly Recurring Revenue]@row, MATCH([Estimated Close Month_Use Last Day of Month]@row, "11/30/22", 0)), "")

  • Estimated Close Month as date column type (formula does not return any results)
  • Estimated Close Month as text column type (desired results but not desired column type for the Estimated Close Month)
  • Error if using two different column types.
  • If formatted as the same column type it works


Thanks 😀!!!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!