Solved%3a Date Format Issue Excel

All your hard work of maintaining data with proper date seems to go wasted because all of a sudden Excel not recognizing date format?

Solved%3a Date Format Issue Excellence

Hi I have a date field in excel with two different formats have tried using Timestamp # Date # also ALT but am not getting it right. Note: Using QlikView 11.20 Personal Edition thus any solution please post the script will not be able to access other QVW. An example of Excel Date Format issue is as follows: A user enters or types a Date — September 6 in a cell of Excel file. However, on pressing the ‘Enter’ key the value of the cell converts to a ‘Number.’ This implies that the cell’s number format is not in sync with what the user is trying to achieve.

If your text string isn’t in a format recognised by Excel it will return the #VALUE! Excel will return the serial number for your date, you will then need to format the cell with a date format to make the serial number ‘look’ like a date as I have done in column E above. Hi, i am facing a very minor but significant problem. The problem is with the excel xlsx file format changing when i am saving the data in book2.xlsx file. As you can see in the file from date 25th till the date 31st the format is dd-mm-yyyy hh:mm:ss but it changes from date 1st to 12th to dd-mm-yy.

This problematic situation can happen anytime and with anyone so you all need to be prepared for fixing up this Excel date format messes up smartly.

If you don’t have any idea how to fix Excel not recognizing date format issues then also you need not get worried. As in our today’s blog topic, we will discuss this specific date format not changing in Excel problem and ways to fix this.

When Excel Not Recognizing Date Format?

When you copy or import data into Excel and all your date formats go wrong.
Excel recognizes the wrong dates and all the days and months get switched.
While working with the Excel file which is exported through the report. You may find that after changing the date value cell format into a date is returning the wrong data.

To recover lost Excel data, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
Preview the repaired files and click Save File to save the files at desired location.

Why Excel Not Recognizing Date Format?

Your system is recognizing dates in the format of dd/mm/yy. Whereas, in your source file or from where you are copying/importing the data, it follows the mm/dd/yy date format.

The second possibility is that while trying to sort that dates column, all the data get arranged in the incorrect order.

How To Fix Excel Not Recognizing Date Format?

Resolving date not changing in Excel issue is not that tough task to do but for that, you need to know the right solution.

Here are fixes that you need to apply for fixing up this problem of Excel not recognizing date format.

1# Text To Columns

Here are the fixes that you need to perform.

Firstly you need to highlight the cells having the dates. If you want then you select the complete column.
Now from the Excel ribbon, tap to the Data menu and choose the ‘Text to columns’ option.

In the opened dialog box choose the option of ‘Fixed width’ and then hit the Next button.

If there are vertical lines with arrows present which are called column break lines. Then immediately go through the data section and make a double click on these for removing all of these. After that hit the Next button.
Now in the ‘Column data format’ sections hit the date dropdown and choose the MDY or whatever date format you want to apply.
Hit the Finish button.

Very soon, your Excel will read out all the imported MDY dates and after then converts it to the DMY format. All your problem of Excel not recognizing date format is now been fixed in just a few seconds.

2# Dates As Text

In the shown figure, you can see there is an imported dates column in which both data and time are showing. If in case you don’t want to show the time and for this, you have set the format of the column as Short Date. But nothing happened date format not changing in Excel.

Now the question arises why Excel date format is not changing?

Mainly this happens because Excel is considering these dates as text. However, Excel won’t apply number formatting in the text.

Having doubt whether your date cell content is also counted as text then check out these signs to clear your doubt.

If it is counted as text then Dates will appear left-aligned.
You will see an apostrophe in the very beginning of the date.
When you select more than one dates the Quick Calc which present in the Status Bar will only show you the Count, not the Numerical sum or Count.

3# Convert Date Into Numbers

Once you identify that your date is a text format, it’s time to change the date into numbers to resolve the issue. That’s how Excel will store only the valid dates.

For such a task, the best solution is to use the Text to Columns feature of Excel. Here is how it is to be used.

Choose the cells first in which your dates are present.
From the Excel Ribbon, tap the Data.
Hit the Text to Columns option.

