Nehad Ulfat
In data entry, it’s a common phenomenon that the dates get manipulated with text or other formats. In this article, you’ll learn how you can change, customize or fix the dates that were not formatted correctly in the Excel spreadsheet.
The above screenshot is an overview of the article which represents an example of fixing date format in Excel. You’ll learn more about the dataset as well as the methods & functions to change, customize or fix date formats in the following sections of this article.
Download Practice Workbook
You can download the Excel Workbook that we’ve used to prepare this article.
8 Simple Solutions to Fix Date Not Formatting Correctly in Excel
1. Converting Text String to Date Format
When we have to copy dates from a range of cells to another area, sometimes the format changes and as a result, we see only number values with some digits starting with 4. In the picture below, this is an example of the occurrence mentioned where Column D represents the number values only, not in date format. But we have a very easy solution for this happening.
📌 Steps:
➤ Select the range of cells containing the number values that have to be turned into date format.
➤ Under the Home tab and from the Number group of commands, click on the drop-down. You’ll see two types of date format there- Short Date and Long Date.
➤ Select any one of these two formats and you’re done.
Like in the screenshot below, you’ll get the results with the proper date format at once.
Read More: How to Convert Text to Date with Excel VBA (5 Ways)
2. Customizing Date Format
Assuming that, now you want to customize the date format according to your preference. So you have to customize the date format, right? Let’s follow the steps now.
📌 Step 1:
➤ Under the Home ribbon, open the Cell Format dialogue box from the Number group of commands.
📌 Step 2:
➤ Select Custom under the Number tab.
➤ For example, we want to see the date format as- ‘Wednesday, 12.08.2020’, so under the Type option, you have to write:
dddd, dd.mm.yyy
You’ll be shown a preview under the Sample bar.
➤ Press OK and you’re done.
Here are our resultant values with the customized date format in the following picture.
Read More: How to Use Formula to Change Date Format in Excel (5 Methods)
3. Extracting Dates from Texts by Using Column Wizard
Sometimes we have to copy texts including dates from a source and then we need to extract dates only from those text strings in the Excel sheet. There’s no option to simply customize or change the date format by going through the Format Cells commands. In the picture below, this is an instance of the problem where the dates are lying with times and texts.
📌 Step 1:
➤ Select the entire range of cells containing the texts with dates.
➤ From the Data tab, choose Text to Columns option from the Data Tools drop-down, a dialogue box will appear.
📌 Step 2:
➤ Select the Delimited radio button as data type & press Next.
📌 Step 3:
➤ Now mark on Space as the delimiters since our text data contains spaces among them.
📌 Step 4:
➤ In the picture below, you’re now seeing a column containing dates only with a black background. Select Date as Column Data Format.
➤ If you notice, our dates in the texts are in MM/DD/YYYY format so select MDY format from the Date drop-down.
📌 Step 5:
➤ Click on the 2nd column now in the Data Preview section.
➤ You’ll be now shown 2nd column containing times that we want to remove. So select ‘Do not import column(skip)’ radio button as Column Data Format.
📌 Step 6:
➤ Click on the 3rd column now.
➤ Like the previous step, select ‘Do not import column(skip)’ as Column Data Format for the 3rd column too.
➤ Press Finish.
Now you’ll have a range of cells with the extracted date values only. You can change the Date format or customize the format now as your preferences.
Related Content: How to Change Default Date Format from US to UK in Excel (3 Ways)
4. Using VALUE Function to Fix Date Format
We have another option to convert a text string to the date format by using the VALUE function. In Column B, we have now dates with text format though they look like in the exact date format. In Column C, we’ll apply the VALUE function that converts a text string to a number format if the numbers are found in the specified cell.
📌 Step 1:
➤ Select Cell C5 & type:
=VALUE(B5)➤ Press Enter & the function will return with some digits.
➤ Use the Fill Handle now to autofill the entire Column C.
So, the text format has just turned into number format.
📌 Step 2:
➤ Now as we have to format the numbers that will represent dates, select the whole range of cells containing those number values.
➤ Under the Home tab and from the Number group of commands, select Short or Long Date format.
Then the dates will be displayed in a proper format in Column C.
Related Content: How to Convert Date to Month in Excel (6 Easy Methods)
Similar Readings:
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Get First Day of Month from Month Name in Excel (3 Ways)
- How to Get Last Day of Previous Month in Excel (3 Methods)
- Â Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)
5. Inserting DATEVALUE Function to Fix Date Format in Excel
DATEVALUE function searches for the cells containing dates as well as time data and returns with the dates only. If other texts except date or time lie in the cell, then the DATEVALUE function cannot recognize the date or time data in the cell and will return as #VALUE! error. The syntax of this DATEVALUE function is:
=DATEVALUE(date_text)
📌 Steps:
➤ Select Cell C5 & type:
=DATEVALUE(B5)➤ Press Enter, use Fill Handle to autofill other cells in Column C and you’re done.
The text values will turn into number format and then you have to change the format of those numbers into date format.
The basic difference between uses of VALUE and DATEVALUE functions is DATEVALUE function extracts only dates from a combination of date and number from a cell. But the VALUE function searches for numbers only from a text string no matter it represents a date or time value.
Read More: How to Convert Date to Year in Excel (3 Quick Ways)
6. Applying Find & Replace Command to Convert Text into Date Format
If the dates are in such a format that includes Dots(.) instead of Obliques(/) as the separators, then the VALUE or DATEVALUE function will not be able to recognize date value from a text string. In that case, we have to use Find and Replace command to replace Dot(.) with Oblique(/) and then the VALUE or DATEVALUE function will convert them into Date formats.
📌 Step 1:
➤ Select the text data containing dates.
➤ Press CTRL+H to open the Find and Replace dialogue box.
➤ Input Dot(.) as Find What and Forward Slash(/) as Replace With options.
➤ Press Replace All.
📌 Step 2:
➤ So now all date values in Column B are now representing slashes as separators. But these date values are still in text format that we have to convert into number format.
📌 Step 3:
➤ Now in Cell C5, use the DATEVALUE function to convert text format into number format that will represent dates.
=DATEVALUE(B5)➤ Press Enter, use Fill Handle to fill down the rest of the cells and you’ll find the dates in accurate format.
Related Content: VBA to Remove Time from Date in Excel (3 Methods)
7. Using SUBSTITUTE Function to Fix Date Format
By using SUBSTITUTE function, we can replace or substitute dots with slashes as well as convert text format to date format more efficiently. The generic formula of the SUBSTITUTE function is:
=SUBSTITUTE(text, old_text, new_text, [instance_num]
📌 Steps:
➤ In Cell C5, the related formula with SUBSTITUTE function will be:
=SUBSTITUTE(B5,".","/")➤ Press Enter, autofill the entire Column C with Fill Handle, and you’ll get the resultant values with proper date format at once.
Read More: How to Change Date Format in Pivot Table in Excel
8. Using Error Checking Option to Fix Date Format
Sometimes, the cells containing dates can show errors that you can find by clicking on the yellow icon with the Exclamatory sign. You may find the message showing that the text date is with 2-digit year and there’ll be options below that message to convert 2-digit year into the 4-digit year.
Let’s select the option that will convert the 2-digit year to the 4-digit year with the 20XX year format. If the years in your data represent 1900-1999 then you have to go for 19XX.
After the conversion of year formats in all cells, you’ll find no error message anymore for the date values.
Read More: How to Convert Date to Day of Year in Excel (4 Methods)
Concluding Words
I hope all of the methods mentioned above to fix the date formats will now allow you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
It misses one useful way to fix dates – when dates display as number in the cell and a date in the entry bar, and all the applying date formats do nothing.
Ctrl + `
Where a user has accidentally turned on “Show formulas”
To reproduce:
Ctrl + `
Enter a date
Set format on cell to “dd-mmm”, then “dd-mmm-yyyy” and any other combination
Then Ctrl + ` and the date is correctly formatted
Thank you so much for sharing this tip, Andy! I sincerely appreciate the time you took to share your expertise and assist others with this problem. Your suggestion proved effective, and We are genuinely grateful for it. Thank you again!
Regards
Lutfor Rahman Shimanto