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
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Mar 29, 2013 at 01:23 PM
Related:
- Formatting dates
- How to clear formatting in excel - Guide
- Phone formatting software for pc - Download - File management
- Conditional formatting if two cells match ✓ - Excel Forum
- Excel conditional formatting if another cell contains specific text - Excel Forum
- Gamescom 2023 dates - Guide
4 responses
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
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.
`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.
purplequiller
Posts
2
Registration date
Thursday March 28, 2013
Status
Member
Last seen
March 28, 2013
Mar 28, 2013 at 08:23 PM
Mar 28, 2013 at 08:23 PM
Thank you so much! I will give it a try and will follow-up
purplequiller
Posts
2
Registration date
Thursday March 28, 2013
Status
Member
Last seen
March 28, 2013
Mar 28, 2013 at 08:54 PM
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?
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?
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
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.
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.
