Matching 2 "dates" that are formated differently

[carl]

I have data from 2 sources. I would like to match up the data that is tagged
with the same month and year data. For example:

data set 1

Date Product
200503 ABC

data set 2

ColA ColB ColC
Date Product
3/18/2005 ABC TRUE
5/15/2006 ABC FALSE

I am trying to find a formula to place in Col C of data set 2 that will
return true if the month and year are the same. Is it possible ?

Thank you in advance.

 

[JE McGimpsey]

One way:

If both values are XL dates, but the data in Set1 is formatted as
yyyymm, then you can compare them directly.

If the date in Set1 is instead a value or text 200503, then you can
compare the text values.

Say Set1 is text "200503" and is in Sheet1!A:B, and Set2 is in
Sheet2!A:C. Then

Sheet2!C2: =TEXT(A2,"yyyymm") = Sheet1!A2


If instead Set1 has numeric values, 200503, then use two unary minus
operators to coerce the text into numbers

Sheet2!C2: =--TEXT(A1,"yyyymm") = Sheet!A2

 

[Don Guillett]

try
=IF(VLOOKUP(VALUE(TEXT(B6,"yyyymm")),A:A,1),1,2)
or
=IF(MATCH(VALUE(TEXT(B7,"yyyymm")),A:A,-1),1,2)

 

[David McRitchie]

Hi Carl,
It sometimes gets you a quicker answer if you show how far you got.
Assuming that your regional short date format is ordered as mm/dd/yy

=1<=COUNTIF('carl_1'!A:A,YEAR(A2)*100+MONTH(A2))