Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ploting dates against a calendar and not as a simple events | Charts and Charting in Excel | |||
pasted dates changing | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) |