Formatting dates

[Closed]
Report
Posts
1
Registration date
Thursday March 28, 2013
Status
Member
Last seen
March 28, 2013
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
43
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.
Posts
2
Registration date
Thursday March 28, 2013
Status
Member
Last seen
March 28, 2013

Thank you so much! I will give it a try and will follow-up
Posts
2
Registration date
Thursday March 28, 2013
Status
Member
Last seen
March 28, 2013

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?
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
43
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.