Excel stores dates as sequential serial numbers so it can use them in calculations. A cell’s number format controls whether that value appears as a date or as a number. Select the affected cells and choose Home > Number Format > Short Date (or Long Date). If the display does not change, the cell may contain text rather than a real Excel date.
Why Excel shows a date as a number
A date in Excel is usually a number underneath. In the 1900 date system, for example, January 1, 1900 is serial 1 and January 1, 2008 is serial 39448. Excel applies a number format to show that stored value as a calendar date; changing the format changes the display, not the value used in calculations. Microsoft explains this behavior in its DATEVALUE function documentation.
As an Amazon Associate I earn from qualifying purchases.
If a cell suddenly displays a number such as 39448 instead of a date, the value may be fine: its format may have changed to General or Number.
Quick fix: apply a date format
- Select the affected cell, range, or entire column.
- Go to Home > Number Format and choose Short Date or Long Date.
- If you need a particular appearance, open Format Cells, choose the Date category, or enter a custom format.
Short Date is the simplest choice for a compact date; Long Date spells out more of it. Custom formats give you more control over how the date is presented. Available styles and their appearance can reflect regional settings. See Microsoft’s guide to formatting dates in Excel.
#1 Best Overall
If date formatting does not work, convert text to a date
Number formatting cannot turn text into a date value. If a cell contains a date-looking string and applying Short Date leaves it unchanged, convert the text first. In a blank cell formatted as General, enter =DATEVALUE(A1), replacing A1 with the cell containing the text. Apply a date format to the result.
DATEVALUE converts date text Excel recognizes, but interpretation can depend on regional date conventions. An ambiguous value such as 03/04/2025 may be read differently depending on whether the source uses month/day or day/month order. Check the original convention and use four-digit years when entering dates. Microsoft’s instructions for converting dates stored as text cover additional conversion options.
Rank #2
- Over 215 Microsoft Windows Excel Shortcuts
- Two-Sided Durable Laminiated Sheet
- Designed for Excel on a Windows Computer
If you need the converted values to replace the original text, copy the conversion results and use Paste Special > Values, then apply a date format. Check the pasted dates before removing the helper formulas or source text.
Recommended Free Tools
Check the workbook date system if dates are shifted
Excel supports both the 1900 and 1904 date systems. For the same calendar date, the serial numbers in those systems differ by 1,462 days—four years and one day. That difference can explain dates that appear shifted after copying values between workbooks.
Compare the date-system setting in both workbooks before changing it. A workbook setting affects how serial values correspond to calendar dates, so changing it can alter how existing values are interpreted. Microsoft documents the 1900 and 1904 date systems and how to change the date system. Defaults and settings can differ by platform; the exact interface may also vary by Excel version.
What if Excel shows ##### instead?
Hash marks are a different symptom from a serial number. First widen the column: the formatted date may not fit. If the marks remain, inspect the formula or value for a negative date or time result, which can also display this way. Microsoft’s guidance on correcting a ##### error describes both causes.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Keep dates as values when you need calculations
Use a date number format when you want a date to remain usable in calculations, sorting, or date-based formulas. The TEXT function can create a formatted text string—for example, =TEXT(A1,"mmmm d, yyyy")—but its result is text, not a date value. That can make later calculations or references harder. Use TEXT only when text output is specifically what you need, and keep the original date value in its own cell when it must remain a date. See Microsoft’s TEXT function documentation.
Quick Recap
Best Value
- 💻 ✔️ EVERY ESSENTIAL SHORTCUT - With the SYNERLOGIC Reference Keyboard Shortcut Sticker, you have the most important shortcuts conveniently placed right in front of you. Easily learn new shortcuts and always be able to quickly lookup commands without the need to “Google” it.
- 💻 ✔️ Work FASTER and SMARTER - Quick tips at your fingertips! This tool makes it easy to learn how to use your computer much faster and makes your workflow increase exponentially. It’s perfect for any age or skill level, students or seniors, at home, or in the office.
- 💻 ✔️ New adhesive – stronger hold. It may leave a light residue when removed, but this wipes off easily with a soft cloth and warm, soapy water. Fewer air bubbles – for the smoothest finish, don’t peel off the entire backing at once. Instead, fold back a small section, line it up, and press gradually as you peel more. The “peel-and-stick-all-at-once” method only works for thin decals, not for stickers like ours.
- 💻 ✔️ Compatible and fits any brand laptop or desktop running Windows 10 or 11 Operating System.
- 💻 ✔️ Original Design and Production by Synerlogic LLC, San Diego, CA, Boca Raton, FL and Bay City, MI, United States 2025. All rights reserved, any commercial reproduction without permission is punishable by all applicable laws.
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.
