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.
1. Converting Text String to Excel Date Format Correctly
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.
2. Customizing Date Formatting Correctly in Excel
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 Short Date Number Format in Excel
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.
4. Using Excel 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.
Read More: How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
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.
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.
7. Using Excel SUBSTITUTE Function for Date Formatting Correctly
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.
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.
Things to Remember
Excel stores the date values as numbers. By default, this counting starts from 1st January of 1900. Basically, Excel considers 1st January of 1900 equal to 1, 2nd January of 1900 equal to 2, 3rd January of 1900 equal to 3, and this counting goes on. Unfortunately, it couldn’t identify the dates prior to 1900 year. Excel assumes these dates as text values. So, if you want to keep or format dates before 1900 then you must input them manually.
You can see the following image. I inserted some dates prior to 1900 and Excel considered these dates as text values. When I fixed the format, still Excel couldn’t modify these dates.
Download Practice Workbook
You can download the Excel Workbook that we’ve used to prepare this article.
Conclusion
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
Another point, you cannot use the date format function for dates before 1900. For example, 10/01/1889 will not convert to the date format (dd-mmm-yyyy) 01-Oct-1889. You must enter the date in the preferred format manually. I cannot think why Excel would be limited in this way, but for those of us documenting genealogy with large populations with dates prior to 1900 this is a head scratching limitation.
Thank you so much for pointing out this issue, Debbie! I wholeheartedly express my gratitude for the valuable time you dedicated to sharing your expertise and aiding others in addressing this matter.
I have added the limitations of Excel (regarding date format) in the article too. Thanks again!
Regards,
Musiha|Exceldemy