Formatting dates
Closed
Gracie01
Posts
1
Registration date
Thursday 28 March 2013
Status
Member
Last seen
28 March 2013
-
28 Mar 2013 à 17:06
Zohaib R Posts 2368 Registration date Sunday 23 September 2012 Status Member Last seen 13 December 2018 - 29 Mar 2013 à 13:23
Zohaib R Posts 2368 Registration date Sunday 23 September 2012 Status Member Last seen 13 December 2018 - 29 Mar 2013 à 13:23
Related:
- Formatting dates
- Excel clear formatting - Guide
- Iphone 14 release dates - Home - IOS
- Vivatech 2024 dates - Guide
- Blackhat dates - Guide
- Conditional formatting excel dates - Guide
4 responses
Zohaib R
Posts
2368
Registration date
Sunday 23 September 2012
Status
Member
Last seen
13 December 2018
69
28 Mar 2013 à 17:31
28 Mar 2013 à 17:31
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 28 March 2013
Status
Member
Last seen
28 March 2013
28 Mar 2013 à 20:23
28 Mar 2013 à 20:23
Thank you so much! I will give it a try and will follow-up
purplequiller
Posts
2
Registration date
Thursday 28 March 2013
Status
Member
Last seen
28 March 2013
28 Mar 2013 à 20:54
28 Mar 2013 à 20:54
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 23 September 2012
Status
Member
Last seen
13 December 2018
69
29 Mar 2013 à 13:23
29 Mar 2013 à 13:23
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.