In the first step, you need to choose the Delimited option, and click the Next.

In the second step, from the delimiter section chooses Space In the below-shown preview pane you can see the dates divided into columns.
Click the Next.

In the third step set the data type of each column:

Now in the preview pane, hit the date column and choose the Date option.
From the Date drop-down, you have to select the date format in which your dates will be displayed.

Suppose, the dates are appearing in month/day/year format then choose the MDY.

After that for each remaining column, you have to choose the option “Do not import column (skip)”.
Tap the Finish option for converting the text format dates into real dates.

4# Format The Dates

After the conversion of the text dates or real dates, it’s time to format them using the Number Format commands.

Here are a few signs that help you to recognize the real dates easily:

If it is counted as text then Dates will appear right-aligned.
You will see no apostrophe at the very beginning of the date.
When you select more than one date the Quick Calc which present in the Status Bar will only show you the Count, Numerical count, and the sum.

For the formatting of the dates, from the Excel ribbon choose the Number formats.

Everything will work smoothly now, after converting text to real dates.

5# Use The VALUE Function

Here is the Syntax which you need to use:

=VALUE (text)

Where ‘text’ is referencing the cell having the date text string.

The VALUE function is mainly used for compatibility with other workbook programs. Through this, you can easily convert the text string which looks like a number into a real number.

Overall it’s helpful for fixing any type of number not only the dates.

Notes:

If the text string which you are using is not in the format that is well recognized by Excel then it will surely give you the #VALUE! Error.
For your date Excel will return the serial numbers, so you need to set the cell format as the date for making the serial number appear just like a date.

6# DATEVALUE Function

Here is the Syntax which you need to use:

=DATEVALUE(date_text)

Where ‘date_text’ is referencing the cell having the date text string.

DATEVALUE is very much similar to the VALUE function. The only difference is that it can convert the text string which appears like a date into the date serial number.

With the VALUE function, you can also convert a cell having the date and time which is formatted as text. Whereas, the DATEVALUE function, ignores the time section of the text string.

7# Find & Replace

In your dates, if the decimal is used as delimiters like this, 1.01.2014 then the DATEVALUE and VALUE are of no use

In such cases using Excel Find & Replace is the best option. Using the Find and Replace you can easily replace decimal with the forward slash. This will convert the text string into an Excel serial number.

Here are the steps to use find & replace:

Choose the dates in which you are getting the Excel not recognizing date format issue.
From your keyboard press CTRL+H This will open the find and replace dialog box on your screen.
Now in the ‘Find what’ field put a decimal, and in the ‘replace’ field put a forward slash.
Tap the ‘Replace All’ option.

Excel can now easily identify that your text is in number and thus automatically format it like a date.

HELPFUL ARTICLE: 6 Ways To Fix Excel Find And Replace Not Working Issue

8# Error Checking

Last but not least option which is left to fix Excel not recognizing date format is using the inbuilt error checking the option of Excel.

This inbuilt tool always looks for the numbers formatted as text.

You can easily identify if there is an error, as it put a small green triangle sign in the top left corner of the cell. When you select this cell you will see an exclamation mark across it.

By hitting this exclamation mark you will get some more options related to your text:

In the above-shown example, the year in the date is only showing 2 digits. Thus Excel is asking in what format I need to convert the 19XX or 20XX.

You can easily fix all the listed dates with this error checking option. For this, you need to choose all your cells having date text strings right before hitting the Exclamation mark on your first selected cell.

Steps to turn on error checking option:

Hit the office icon present in the top left corner of the opened Excel workbook. Now go to the Excel Options > Formulas
From the error checking section choose “enable background error checking” option.
Now from the error checking rules choose the “cells containing years represented as 2 digits” and “number formatted as text or preceded by an apostrophe”.

Wrap Up:

When you import data to Excel from any other program then the chances are high that the dates will be in text format. This means you can’t use it much in formulas or PivotTables.

There are several ways to fix Excel not recognizing date format. The method you select will partly depend on its format and your preferences for non-formula and formula solutions.

So good luck with the fixes…!

Margret

Solved%3a Date Format Issue Excellence

Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.