Formatting dates

Closed
Gracie01 Posts 1 Registration date Thursday March 28, 2013 Status Member Last seen March 28, 2013 - Mar 28, 2013 at 05:06 PM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Mar 29, 2013 at 01:23 PM
Help!

I need to do a vlook-up between two workbooks with the date being the common between the two. The problem is that the date is formatted in a different way on each sheet. I can not figure out how to change 03/07/2012 to this 20120531

Hope someone can help please

4 replies

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 28, 2013 at 05:31 PM
Hi Gracie01,

`03/07/2012' and `20120531' represent two different values. If the dates are same in the two sheets formatted differently it is possible to VLOOKUP() the values. For example `Sheet1' has `03/07/2012' in the cell A1 and `Sheet2' has `20120703' in the cell A1 then you can use the below mentioned formula to lookup the corresponding value in Cell B1 of `Sheet1':

=VLOOKUP(DATEVALUE(RIGHT(A1,2)&"/"&RIGHT(LEFT(A1,6),2)&"/"&LEFT(A1,4)),Sheet1!$A$1:$B$1,2,0)

You can modify the formula according to your need.

To change '20120703' to '03/07/2012' use this formula:

=RIGHT(A1,2)&"/"&RIGHT(LEFT(A1,6),2)&"/"&LEFT(A1,4)

Please revert for clarification.
0
purplequiller Posts 2 Registration date Thursday March 28, 2013 Status Member Last seen March 28, 2013
Mar 28, 2013 at 08:23 PM
Thank you so much! I will give it a try and will follow-up
0
purplequiller Posts 2 Registration date Thursday March 28, 2013 Status Member Last seen March 28, 2013
Mar 28, 2013 at 08:54 PM
No luck. I tried reformatting the 20120313 date but the sheet I had the date data on will not react to any formulas.

The date data that I am trying to format was originally a cvs file that I opened in excel....could that be reason why the formula does not work?
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 29, 2013 at 01:23 PM
Hi purplequiller,

I have applied these formulas and prepared a sample sheet. I have uploaded the same to the below mentioned link:

http://speedy.sh/aSW9G/DateVlookup.xlsx

Please download the file and check if this helps.

Do reply with results.
0