Date Not Working in Excel? Causes and Fixes

If a date is not working in Excel, first check whether the cell contains a real date value or text that only looks like a date. If it is a real date, apply a date format; if it is text, convert it using a method that matches the date’s order and regional settings.

Quick checks to identify the problem

Excel stores most dates as numbers and displays those numbers in a date format. That means a date that looks wrong can have two different causes: the underlying value may not be a date, or the value may be correct but displayed in an unexpected way. Check a problem cell before changing data, especially if the workbook contains many dates.

Select the cell and look at the formula bar. A recognized date is usually shown there as a date, although the exact appearance can vary by format.
Change the cell temporarily to General using Home > Number Format. If a number such as 45,000 appears, the cell likely contains a date serial value. If the original date text remains, it may be stored as text.
Check whether the value is aligned or marked differently from nearby dates. Alignment alone is not proof, because formatting can override Excel’s usual alignment behavior.
Try sorting a small copy of the data. Text dates may sort alphabetically rather than in chronological order, which can reveal inconsistent values.

Before converting a whole column, make a copy of the workbook or duplicate the column. Some conversion methods replace the original values, and a mistaken interpretation can turn day and month around.

How Excel dates work

In a normal Excel workbook, a date is represented by a serial number: whole-number increments represent days, and any decimal portion represents a time of day. For instance, a date combined with a time can have a value such as 0.5 added for noon. Excel’s number format controls whether the value appears as a date, a time, a plain number, or a custom display.

This explains why formatting alone cannot make text into a date. A cell containing the characters 4/3/2025 stays text if Excel has not interpreted them as a date; applying a date format changes how numeric values appear, not the meaning of text. Conversely, a valid date displayed as a large number is not necessarily damaged. It may simply be formatted as General or Number.

With a date in A2 and a number of days to add in B2, the formula =A2+B2 returns a later date value. Format the result as a date to see it as a calendar date. If B2 is 7, the result is seven days after the date in A2. If the result looks like a decimal or serial number, adjust the result cell’s number format rather than changing the calculation.

Convert date text into real dates

For a short range, the safest option is often to enter the date again in a form Excel recognizes under your regional settings, then verify it. For a larger imported column, use Text to Columns so you can tell Excel how to interpret the pieces. In Microsoft 365 for Windows, select the cells and choose Data > Text to Columns. Choose Delimited, select Next, leave the delimiters unchecked if the entries are not separated into fields, and select Next. Under Column data format, choose Date and specify the order used in the source, such as MDY or DMY. Select Finish.

Use that date-order choice carefully. If a source contains 03/04/2025, it could mean March 4 or 3 April. The right choice depends on the source data, not on what looks familiar on your computer. Check a few converted values against the original records before relying on the entire column. Text to Columns can also affect adjacent cells if the input is split into multiple columns, so check the preview and destination first.

A formula can convert text when Excel’s regional settings recognize its format. With date text in A2, enter =DATEVALUE(A2) in B2. If Excel recognizes the text, the result is a date serial; format B2 as a date to display it. Copy the formula down for other rows, then, if you need fixed values, copy the results and use Paste Special > Values. DATEVALUE can return an error when the text is ambiguous or does not match a format Excel recognizes. For dates assembled from separate year, month, and day columns, using DATE with those components is generally clearer than relying on ambiguous text.

For example, with year in A2, month in B2, and day in C2, enter =DATE(A2,B2,C2) in D2. If A2 is 2025, B2 is 4, and C2 is 3, the result represents April 3, 2025. DATE expects year, month, and day in that order, regardless of how a displayed date is arranged. It also normalizes out-of-range month or day values, so check the source components instead of assuming an unexpected result is an error message.

Regional settings and ambiguous dates

Excel’s interpretation of typed or imported dates can depend on the language and regional date settings in use. In a month-day-year region, 04/05/2025 is commonly read as April 5; in a day-month-year region, it is commonly read as 4 May. The displayed date can therefore differ across computers even when the original text is identical. This is a common source of errors in shared workbooks and CSV files.

When you control the source, use an unambiguous format such as a four-digit year with clearly identified components, or provide separate year, month, and day fields. For an imported file, find out what convention the sending system uses before converting it. Do not assume that a date that converts without an error converted correctly: ambiguous values may be accepted with the wrong month and day.

If a workbook is used by people in different regions, keep the underlying cells as genuine date values and choose a display format that is clear to everyone, such as a month name. A format such as 3-Apr-2025 is less ambiguous than 03/04/2025, though the words and order can still be localized. For data exchange, document the date convention alongside the file.

Fix dates that display incorrectly

If a date cell shows a serial number, select it and choose a date option from Home > Number Format. To set a specific display, open Format Cells with Ctrl+1 on Windows, select the Number tab, and choose Date or Custom. On Mac, Command+1 opens Format Cells in current versions of Excel; ribbon labels and some options may vary slightly by release. Formatting changes appearance, not the stored date value.

