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:
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
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
How do I format dates to show MM/DD/YYYY?
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.
It is one of those excel annoyances. The original data - even if it looks like as a Date type - might not be the type of Date but Text.
A recreation of this problem:
- Create a new sheet
- select a cell
- change its data type to Text
- enter in the same cell: '3343423' (just the numbers), press Enter
- change the cell's data type back to Number => Nothing happens!
- select the cell, press F2, press Enter => presto! It's now a Number.
Now back to the original question:
Say, if A1 is the original data (and A2 is empty) first make sure A1's type is set to Data, the format is Custom and the custom format describes exactly the original data. (You can try it the other way around. Try setting the current date in a cell to your custom format until the result is satisfactory).
If A1 seems OK, then set A2 to "=A1", next click back on A1, press F2 (edit) and press Enter.
There's no need to use a formula for this, just use the Cell Format options. (As in, copy the dates as desired -either as values or as references-, and then format the destination cells as dd/m/y.)
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
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.
Try using "Text to columns" functionality to convert "in situ"
Select column of dates then use
Data > Text to columns > Next > Next > under "column data format" choose "Date" > in dropdown select MDY > OK
Now you should have valid dates which you can reformat any way you want, e.g. dd/mm/yyyy
See here
You can use Excel's DATEVALUE function to convert each of the date strings to Excel date values, then format the cells as dates.
To format the cells, select them and right-click. Choose "Format cells" on the menu that appears, select Format Cells and choose a date format.