Is that a real date with the cell format set to displays it as YYMMDD or is that just a number which you mentally interpret to be a date in that format? If it is a real date, just reformat the cell like Robby posted to do. However, that is really just a number, not a real date, then select the column, call up the Text To Columns dialog box (Data tab, Data Tools panel), select the Delimited option and then click the Next button twice, select the Date option button and select YMD from its drop down, then click the Finish button.I need help in converting dates from this format:
YYMMDD
180422
Excel Workbook | ||||
---|---|---|---|---|
J | K | |||
2 | 180422 | 04/22/2018 | ||
Sheet1 |
If all of the year values will be after 1999, then this formula could also be used (the cell would still need to be formatted as you indicated)...Custom format K2 as "mm/dd/yyyy"
Excel Workbook
J K 2 180422 04/22/2018 Sheet1
If the dates could be in either the 1900s or 2000s, then assuming 30 as the breakpoint for assuming dates prefaced by 19 instead of 20, this formula should work...If all of the year values will be after 1999, then this formula could also be used (the cell would still need to be formatted as you indicated)...
=0+TEXT(A1,"2\000-00-00")
Thanks Rick. That's better than what I posted which has the same constraint, but fails if the YY part of A1 is 00 (year 2000) .If all of the year values will be after 1999, then this formula could also be used (the cell would still need to be formatted as you indicated)...
=0+TEXT(A1,"2\000-00-00")
I do not know for sure (because I do not have to deal with international issues), but would your formula be locale dependent given you are asking Excel to decide how to make a text date into a real date?Thanks Rick. That's better than what I posted which has the same constraint, but fails if the YY part of A1 is 00 (year 2000) .
Is that a real date with the cell format set to displays it as YYMMDD or is that just a number which you mentally interpret to be a date in that format? If it is a real date, just reformat the cell like Robby posted to do. However, that is really just a number, not a real date, then select the column, call up the Text To Columns dialog box (Data tab, Data Tools panel), select the Delimited option and then click the Next button twice, select the Date option button and select YMD from its drop down, then click the Finish button.