Matching Dates

Topic

New Reply

Col A in the attached spreadsheet contains a list of birth dates given to me, Col F contains a list of dates extracted from our database. I need to match each value in Col A with a value in Col F but, as you can see from my attempts to use Match (Col C) are not working.

I think it involves the formatting of the values but I cannot work out how, I have tried pasting formats from Col A to Col F and vice versa, and using Text(A1, “mmm dd yyyy”) but no success.

Can Anyone help?

TIA

Reply | Quote

Replies

The values in F are text representations of dates. The formatting will not matter as Excel will only see them as text until they are altered to true dates.

You can use this formula to convert the yext to date:
=–(SUBSTITUTE(TRIM(F2),” “,”, “,2))
Copy down as far as needed
Then format the cells to the date format of your choice.

Adjust your match() function to use the column with the formulas.

Reply | Quote

This also works for me

Create a New Date Column in G from G2

=DATEVALUE(VALUE(MID(F2,5,2)) & “-” & LEFT(F2,3) & “-” & RIGHT(F2,4))

Copy dowm and then change Formula in B

=MATCH(A2,$G$2:$G$58,0)

Reply | Quote

Manually:

– Do a search and replace on your offending dates column, replace two spaces with one
– Then replace space with – or /
– Finally, select the offending column, Data, Text to columns, fixed width, Push Next (twice) and set up the format for the field as being MDY. Finish.

Reply | Quote