using VLOOKUP with Dates and Text

Umesh Shah

I have two sheets, one which keeps track of dates and reminder dates which is maintained by team and looks like this:

and another sheet which drives a form where people pick dates (a copy of the dates in Long Date above).

The "target Appointment Date" is a drop-down value field i.e. TEXT.

In the field "reminder 1" i would like to do a lookup to the dates field above. I would like to lookup the drop-down value in "Targeted Appointment Date" and bring back the "Reminder 1" date (which is a date field on both sheets).

I know the problem is that the field i am trying to match is a text field, while the table has all date fields. How can i fix this without having the change the table to text.

Paul Newcome

@L_123 I don't even remember what I worked on yesterday let alone a year ago. Hahaha. I'll take a shot though.

We have a date type column (stored on back end as mm/dd/yyyy) but displayed as the long date, and we need to match this to a text value that only looks like the long date. So what we need to do is figure out a way to convert one of those entries into the other data type.

I am thinking it will be easier to convert the date into a text value. We can do that either with a couple of nested IFs (ugly) or with a reference table (preferred).

I would have the reference table set up like this:

Then we can string together some INDEX functions like so:

=INDEX({Day}, WEEKDAY([Long Date]@row)) + ", " + INDEX({Month}, MONTH([Long Date]@row)) + " " + DAY([Long Date]@row) + ", " + YEAR([Long Date]@row)

This should give you a text string that mimics the Long Date, and since it is a text value, it should be able to match when referenced by the dropdown column value.

L_123

The simple way is to use a helper column

=[long date]@row + ""

which will convert it into text, then use your lookup. You might be able to use a collect with some modifiers though.

Umesh Shah

When I try that formula, it gives me:

Where my text matches "Wednesday, December 14, 2022" and not 12/14/22.

L_123

You're correct, I think this might be better with an unformatted helper column for the lookup.

@Paul Newcome I seem to remember you working with some dates of this format a year or so ago, have you done this? I forgot the text parsing only retains the original format

Paul Newcome

@L_123 I don't even remember what I worked on yesterday let alone a year ago. Hahaha. I'll take a shot though.

We have a date type column (stored on back end as mm/dd/yyyy) but displayed as the long date, and we need to match this to a text value that only looks like the long date. So what we need to do is figure out a way to convert one of those entries into the other data type.

I am thinking it will be easier to convert the date into a text value. We can do that either with a couple of nested IFs (ugly) or with a reference table (preferred).

I would have the reference table set up like this:

Then we can string together some INDEX functions like so:

=INDEX({Day}, WEEKDAY([Long Date]@row)) + ", " + INDEX({Month}, MONTH([Long Date]@row)) + " " + DAY([Long Date]@row) + ", " + YEAR([Long Date]@row)

This should give you a text string that mimics the Long Date, and since it is a text value, it should be able to match when referenced by the dropdown column value.

L_123

@Paul Newcome Thanks for looking at this. I wonder if he could take advantage of the standard formatting, and use something like

-erased, on edit. Not correct

Umesh Shah

@Paul Newcome your previous post help solve the problem, with the adjustment that the DAY column needs to start with Sunday or else your text days will be off by one day.

Paul Newcome

@Umesh Shah You are absolutely correct. I completely skipped over the weekend days. Sorry about that.

Paul Newcome

@L_123 No worries. It was definitely an interesting one that I'll be keeping tucked away for future reference.