If the cell shows only hash marks, Excel may not have enough width to display the formatted value. Widen the column by dragging its right boundary or double-clicking that boundary to AutoFit. Hash marks can also appear for negative date or time values under some formats. Check the underlying value and formula before assuming column width is the only issue.

For a custom display such as a full month name and four-digit year, select Custom in Format Cells and use an appropriate format code, for example mmmm yyyy. This changes only the display; it does not turn a text entry into a usable date or alter the month and year stored in the cell. Keep in mind that formatting a whole column can make existing numbers look like dates even if they represent something else.

When date formulas do not calculate as expected

Date subtraction works when both inputs are real date values. With a start date in A2 and an end date in B2, =B2-A2 returns the number of elapsed days between them. For example, if A2 is April 1, 2025 and B2 is April 8, 2025, the result is 7. If the result appears as a date, set the result cell to General or Number. If the formula returns an error or an implausible answer, verify that both inputs are numeric dates and that the start and end cells are in the intended order.

For a date that should be built from known components, the DATE function avoids locale ambiguity. With the values 2025, 4, and 3 in A2:C2 respectively, =DATE(A2,B2,C2) in D2 returns April 3, 2025. To check whether one cell contains a numeric value, with the candidate date in A2, use =ISNUMBER(A2) in B2. TRUE means the cell is numeric, but it does not prove that the number is a date; ordinary numbers are numeric too. Check the cell’s format and source as well.

If a formula such as TODAY does not appear to update, confirm that calculation is set to Automatic under Formulas > Calculation Options. TODAY returns the current date according to the computer’s date and recalculates when the workbook recalculates; it is not a continuously ticking clock. A manually entered date, by contrast, remains fixed until you edit it. If a date formula is displayed literally rather than calculated, check whether the cell is formatted as Text or whether the formula begins with an apostrophe. Change the cell to General, then re-enter the formula.

Imported dates, CSV files, and mixed columns

CSV files contain text fields, not Excel’s cell formats. When opening or importing a CSV, Excel may infer date types using the settings on the current computer. A column can also contain a mixture of genuine dates, text dates, blanks, and entries with extra spaces. That mixture can cause sorting, filtering, and formulas to behave inconsistently even when the cells look alike.

For repeatable imports in Microsoft 365 on Windows, use Data > From Text/CSV where available, inspect the preview, and check the detected data types and locale before loading. The options shown depend on the source and Excel build. If the preview interprets dates incorrectly, change the type or use the transformation options to specify the locale before loading, rather than correcting a large table by hand afterward. Excel for the web and Mac may offer different import capabilities and interface details; for a complex or recurring import, use a desktop version with the necessary import controls.

After loading, check several dates from different parts of the column, including values where the day is greater than 12. Those values help expose a swapped day-month interpretation. Also inspect blanks and unusual rows, because a single non-date entry can produce errors when formulas are filled down. Keep the original source field if the imported data is important, so a conversion can be audited or repeated.

Date limits and workbook date systems

Excel workbooks can use the 1900 or 1904 date system. Most modern Windows workbooks use the 1900 system by default; the 1904 system is retained for compatibility with some older workbooks and workflows. The same serial number represents different calendar dates in the two systems, with a difference of 1,462 days. If copied dates shift by several years between workbooks, check whether the workbooks use different date systems rather than editing every cell individually.

In Excel for Microsoft 365 on Windows, the workbook setting is under File > Options > Advanced, in the section labeled When calculating this workbook; the checkbox is Use 1904 date system. Do not switch it casually: changing the setting changes how existing serial values are interpreted. Verify dates after any change, and consider how other people or systems using the workbook will read it.

Excel’s standard date system also has limits. Dates before 1900 are not handled like ordinary modern dates in the 1900 system, and historical date work may require a text convention or a carefully designed alternative. Excel also preserves a compatibility quirk in the 1900 date system related to the year 1900. If a workbook is for historical research, financial records, or scientific analysis, verify how its date range and conventions are handled before relying on built-in date arithmetic.

Frequently Asked Questions

Why does Excel show a number instead of a date?
A real date is stored as a serial number. Select the cell and choose a date format under Home > Number Format; if the number does not correspond to a plausible date, check the original value and workbook date system.

Why does Excel not recognize a date I typed?
The entry may be stored as text, may include extra spaces or an apostrophe, or may use a day-month order that conflicts with your regional settings. Re-enter it in a recognized format or convert it with Text to Columns after confirming the source convention.

How do I convert text to a date without changing day and month?
Use Data > Text to Columns and explicitly choose the source order, such as DMY or MDY. Verify the results against known records before replacing the original data.

Why do my dates change when I open the workbook on another computer?
The computers may use different regional settings, or the workbook may contain text dates that are reinterpreted during import. Store dates as genuine Excel date values and use clear display formats to reduce confusion.

Why is my date formula showing instead of its result?
The cell may be formatted as Text, or an apostrophe may precede the equals sign. Change the format to General and enter the formula again; if recalculation is also stale, check that calculation is Automatic.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.