You can do this with Text to Columns.
Select the single column of numbers, then in the 3rd step choose as follows, then press Finish.
which leaves you with proper Excel dates:
I'm in the UK with default DMY so the above is showing as such, your dates will show as in your locale, maybe like this:
You can do this with Text to Columns.
Select the single column of numbers, then in the 3rd step choose as follows, then press Finish.
which leaves you with proper Excel dates:
I'm in the UK with default DMY so the above is showing as such, your dates will show as in your locale, maybe like this:
Dear respected Mrsgarcia2,
Good day! Thank you for posting to Microsoft Community. We are happy to help you.
As per your description, the following simple formula may help you to convert the yyyymmdd to normal date (mm/dd/yyyy) format. Drag and fill this formula down to each cell.
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
If above is not your scenario, then could you please share some dummy data sheet view and expected result for our reference, so we can understand your exact scenario and provide our suggestion in a productive manner, perhaps some related screenshots will be helpful.
Appreciate your patience and understanding and thank you for your time and cooperation.
Sincerely,
Waqas Muhammad
java dateformat - Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 ) - Stack Overflow
Returning a date format in a formula as date format (currently showing as text)
How do you convert date format to another?
Formula to format "MM/DD/YYYY" and "M/D/YYYY" strings as date in Excel - Stack Overflow
Videos
OPTION 1)
Assuming that you source date that is in the number format dd.mm.yyyy stored as an excel date serial and only formatted to display as dd.mm.yyyy then the best fix is to select the cells you want to modify. Go to your home tab, and select the number format and change it to General. See Green circles in image below. IF the format is already set to general, or when you switch it to general your numbers do not change, then it is most likely that your date in dd.mm.yyyy format is actually text. and will needed to be converted as per OPTION 2 below. However, if the number does change when you set it to general, select the arrow in the bottom right corner of the number area (see red circle).
After clicking the arrow in the red circle you should see a screen similar to the one below:
Select Custom from the category list on the left, and then in the Type bar enter the format you want which is yyyy/mm/dd.
OPTION 2
=date(Right(A1,4),mid(A1,4,2),left(A1,2))
This assumes your original date is a string stored in A1, and converts the string to a date serial in the form excel stores dates in.1 You can copy this formula down beside you dates. You can then apply cell formatting for the date as described above, or use the build short or long date if that style matches your needs.
1Excel counts the number of days since January 0 1900 for the windows version of excel. I believe mac is 1904 or 1905.
The problem is that your date is a text string, so changing the cell number format won't have any effect.
One method of convert the dates in place to "real" dates is to use the Text to Columns wizard.
Select your date(s).
Select the Text to Columns option on the Data Tools Tab of the Data Ribbon:
- At Step 1, select "Fixed Width" then "Next"
Step 2 is irrelevant
At Step 3, for the date column, select DMY as the format. You can also select the "Destination" if you want the results written elsewhere.
- Then "Finish".
That's it. Your dates will be converted in place
Since your dates are now "real" Excel dates, you can use the Cell ► Number Format dialog to change the format to whatever you want.
I have thousands of dates in a sheet that is formatted yyyymmdd Example: 20220827 for August 27, 2022.
I’d like to convert it from yyyymmdd to m/d/yyyy (or mm/dd/yyyy) with the slashes. How do I re-format this for all cells?
Formula in B2 is
=DATEVALUE(TEXTJOIN("-",TRUE,CHOOSECOLS(TEXTSPLIT(A1,"/"),3,1,2)))
Working from the inside out:
TEXTSPLIT to split the string into month, day, year
CHOOSECOLS to reorder the strings into year, month, day
TEXTJOIN to join those strings with a - that will be recognized by the DATEVALUE formula
DATEVALUE to turn the strings into a date, and it won't matter whether they are single or double digits for month and date.
=DATE(
INDEX(FILTERXML("<t><s>" & SUBSTITUTE(A1, "/", "</s><s>") & "</s></t>", "//s"), 3),
INDEX(FILTERXML("<t><s>" & SUBSTITUTE(A1, "/", "</s><s>") & "</s></t>", "//s"), 1),
INDEX(FILTERXML("<t><s>" & SUBSTITUTE(A1, "/", "</s><s>") & "</s></t>", "//s"), 2)
)
Will convert text strings in an MDY type format with a / separator into a proper date. You can then format it as you wish.
If you might have other delimiters, nest SUBSTITUTE functions to take cared of that.
For example, if you might have either a / or a ., you would modify the formula to:
=DATE(
INDEX(
FILTERXML(
"<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, ".", "/"), "/", "</s><s>") & "</s></t>",
"//s"
),
3
),
INDEX(
FILTERXML(
"<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, ".", "/"), "/", "</s><s>") & "</s></t>",
"//s"
),
1
),
INDEX(
FILTERXML(
"<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, ".", "/"), "/", "</s><s>") & "</s></t>",
"//s"
),
2
)
)
Algorithm:
- Create an XML from the date, replacing the delimiter with the node identifier
- Since we know there will only be three segments, and they are in the order MDY, we can feed those results into the
DATEfunction to create our "real date" - You can then format the date however you wish
In the Formula Bar, a date will show according to your computer's setting for the "Short date".
In Control Panel > Clock, Language and Region > Region and Language you can set these defaults for your computer.
The Excel "short date" format will always be displayed in the formula bar. In the screenshot below, you can see my regional settings. The Excel cell has today's date, formatted as a "Long Date". The formula bar shows the "Short date", which is determined by the computer's regional settings.
So, no, you cannot change the way the date shows in the formula bar, unless you are prepared to change your computer settings for "short date". This will then apply to all applications that use that date format, which may be way more than just Excel.
There is no way to change the date format in the formula bar. Excel actually stores dates as serial number, so your example of September 26, 2013 is actually stored in Excel as 41543. You can use the format cell settings to change the in-cell output to whatever format you need, but there is no explicit "formula bar" format. It is just there as a way to show you the data contained in the cell.
The only way to change the view in the formula bar would be to convert the date to text using the TEXT() function, but then you would no longer be able to interact with the date as a number.
A full explanation of the way dates and times work in Excel is available on Excel Tactics: The Definitive Guide to Using Dates and Times in Excel
When I try to format cells to show the MM/DD/YYYY format I right click on the cell selection, click Format Cells and select Date but the the list only shows D/D/YYYY. There's nothing there to let me display the 0 in front of months and dates that only have one digit.
I've been trying to figure this out for a while and every Google search tells me to do what I've done and select MM/DD/YYYY but like I said the only format I can choose is the M/D/YYYY format.