In Excel for Microsoft 365 on Windows, enter a date in a format your regional settings recognize, such as 11/2/2026, then press Enter; Excel stores a valid date as a number and displays it using the cell’s date format. For the least ambiguous date construction in a formula, use the DATE function with separate year, month, and day values.
How Excel date input works
A date in Excel is usually not stored as a string of characters such as 11/2/2026. Excel stores it as a serial number: whole-number values count days from a starting point, and the cell’s number format controls how the value appears. That is why changing a cell from a date format to General may reveal a number rather than the date you typed.
Time can be stored in the same value as a decimal fraction of a day. For example, a value representing noon has a time fraction of one half. A cell formatted to show only a date can hide that time component, so two cells that look like the same date may still contain different values.
This distinction matters for sorting, date calculations, and filtering. A true Excel date can be added to or compared with another date. Text that merely looks like a date may not behave the same way, even when its appearance is convincing.
Ways to enter a date
For a quick one-off entry, click a cell, type a recognizable date, and press Enter. Excel commonly accepts entries with slashes or hyphens, such as 11/2/2026 or 11-2-2026, but which month and day those numbers represent can depend on the computer’s regional settings. If an entry is ambiguous, Excel may interpret it differently than you intend.
- Type a complete date: Include the year, especially in shared workbooks. A two-digit year can be interpreted according to Excel’s date rules rather than your assumption.
- Insert today’s date as a fixed value: On Windows, press Ctrl+; (semicolon). Excel for Mac also uses Control+; for the current date. This inserts a value that will not change tomorrow.
- Build a date in a formula: With the year in A2, month in B2, and day in C2, enter
=DATE(A2,B2,C2). If those cells contain 2026, 11, and 2, respectively, the result is November 2, 2026, displayed according to the result cell’s number format.
DATE is useful when the separate parts come from a form, imported columns, or other calculations. It also avoids asking Excel to guess whether a typed string such as 3/4/2026 means March 4 or April 3. The function normalizes out-of-range month and day values; for example, a day value beyond the end of a month can roll into the following month. Check source data if that normalization would conceal an input mistake.
Choose a date format that is clear
Typing a date and formatting a date are separate operations. If Excel has recognized the entry as a date, select the cell or range and use Home > Number Format to choose a date display. For more choices, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, select the Number tab, and choose Date or Custom. The displayed format does not change the underlying date value.
For a workbook shared across countries, prefer a format that spells out the month or uses a four-digit year. A display such as 2-Nov-2026 is less likely to be mistaken than 11/2/2026. A custom format such as yyyy-mm-dd can also make the order explicit when readers understand that the year comes first. Formatting changes appearance, however; it does not repair text that Excel has not recognized as a date.
Use a date-only display for dates without meaningful times. If the values include appointment times, select a date-and-time format so a hidden time does not make records look identical. For elapsed time, remember that a time value is a fraction of a day and a date-time can include both the day count and that fraction.
Handle regional settings and ambiguous dates
Excel’s interpretation of typed date strings can depend on the operating system’s regional date settings. In one setting, 4/5/2026 may mean April 5; in another, it may mean 4 May. A display format applied afterward cannot tell you whether Excel interpreted the original entry as intended—it only changes how the stored value is shown.
When entering dates manually, include a four-digit year and avoid ambiguous numeric order when you can. A month name, such as 5-Apr-2026, is easier for a person to interpret, though localized month names can still matter when a workbook moves between language settings. For data exchanged between systems, agree on a documented format and verify the imported results rather than assuming every application parses the same text identically.
If a formula needs to construct a date from components, DATE is a good way to remove the ambiguity. If a file is being imported, inspect the preview and resulting values, particularly when dates contain only numbers and separators. The import process and regional settings can affect conversion, so test a few known records before relying on a full column.
Fix dates stored as text
A warning sign is a date-looking value that stays left-aligned under General formatting, refuses to sort chronologically, or is not recognized by date formulas. Alignment alone is not proof—cells can be manually aligned—but it can help identify a text-date problem. Changing the format to Date will not convert text into a date value.
For a column of consistently structured text dates, try selecting the cells and choosing Data > Text to Columns. In the wizard, select Delimited and click Next twice; on the final step, choose Date and the order that matches the source data, such as MDY or DMY, then click Finish. Make a copy of the column first if the values are important, and check the converted results because choosing the wrong order can create valid but incorrect dates.
For a small number of entries, re-enter the values in a recognized format or build them from separate year, month, and day cells with DATE. With the text date in A2, =DATEVALUE(A2) can convert text Excel recognizes as a date into a serial date value. DATEVALUE relies on the system’s date interpretation, so an ambiguous string can still be misread; confirm the result by applying a clear date format and comparing it with the source.
If data comes from a CSV or another system, do not assume that a date-looking column has been converted correctly. Check a few known dates, especially values where the day is 12 or lower, since those can reveal a month/day reversal. Keep the original imported data until the conversion and interpretation have been verified.
Calculate with dates
Once Excel recognizes a date as a date value, ordinary arithmetic can be useful. For example, adding 7 advances the date by seven days. With a valid date in A2, enter =A2+7 in another cell; if A2 contains November 2, 2026, the result is November 9, 2026. Format the result cell as a date if it shows a serial number instead.
Subtracting one valid date from another returns the number of days between them. These simple calculations count calendar days, not business days. A result that appears unexpected may also involve a hidden time fraction: subtracting two date-time values can produce a fractional number of days. Choose a number format that reveals the detail you need before deciding the calculation is wrong.
DATE, DATEVALUE, and basic addition are not substitutes for deciding what a date means in the source data. A due date, a timestamp in a particular time zone, and a date-only event can have different requirements. Excel date values do not by themselves preserve a time-zone label.
Date systems and workbook compatibility
Excel workbooks can use the 1900 or 1904 date system. The systems assign different serial numbers to the same calendar date, with a difference of 1,462 days. If dates are copied between workbooks using different systems, values can shift unless the transfer handles the date-system difference. This is one reason a pasted serial number may not display as the expected date.
Most Windows workbooks use the 1900 date system. Excel retains a historical compatibility quirk in that system: it treats 1900 as though it had a February 29, even though that calendar date did not exist. For everyday modern dates, this rarely affects normal entry, but it matters when comparing serial values with other systems or working with very old dates.
If a workbook’s dates suddenly shift after copying, check whether the source and destination use different date systems before manually changing every value. On Windows, the setting is in File > Options > Advanced, under When calculating this workbook. The corresponding setting and location can differ by Excel version on Mac; check Excel’s calculation preferences for the workbook. Avoid changing the setting casually in a shared file because it can affect every date value in that workbook.
Common date-input mistakes
- Typing only a day number: A value such as 15 may be stored as the number 15, not as the fifteenth day of the current month. Enter a complete date or construct it with DATE.
- Assuming a format fixes the value: Choosing Date from the number-format menu changes display, not text conversion. Convert the text first.
- Using a short year: A two-digit year can be interpreted according to Excel’s date rules. Enter all four digits to make the intended year clear.
- Overlooking hidden time: A date-only display can conceal a time value that affects comparisons and subtraction.
- Trusting a date because it looks right: A text string can look exactly like a date. Test sorting, calculations, or conversion on a copy to confirm Excel recognizes it.
Frequently Asked Questions
What is the best date format to type in Excel?
Use a complete date with a four-digit year and an unambiguous month when possible, such as 2-Nov-2026. For formulas built from separate year, month, and day values, DATE avoids relying on Excel to interpret a slash-separated string.
Why does Excel show a number instead of my date?
Excel may be showing the date’s serial value because the cell is formatted as General or Number. Apply a date format using Home > Number Format. If it remains text or will not calculate as a date, formatting alone is not enough; convert the value.
How do I enter today’s date without it changing?
On Windows, select the cell and press Ctrl+; (semicolon). Excel for Mac uses Control+;. The shortcut inserts the current date as a fixed value. A formula such as TODAY, by contrast, recalculates and is not a permanent record of the date it was first entered.
Why is Excel changing 3/4 into a date?
Excel may interpret an entry containing a slash as a date according to the cell format and regional settings. If you intended text, format the cell as Text before typing or prefix the entry with an apostrophe. If you intended a date, include the year and check which month/day order Excel used.
Can I change how Excel interprets dates?
The interpretation of typed date strings is influenced by regional settings, and the workbook’s date system controls serial numbering. For a particular column of imported text, use a deliberate conversion method such as Text to Columns and choose the source date order. Always verify the result rather than relying on appearance alone.
Related
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Not Working? Fix Common Excel Lookup ErrorsIf VLOOKUP is not working properly, start by using an exact match with FALSE, checking that the lookup…
- Fit All Columns on One Page in Excel Not Working?If Fit All Columns on One Page is not working in Excel, check the active sheet’s scaling, print…
- Excel Text Hidden Behind the Next Cell? How to Fix ItIf Excel text looks hidden behind the next cell, it is usually still in the original cell: Excel…
- Excel Text to Columns With Dates: Fix Date FormatsTo convert text dates in Excel, select the cells, choose Data > Text to Columns, move through the…
- Microsoft Excel VLOOKUP Not Working? Fix Common ErrorsIf Microsoft Excel VLOOKUP is not working, first set its fourth argument to FALSE for an exact match,…
- Excel Function Keys Not Working? Causes, Checks, and FixesIf Excel function keys are not working, first check whether your keyboard’s Fn Lock is making the top…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.