Help on Excel lookup for dates in different format

[peace77]

I have two different worksheets and each sheet has dates in different format. Sheet1 dates are pulled from the system and shows up as '080215 (in general format and starts with single quote because it is pulled from the system)whereas Sheet2 has date in format 7/02/2008.

Here are the contents of Sheet1 (two columns)

Date Value
'080215 0.998602
'080214 1.002959
'080213 1.00065
'080212 1.003915
'080211 0.998253
'080208 1.000851
'080207 0.988582
'080206 0.997506
'080205 0.995718

Here are the contents of Sheet 2 (one column)

Date
5/2/2008
5/2/2008
5/2/2008
7/2/2008
7/2/2008
7/2/2008
8/2/2008
8/2/2008
8/2/2008

I need to insert second coloumn in Sheet 2 names as Values and populate the numbers in this field from “Value” column in Sheet1 corresponding to the respective dates. For example in Sheet 2, for date 5/2/2008 the value should be 0.995718 (which is against the same date in Sheet 1 i.e 080205)

I just roughly tried the Vlookup function in Excel but it fails to return the value because the date formats are different in both the sheets. If I manually change the dates in both the sheets to the same date format then the vlookup works.

However I actually need a VBA code for Excel to look up for those values in Sheet 2 as I am asked to develop a macro to populate the values in Sheet 2. I am a novice in coding/VBA so can not really understand how to make this work.

Please give some insight on the vba code for excel.

Thanks,

Peace77

 

[Chance1234]

Why do you need it to be a macro ?

Chance,

F, G + 3MSTG

 

[SkipVought]

"...each sheet has dates in different format..."

WRONG!!!

Sheet 1 does NOT have dates. It has STRINGS (TEXT).

Simply convert your phony dates to REAL DATES, using the Data > Text to columns..., selecting the appropriate YMD format.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[peace77]

Hi Chance1234- I need it to be a macro because the process has to be automated and the user should not be running any of the formulas or functions to generate the final output.

SkipVought- you are correct in saying that sheet 1 has strings. But I am unable to convert the phoney dates into a valid date format via Data > Text to Coloumn as it generates wrong dates.

I am unable to figure out how should I match these date looking numbers across both the sheets.

 

[SkipVought]

"...date format via Data > Text to Coloumn as it generates wrong dates..."

Please post an EXAMPLE of a string that cannot be converted, the result of Text to columns and the desired result.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[peace77]

Date Converted date in DMY Desired results
080219 2/8/19 19/02/08
080218 2/8/2018 18/02/08
080215 2/8/2015 15/02/08
080214 2/8/2014 14/02/08
080131 1/8/1931 31/01/08
080130 1/8/1930 30/01/08

 

[SkipVought]

Works for me. I selected the YMD conversion and I got...
[tt]
2/19/1980
2/18/1980
2/15/1980
2/14/1980
1/31/1980
1/30/1980
[/tt]
which has a default FORMAT (display only).

A simple change of FORMAT (realizing that FORMAT CHANGES NOTHING!!!)
[tt]
19/02/80
18/02/80
15/02/80
14/02/80
31/01/80
30/01/80
[/tt]
VOLA!

IT all took mebe 10 seconds!


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

OOPS...

I just saw something...



Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

paste 20000000 into a cell.

COpy

select the column of phony dates.

Edit > PasteSpecial -- ADD

Now do the Text to Columns as the FULL YEAR will be in the first FOUR bytes.

OK, it took 35 seconds!


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

Actually, I lost the leading zero when I pasted your example into my worksheet.

When the leading zero is present, simply using the YMD comversion, yields...
[tt]
2/19/2008
2/18/2008
2/15/2008
2/14/2008
1/31/2008
1/30/2008
[/tt]
which is then simply formatted to...
[tt]
19/02/08
18/02/08
15/02/08
14/02/08
31/01/08
30/01/08
[/tt]
...and we're back to 10 seconds. phew!!!


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[peace77]

Hi SkipVought ,

I did Data > Text to Coloumn for Sheet 1dates in YMD format and Sheet 2 dates in DMY formatand eventually results for both the sheets are generated in the same date format. Thanks for your help!

I now need to figure how I can incorporate this in VBA code..Any ideas are highly appreciated.

Thanks!

Peace77

 

[SkipVought]

Feel free to use your mace recorder.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

.man oh man, are my phingers phumblin today.

MACRO recorder.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[peace77]

Thanks very much..I guess I am losing my sanity..