Date Serial Number

Excel stores dates as serial numbers and applies a date format for display. In its 1900 date system, 1 November 2024 is held as 45597 and shown as a date only because the cell is formatted that way. Times are the fractional part of the same number: half a day is 0.5, so a timestamp is a date with a decimal on the end.

Knowing this explains several behaviors at once. Subtracting two dates yields a count of days because it is plain subtraction. Adding 7 moves a date a week forward. A cell that suddenly displays 45597 has lost its format, not its value, and formatting restores it. Sorting dates works correctly because it is sorting numbers — provided every cell in the column really is one.

That proviso is where the trouble lives. A date imported as text looks identical and behaves nothing alike: it will not subtract, it sorts alphabetically so that 10 February precedes 2 January, and date calculations on it are unreliable. Some functions can convert a recognizable date string, DATEVALUE being the explicit one, but whether a given string converts depends on the function, the string and the locale, so a text date cannot be trusted to calculate consistently. The safer habit is to check the format and locale and convert text dates into real numeric dates. One subtraction that happens to work is not proof that a cell holds a date, because some operations coerce text silently; a cell that has been converted, or that sorts chronologically and aligns right by default as numbers do, is better evidence. Date arithmetic can also fail on its own terms — DATEDIF with the end date before the start date returns #NUM! rather than a negative count.

Two practical cautions. Applications differ in epoch and in how they resolve two-digit years, so a file moved between tools can shift dates rather than fail visibly; verify a known date after any import. And ambiguous text like 03/04/2024 is read as March or April depending on the locale the import assumed, which is a reason to prefer an unambiguous written format such as 2024-03-04 in anything you export or share.

References:

• DATE function
• Format a date the way you want