Matching 2 "dates" that are formated differently
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. |
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 In article , "carl" wrote: 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. |
try
=IF(VLOOKUP(VALUE(TEXT(B6,"yyyymm")),A:A,1),1,2) or =IF(MATCH(VALUE(TEXT(B7,"yyyymm")),A:A,-1),1,2) -- Don Guillett SalesAid Software "carl" wrote in message ... 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. |
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)) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "carl" wrote in message ... 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. |
All times are GMT +1. The time now is 10:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com