Fix Excel Date Not Formatting Correctly (8 Quick Solutions)

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.

fix excel date no formatting correctly overview

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.

fix excel date not formatting correctly by converting into date format

📌 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.

fix excel date not formatting correctly by converting into date format

Like in the screenshot below, you’ll get the results with the proper date format at once.

fix excel date not formatting correctly by converting into date format

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.

fix excel date not formatting correctly by customizing number format

📌 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.

fix excel date not formatting correctly by customizing number format

Here are our resultant values with the customized date format in the following picture.

fix excel date not formatting correctly by customizing number format

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.

fix excel date not formatting correctly by extracting dates from text

📌 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.

fix excel date not formatting correctly by extracting dates from text

📌 Step 2:

➤ Select the Delimited radio button as data type & press Next.

fix excel date not formatting correctly by extracting dates from text

📌 Step 3:

➤ Now mark on Space as the delimiters since our text data contains spaces among them.

fix excel date not formatting correctly by extracting dates from text

📌 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.

fix excel date not formatting correctly by extracting dates from text

📌 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.

fix excel date not formatting correctly by extracting dates from text

📌 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.

fix excel date not formatting correctly by extracting dates from text

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.

fix excel date not formatting correctly by extracting dates from text

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.

using value function to fix excel date not formatting correctly

📌 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.

using value function to fix excel date not formatting correctly

Then the dates will be displayed in a proper format in Column C.

using value function to fix excel date not formatting correctly

Related Content: How to Convert Date to Month in Excel (6 Easy Methods)


Similar Readings:


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)

using datevalue function to fix excel date not formatting correctly

📌 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.

using datevalue function to fix excel date not formatting correctly

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.

find and replace to fix excel date not formatting correctly

📌 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.

find and replace to fix excel date not formatting correctly

📌 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.

find and replace to fix excel date not formatting correctly

📌 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.

find and replace to fix excel date not formatting correctly

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.

using substitute function to fix excel date not formatting correctly

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.

using error checking option to fix excel date not formatting correctly

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.

using error checking option to fix excel date not formatting correctly

After the conversion of year formats in all cells, you’ll find no error message anymore for the date values.

using error checking option to fix excel date not formatting correctly

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.


Related Articles

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

2 Comments
  1. 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

    • Reply Avatar photo
      Lutfor Rahman Shimanto Feb 2, 2023 at 5:27 PM

      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

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo