How To Fix Excel Invalid Date Field Format Error?

Does your Excel workbook show invalid date field format error? Don’t know why you are getting this invalid date format error and how to fix this?

What Could Cause An Invalid Date Error When Importing An Excel File To A Varicent Table?

Date mapping requires matching the source data format with a variant table date format.

 So at the time of date mapping meanwhile the process of importing make sure you have selected the right format.

If the source data is in yyyy/mm/dd format and you are selecting the mm/dd/yyyy format then it’s obvious to encounter an invalid date format error.

How To Fix Invalid Date Field Format Error?

Here are the following fixes that you can try to fix invalid date field format error.

1# Convert Invalid Date Format To Valid One
2# Excel Data Validation For Dates

Method 1# Convert Invalid Date Format To Valid One

Excel allows data input in the MM/DD/YY format until and unless you modify this from the PC’s control panel. It’s the default date format that is accepted by Excel.

For example dates in this format 22/10/2007 or 22.10.2007 are counted as invalid one.

Let’s know how the Text to Columns feature of Excel helps you in resolving the date format issue.

Suppose that you have a recordset containing huge numbers of like this:

Watch out the first date – 8.4.2007 and tell me whether it is 4^th August or 8^th April. Confusing right…?

Well many users choose the entire range and after that from the Format Cell option changes the date format.

But this won’t work because it’s not the valid date format and usage of dot separators is also invalid in Excel.

If this is not counted as a date whereas considered as simple text then how will you fix this issue?

Let’s know-how can you fix this issue.

Step 1.

Add one extra column in your excel sheet like I have added one remarks column.

Now use the ISNUMBER formula as this will ask excel whether the date listed in the first column is number or text.

If your entered date is a number and valid format then it will show the message TRUE otherwise FALSE.

So now you don’t have to change the date showing the TRUE message.

Step 2. Now you need to sort the table data as per the 3^rd column i.e remarks as per the image I have shown.

 so that “FALSE and TRUE” will get sorted in a different block.

This gives you the false record in one side and true records on the other side.

Step 3. Choose the dates for those sections whose remarks are false.

Step 4. From the Data tab hit the Text to column and then choose the Delimited option. At last hit on the Next button.

Step 5. Make sure that you Switched ON none of the checkboxes present in Step 2 of 3. Hit the Next button.

Step 6.

After Entering in step 3 choose the date option and select the date format sequence or current status.

In my case, it is DMY (8.4.2007). Now hit the Finish button.

Now you will see that Excel must have convert your invalid dates into valid dates.

Method 2# Excel Data Validation For Dates

Data validation helps you to limits the entries for the worksheet’s one or more cells.

So let’s know how the Data Validation option helps to fix excel invalid date field format error.

Here in the shown example, employees have to fill the Vacation Request Form. Within cell c4, employees have to assign vacation’s starting date.

Enter the valid date in cell C4, data validation is been used for setting the starting and ending date.

So let’s know how it is to be done.

• Choose the cell C4, and then from the Excel Ribbon hit the Data tab
• Tap to the Data Validation icon.

• In the opened window of data validation, go to the Settings After that from the Allow drop-down menu, choose the Date option.
• From the Data drop-down menu choose the Between option, as you have to limit the date entries between the starting and ending dates.
• For the date range enter the starting date and ending date. In the shown example I am entering January 1, 2017, as the start date and December 31, 2017, as the end date. Well, any date in the year 2017 is counted as a valid one.

• Tap to the OK. After that close the dialog box of data validation.

Wrap Up:

After converting your dates to real ones, you have the option to format them in the number format commands.

For the date, format choose it first and then from excel ribbon use the quick Number formats

OR

To check out more formats, click on the dialog launcher present at the bottom right corner of the Number group present on the Excel Ribbon.

I hope you all will find this post informative and useful.

If you know about some other fixes as well to resolve Excel Not Recognizing Date Format issue then don’t hesitate to share it with us.