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
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.
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
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?
Hi, I have a column of 365 rows showing the dates of the year in the format yearmonthday.
Eg 20180101, 20180102, 20180103, ... etc.
I have tried figuring this out both in Excel and in Google Sheets.
I simply want to add two hyphens so that it looks like year-month-day. For an hour+ I have found two methods which both are failing.
Several websites suggest writing something like
=DATE(LEFT(C61,4);mid(C61,5,2);right(C61,2)) .
In Excel this causes an error message "There is a problem with this formula..." etc. In Google Sheets it says formula parse error.right-click on cell -> format cells -> selecting the Type I want.
In Excel the result is just a very long row of "#################". The locale is set to English (Sweden). In Google Sheets I select cell -> format -> number -> date. 20180102 becomes " 57151-03-29 ".
I can't help to think this software is inadequate in providing simple solutions to simple problems like this. It is more easy to learn a programming language like Python, because there is clear and available documentation on most basic tasks.