How to Add Dates in Excel: Every Method Explained
Dates in Excel look simple on the surface — but once you start working with them, you quickly discover there's more going on underneath. Excel stores dates as serial numbers, formats them in dozens of ways, and offers multiple methods for entering, calculating, and automating them. Understanding how each approach works helps you choose the right one for what you're actually trying to do.
How Excel Actually Stores Dates
Before anything else, it helps to know what Excel is doing behind the scenes. Every date in Excel is stored as a serial number — a plain integer counting days from January 1, 1900 (which Excel treats as day 1). So January 1, 2025 is stored as the number 45658, not as text.
This matters because it means dates are mathematically workable. You can subtract one date from another to get the number of days between them, add 30 to a date to get 30 days later, or sort a column of dates without any special tricks. It also means that if a cell looks like a date but is stored as text, none of that math will work correctly.
Method 1: Typing a Date Directly
The most straightforward way to add a date is to type it. Excel recognizes common date formats automatically and converts your input into a serial number while displaying it as a date.
Common formats Excel accepts:
1/15/202515-Jan-2025January 15, 20252025-01-15(ISO format)
Once entered, Excel applies its default date format based on your regional settings. If your system is set to U.S. locale, you'll see MM/DD/YYYY. If it's set to UK locale, you'll likely see DD/MM/YYYY. This is one of the first variables that affects how dates behave — and one of the most common sources of confusion when sharing spreadsheets across regions.
📅 If Excel doesn't recognize your typed entry as a date, it will store it as text. You'll usually notice this when the entry is left-aligned in the cell instead of right-aligned (numbers and dates align right by default).
Method 2: Using Excel's Built-In Date Functions
Excel includes a set of functions specifically for creating and working with dates.
TODAY() and NOW()
=TODAY()— Inserts today's date. Updates automatically every time the workbook recalculates.=NOW()— Inserts today's date and current time. Also updates on recalculation.
These are useful for dashboards, trackers, or any sheet where you want the current date to stay live. The trade-off: because they recalculate, the value changes every day (or every time you open the file). If you need a static date that doesn't change, these aren't the right tool.
DATE(year, month, day)
The DATE function lets you build a date from separate components:
=DATE(2025, 1, 15) This returns January 15, 2025. It's especially useful when your year, month, and day values are stored in separate columns and you want to combine them into a single date value.
DATEVALUE(date_text)
If you have dates stored as text strings — imported from another system, for example — DATEVALUE converts them into proper date serial numbers that Excel can calculate with.
Method 3: Keyboard Shortcuts for Static Dates
If you want to stamp a fixed date or time into a cell without it ever changing, Excel has two keyboard shortcuts:
| Shortcut | What It Inserts |
|---|---|
Ctrl + ; | Today's date (static) |
Ctrl + Shift + ; | Current time (static) |
These are hardcoded values — they don't update when the file recalculates. This makes them ideal for logging entries, recording when something was completed, or any situation where you need a permanent timestamp.
Method 4: Adding and Subtracting Dates
Because dates are serial numbers, basic arithmetic works directly on them.
- Add days:
=A1 + 30gives you the date 30 days after whatever's in A1 - Subtract dates:
=B1 - A1gives you the number of days between two dates - Add months: Use
=EDATE(A1, 3)to add exactly 3 months (handles month-length differences correctly) - Add business days: Use
=WORKDAY(A1, 10)to move 10 working days forward, skipping weekends
Simple addition works for days. For months and years, dedicated functions like EDATE and EOMONTH are safer because they account for the varying lengths of months. ➕
Controlling How Dates Display
Entering a date correctly and formatting it are two separate things. You can change how a date appears without changing the underlying value.
Right-click a cell → Format Cells → Number → Date to choose from preset formats, or use Custom to build your own:
DD/MM/YYYY— 15/01/2025MMMM DD, YYYY— January 15, 2025DDD, MMM DD— Wed, Jan 15
The format only affects display. The stored serial number stays the same regardless.
Where Things Get More Complicated
Several factors shape how date entry works in practice:
Regional settings — Locale determines how Excel interprets ambiguous dates like 05/06/2025. Is that May 6 or June 5? That depends on your system's date format setting.
Excel version — Older versions of Excel (pre-2016) don't include functions like IFS or some newer date helpers. Excel for the web and Excel on Mac can also have minor behavioral differences.
Imported data — Data from CSVs, databases, or external APIs often arrives with dates stored as text. Converting these correctly requires extra steps and is one of the more common sources of errors in spreadsheets.
Shared workbooks — When a spreadsheet moves between users in different regions, date display and interpretation can shift in unexpected ways, especially if formats are hardcoded as text.
The method that makes most sense depends on whether you need dates to update automatically or stay fixed, whether you're building formulas on top of them, and whether your data is coming from manual entry or an external source. Those variables — your workflow, your data's origin, and how the spreadsheet will be used — are what determine which approach actually fits.