Excel not recognizing dates properly

Question

I have excel 2019. it isn't recognizing dates properly. I have tried changing its date formats. but there is always some issue.

like after I write a date and autofill it downwards, the year goes on increasing instead of the date. is there a way to reset excel to default so that everything goes back to normal?

Answer 1

Can you show us a screen shot. The most likely problem is that the dates are text not numbers. Try referencing one of the date cells with the formula =ISTEXT(A10) where A10 is one of the problem cells. If the result of the formula is TRUE you know you have the wrong data type, text not numbers. You can also type =SUM(X1) into one of the problem date cells, if you see =SUM(X1) and not a value, you have text.

Shane Devenshire

Answer 2

Dates in Excel are tricky until you understand how they work.

.

In Excel a "date" is a special "Data type", like the other data types Text, Number, accounting, general, scientific, etc ...

.

The trick to dates comes in several levels:

• date is a data type
• Excel auto converts text date to date data type
• the date displayed on screen is a "custom format" to make it user friendly
• the custom format can be changed
• underlying the displayed date is the "real' date, an integer serial number
• times are part of dates
• clock time of day is a decimal fraction of the date
• time duration is also a data type

.

Part of the trick to dates is that when you type a date in a format that excel recognizes, it automagically converts the input date into the date data type. That is part of your problem. Excel is not recognizing what you entered as a valid "date".

Autoformat Dates

! Changing Input Conventions 2016 09 29 ****https://excel.tips.net/T007659_Changing_Input_Conventions.html
Different cultures have different conventions for displaying numbers and for parameters in Excel's worksheet functions. Here's how you can change which conventions Excel uses.
Roy is from the UK but he works in Italy. There are two major differences in how Excel works on his system in Italy when compared to how his UK system works. One is that commas are used instead of decimal points. The second is that when Roy is working with functions he must enter semicolons between function parameters instead of commas. Both differences are frustrating to him, as he is used to entering decimal points and commas (in functions) without even thinking about it. Roy wonders if there is a way to make the Italian version of Excel function like his copy back in the UK.
.

@ Convert date format between European and US in Excel?
https://www.extendoffice.com/documents/excel/4162-excel-convert-european-date-to-us-date.html
If you work in an international company, you will receive sheets from different counties every day which may record dates with different date formats. For example, the European counties record dates in the date format dd/mm/yyyy, while US record dates in the date format mm/dd/yyyy. In this case, how can you quickly convert the date format between European countries and US in Excel?
.  *  Convert texted date from European dd/mm/yyyy to US mm/dd/yyyy with formula
.  *  Convert date format between European and US with Format Cells
.

@ Localization- Dates Displayed in Different Languageshttps://www.myonlinetraininghub.com/excel-dates-displayed-in-different-languages
If you share Excel files and reports with users from different countries then you might want to give them a report in their language…and I don’t just mean converting from English to American. It’d be a load of work to create a separate file for each language, so instead we can automate it like this:
.
https://d13ot9o61jdzpp.cloudfront.net/images/excel_date_languages.gif
ET MR Dates and Times.docx.

! Date Format in Excel
https://www.excel-exercise.com/date-format-in-excel/
In Excel, you can display the same date in many different ways just by changing the date format.
.  *  Dates are whole numbers
.  *  Explanation of the numbering
.  *  Short Date Format
.  *  Long Date Format
.  *  Customize your date format
.  *  Date parameters
.  *  How to customize a date?
.  *  Don't write text in your date
.  *  Different displays in function of the format code
.  *  Different examples of custom date
.

Display / Custom Date/Time format codes

