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.
Answer from Forward Ed on Stack OverflowHow do I format dates to show MM/DD/YYYY?
java dateformat - Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 ) - Stack Overflow
How can I write a formula to change a date format of 20230214 to 02/14/2023
Keeping a Date Format in a Formula
Videos
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.
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.
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