Excel stores dates as numbers and times as fractions of a day. A cell’s number format controls how those values look; a workbook’s date system controls which calendar date a serial number represents. That distinction explains both ordinary date calculations and dates that shift when copied between workbooks.
What Excel stores in a date or time cell
In the 1900 date system, January 1, 1900 is serial number 1. The whole-number part counts calendar days, while the fraction represents a portion of one day: 0.5 is noon. Microsoft’s example for the 1900 system gives January 1, 2025 as serial 45658, or 45,657 days after January 1, 1900. Microsoft explains Excel’s date system and serial values.
This numeric representation lets Excel calculate with dates. For example, subtracting an earlier date from a later one gives the number of days between them; time fractions can likewise be added or subtracted. Microsoft’s DAYS function illustrates the end-date-minus-start-date calculation.
Why the displayed date is not the stored value
A cell can contain a numeric serial while showing a calendar date, a time, or both. The number format changes the display, not the underlying numeric value. Set a date/time cell to General to inspect its serial and any fractional part; apply a date or time format to show it in a more familiar way. Microsoft documents inspecting values with General format and formatting numbers as dates or times.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Date and time format codes
Common date codes include d or dd for day, mmm for an abbreviated month name, and yyyy for a four-digit year. Time codes include h:mm, h:mm:ss, and AM/PM. In combined formats, m or mm next to an hour code or immediately before seconds means minutes; elsewhere it means month. Use [h]:mm to show elapsed hours above 24 rather than restarting at zero each day. Microsoft also supports formats that display fractional seconds. See its date and time format guidance.
Regional settings and visible surprises
Regional settings affect how typed date-like values are interpreted and displayed. For example, 2/2 may be recognized as a date, with its appearance depending on locale. If the value is meant to remain literal text, enter or format it as text deliberately. If a date appears as #####, the column may simply be too narrow; widen it before treating the value as corrupt. Microsoft describes regional date display and the narrow-column case.
Why dates can change between workbooks
Excel supports two date systems: 1900 and 1904. The same calendar date has serial values 1,462 days apart between them—four years and one day, including a leap day. Microsoft’s example for July 5, 2011 is serial 40729 in the 1900 system and 39267 in the 1904 system. If a numeric value is interpreted using the other system, it can appear shifted by that offset. Microsoft explains the date systems and the 1,462-day difference.
Excel documents automatic conversion options when copying between workbooks, but copied chart dates from a 1904-system workbook may need manual correction. If dates shift after a copy, check the source and destination workbooks’ date-system settings rather than assuming the displayed format is the cause. Microsoft’s documented paths are version-dependent: in Windows desktop Excel, the setting is under File > Options > Advanced > Use 1904 date system; its Mac instructions place it under Excel Preferences and calculation preferences. Check the setting in the version you use. See Microsoft’s date-system setting and copy behavior notes.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Microsoft documentation describes differing platform defaults across support pages and historical contexts. Do not infer a workbook’s system from whether Excel is running on Windows or Mac; inspect the workbook setting itself. Microsoft’s date-system overview and date-system article describe the settings and platform context.
How to tell whether a date is a number or text
Text that looks like a date is not automatically a usable date serial. Use a small diagnostic sequence before applying formulas or replacing source data:
Rank #4
- Inspect the format: change a sample cell to General. A numeric serial will display as a number (possibly with a decimal); text will remain text-like rather than becoming a serial.
- Establish the source convention: identify the locale and ordering of the original text, such as month/day versus day/month. The same string can be ambiguous under different regional settings.
- Convert a sample: use
DATEVALUEonly when Excel recognizes the text as a date. Test representative values, including ambiguous and edge-case entries, before converting a full column. - Validate before replacing: compare converted results with the original text and preserve the source until the parsing is confirmed.
DATEVALUE returns a serial for text Excel recognizes as a date. Interpretation depends on recognized formats and system context; when the year is omitted, Microsoft’s examples use the computer’s current year, and any time information in the input is ignored. Microsoft documents DATEVALUE behavior and provides guidance on converting dates stored as text.
Build reliable dates with formulas
Use DATE for explicit calendar components
DATE(year,month,day) returns a serial number, so apply a date number format if you want the result displayed as a calendar date. Use a four-digit year to avoid ambiguity from two-digit years. DATE can normalize out-of-range month or day inputs instead of rejecting them; for example, a day value beyond a month’s end can roll into the following month. Check formula inputs when unexpected results appear. Microsoft’s DATE function reference covers its arguments and result.
Recommended Free Tools
Use NOW for a recalculating timestamp
NOW() returns a serial date and time. Microsoft’s examples use NOW()-0.5 for twelve hours earlier and NOW()+7 for seven days later. NOW updates when the worksheet recalculates or a macro runs; it does not tick continuously. Format the result as a date/time to read it as a timestamp. Microsoft’s NOW reference gives the serial example and recalculation behavior.
Quick Recap
A practical checklist for date problems
- Wrong calendar date by roughly four years: compare the workbook’s 1900/1904 settings.
- Serial number or decimal showing instead of a date: apply the intended date/time number format.
- Date-looking text fails in arithmetic: establish its locale and convert recognized text, then validate the result.
- Unexpected month/day interpretation: check the source ordering and regional settings.
- Hashes instead of a displayed date: widen the column and recheck the format.
- Formula-generated date looks numeric: format the result as a date; functions such as DATE return serial values.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