! Custom Date Formats in Excelhttps://www.myexcelonline.com/blog/custom-date-formats-in-excel/
Custom date formats in Excel allow you to display only certain parts of the date.
Say you had a date of 18/02/1979, which coincides to be my birthday.
You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the month as a number on in abbreviated form and the year in two or four digits.
You cam also mix and match to create a custom date format or even enter a custom text that would show something like: Today is Sunday
You can download the following workbook which shows you the different formats that you can use and see the tutorial below of how this can be easily achieved.
DOWNLOAD WORKBOOK
CODE OUTPUT       DATE/TIME  APPEARS AS
m                  Displays the month as a number                                  18/02/1979    2
mm               Displays the month as a number with leading zeros  18/02/1979    02
mmm            Displays the month in abbreviated form                       18/02/1979    Feb
mmmm         Displays the month in full form                                    18/02/1979    February
mmmmm      Displays the first letter of the month                            18/02/1979    F
d                   Displays the day as a number                                     18/02/1979    18
dd                 Displays the day as a number with leading zeros          01/02/1979    01
ddd               Displays the day in abbreviated form                           18/02/1979    Thu
dddd             Displays the day in full form                                        18/02/1979    Thursday
yy                 Displays the last two digits of the year                         18/02/1979    79
yyyy              Displays all the digits of the year                                 18/02/1979    1979
mmmm d, yyyy   Displays the month, the date and the year           18/02/1979    February 18, 1979
mmmm-yyyy Displays the month-year                                             18/02/1979    February-1979
“Today is” dddd   Displays a custom text for the Today function      11/06/2015    Today is Thursday
h                   Displays the hour as a number                                    9:05:13         9
hh                 Displays the hour as a number with leading zeros       9:05:13         09
m                  Displays minute as number                     9:05:13          5
mm               Displays minutes as a number                9:05:13          05
s                   Displays seconds as a number                 9:05:03          3
ss                 Displays seconds as a number                 9:05:13          03
ss.s              Display tenth of second                          9:05:13.3          13.3
ss.00            Displays hundredths of second               9:05:13.03     13.03
AM/PM         Displays the hour indicating AM or PM    9:55:13          AM
https://www.myexcelonline.com/wp-content/uploads/2015/06/Number-Formats-Dates.gif
.

Convert Text to Excel Date Format

Here are a couple of articles on various techniques to convert text to date format

**Convert Text To Date**       2019 05 13 ****https://excelchamps.com/blog/text-to-date/
Dates play an important role when we need to analyze trends. And, this is one of the most important things which we need to capture in a right way.In Excel, the right format to insert a date is mm/dd/yyyy. That’s the basic date format which is used by Excel to store dates.
      1. Use DATEVALUE Function To Convert a Text to Date
      2. Date with Back Slashes
      3. Date with Month Name
      4. Date with Dots
      5. Date with the Month Name and a Comma
      6. Date with the Day Name
      7. Date with a Day Name in the End
      8. Date Having a Suffix with Day
      9. Date with Space Between Day, Month and Year
    10. Date with Short Month Name
    11. Date Without Any Space Between Day, Month and Year
.
**6 Ways to Fix Dates Formatted as Text in Excel**      2014 02 18       Mynda Treacy
https://www.myonlinetraininghub.com/6-ways-to-fix-dates-formatted-as-text-in-excel
If you import data to Excel from another program chances are the dates will come in formatted as text, which means they’re not much use to you in formulas or PivotTables. There are many ways to fix the dates and the method you choose will depend partly on the format they’re in and partly based on your preference for a formula or non-formula solution.
    1. VALUE Function
2. DATEVALUE Function    3. Find & Replace
    4. Text to Columns
    5. VALUE & SUBSTITUTE Functions
    6. Error Checking
ET MR Dates and Times.docx.

Date/Time general tips

**Extended Date Functions**: 17 MVP tips, tricks and shortcuts for Excel
https://spreadsheetpage.com/use-excel-tips-tricks/
The issue many Excel users face is that it’s so densely packed with useful tools that even accomplished veterans get lost, and beginners get easily overwhelmed. They need a guru. Someone to explain to them the intricate details of the world of Excel and the dangers of the dark side of the Force.
The Excel MVPs are recognized as “Excel wizards”. MVP’s are far from normal. Which is precisely why we reached out to 154 MVPs and asked them to share their best tips, tricks, and shortcuts. With great power comes great responsibility, and the Excel wizards of the world were more than willing to put on their superhero capes and save the day. So, let’s venture into expert wisdom territory, shall we?
.  * Shortcuts ****.     *  Alt + down arrow: instant drop down list
.     *  Alt-T-I and Alt-T-M-S:
.     *  Disable Excel 2003 Menu Accelerators keys in Excel 2007-2016
.     *  Alt+F11: Open VBA Editor
.     *  Alt+F10: display Selection Pane for shapes and layers
.     *  Ctrl+T: define Excel table
.     *  F9: Calculate selected part of a formula
.     *  F4: Repeat last action
.     *  Ctrl+Shift+L: Apply filter to header row
.  * Tips and Tricks ****.     *  Learn power query
.     *  Data types
.     *  Data validation
.     *  Coloring the active cell,
.     *  Coloring Row/Column ID
.     *  Copy here as values only / Paste Special
.     *  A few tips and tricks
.        *  Remove Duplicates
.        *  Flash fill
.        *  Expand/Collapse Formula Bar
.        *  Break Formula Linki: Paste Special Values
.        *  Format painter
.        *  Hide Sheet
.     *  The UNIQUE function
.     *  Named formulae
.     *  Making a duplicate copy of a sheet: <CTL><DRAG>
.  *  BONUS
.     *  17. F#: Programming language concepturally close to Excel
.