Help Converting Dates

coptrdr

New Member
Joined
Apr 22, 2018
Messages
3
I need help in converting dates from this format:

YYMMDD

180422

My desired result would be (with slashes):

MM/DD/YYYY

04/22/2018

Any takers?

Thanks!

Eric
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Robby19

Board Regular
Joined
Mar 19, 2018
Messages
227
Select the column(s) or cell(s) you want to convert, right-click, "Format Cells", choose the Date category and select the appropriate type. In this case it should look like 03/14/12 (MM/DD/YYYY).
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,030
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I need help in converting dates from this format:

YYMMDD

180422
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,442
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Custom format K2 as "mm/dd/yyyy"
Excel Workbook
JK
218042204/22/2018
Sheet1
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,030
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Custom format K2 as "mm/dd/yyyy"
Excel Workbook
JK
218042204/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)...

=0+TEXT(A1,"2\000-00-00")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,030
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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")
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...

=0+TEXT(A1,(20-(LEFT(A1)>"2"))&"00-00-00")
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,442
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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) :mad:.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,030
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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) :mad:.
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?
 

coptrdr

New Member
Joined
Apr 22, 2018
Messages
3
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.

Yes, that is a real date.

Thanks!
 

coptrdr

New Member
Joined
Apr 22, 2018
Messages
3
Yes, I am asking Excel to make a date from text. When I try to use the format cell, and I chose date (which in this case is 180422) my result comes out to:
12/22/2393. This is not my desired result.


<colgroup><col width="71"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,748
Members
418,149
Latest member
amamiche67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top