Excel Date Formulas: TODAY, DATEDIF and More

Someone pastes a formula to work out how many days are left until a deadline, hits Enter, and the cell spits back 45292 instead of a date. That number isn’t a bug. It’s Excel showing its work in a way nobody asked for, and it’s the single most common reason people give up on date math halfway through a spreadsheet.

Before getting into cell formatting and why that number shows up, here are the five excel date formula options that cover most real jobs. Each one has a working example you can drop straight into a cell. Everything here was checked in Excel 2021 and 2024, and it behaves the same in Microsoft 365.

The goal is simple enough. Stamp today’s date, build a date from pieces, shift a date forward, measure the gap between two dates, count working days. That’s the whole toolkit most people ever need.

The five excel date formula examples people actually reach for

One example each. No filler.

TODAY stamps the current date and refreshes every time the file recalculates.

=TODAY()

That’s it. Nothing inside the brackets. The excel today function is handy for “days since” or “days until” math because it always reflects the current day. Pair it with a due date like =A2-TODAY() and you get days remaining.

DATE builds a real date from three numbers, which matters when year, month and day live in separate columns.

=DATE(2025, 3, 14)

Returns March 14, 2025. Feed it cell references instead, like =DATE(A2, B2, C2), and it assembles a proper date Excel can calculate with. Corporate Finance Institute has a clean breakdown of the DATE function and how it handles out-of-range months, worth a look if you’re feeding it messy inputs.

EDATE shifts a date by a set number of months, forward or back. Good for renewal dates, billing cycles, anything monthly.

=EDATE(A2, 3)

Three months after whatever’s in A2. Use a negative number to go backwards. There’s also EOMONTH(A2, 0), which returns the last day of the month A2 sits in – which beats trying to remember whether February has 28 or 29 days this year.

DATEDIF is the one for an excel date difference measured in whole units.

=DATEDIF(A2, B2, "d")

Days between A2 and B2. Swap "d" for "m" to get full months, or "y" for full years. More on this one in a second, because it has a strange backstory.

NETWORKDAYS counts working days between two dates and skips weekends automatically.

=NETWORKDAYS(A2, B2)

Add a third argument pointing at a list of holiday dates and it’ll skip those too. For project timelines this does far more useful work than plain subtraction ever will.

Why your date shows up as 45292

Here’s the part the template pages skip. Excel doesn’t store dates as dates. It stores them as serial numbers counting up from January 1, 1900 – so that first day is 1, and a date in early 2024 lands somewhere around 45292. When a cell is formatted as General or Number, you see the raw count instead of the friendly version.

Fixing it takes a few seconds. Select the cell or column, go to Home → Number group → the dropdown, choose Short Date or Long Date. Keyboard route is Ctrl+Shift+3, which applies the default date format in one shot. Once that’s done, 45292 becomes something like 1/1/2024 depending on your regional settings.

“The formula almost always works. It’s the cell format that lies about the result.”

This cuts the other way too. Subtract one date from another, expect a plain number of days, and you sometimes get a date back instead, because Excel guessed you wanted a date. Same fix in reverse – set that result cell to General or Number and the day count appears. Ablebits covers this inheritance behavior in their rundown of Excel DATE formulas and date math, a solid reference when results format themselves in ways you didn’t ask for.

When the date is secretly text

There’s a nastier version of this problem. Sometimes what looks like a date is really text, usually after pasting from a web page, a CSV, or an exported report. Text dates won’t do math. =B2-A2 on text returns an error or a nonsense value, and no amount of reformatting fixes it, because the cell isn’t holding a number at all.

Quick tell: a real date sits right-aligned by default, text sits left. If your “dates” are hugging the left edge of the cell, they’re probably text.

Two reliable fixes:

• =DATEVALUE(A2) converts a text string that looks like a date into a real serial number you can calculate with.
• Data → Text to Columns, click straight through to Finish, then choose a Date format on the final step. This forces the whole column back into genuine dates.

After either fix, apply a date format and the column behaves. ClickUp’s guide to Excel date functions with worked examples walks through a few more conversion cases if you want them.

The DATEDIF oddity worth knowing

DATEDIF has a weird status. It works in every modern version of Excel, including 2021 and 2024, but Microsoft barely documents it and it won’t pop up in the formula autocomplete as you type. You have to know it exists and type the whole thing yourself. Microsoft’s own DATEDIF function support page even flags that it’s provided mainly for compatibility with older spreadsheets, which is an unusual thing to say about a function people still reach for daily.

Supported, though. Not deprecated, not broken, just quietly kept around.

One catch people hit: the "md" unit, which returns leftover days ignoring months, can produce odd or even negative results on certain date pairs. For a straightforward excel date difference in days, months or years, stick to "d", "m" and "y" and it behaves predictably.

Adding days to a date

This one gets overcomplicated for no reason. To do an excel add days to a date operation, you add a plain number.

=A2+30

Thirty days after the date in A2. Because dates are serial numbers underneath, Excel treats +30 as thirty days forward, rolling across month and year boundaries on its own. Subtract to go backward – =A2-14 lands two weeks earlier.

Months are different. Go back to EDATE for those, since adding +30 doesn’t respect that months have different lengths. And if the result shows as a serial number, you already know the fix.

Quick troubleshooting

A handful of issues cover most of what goes wrong with any excel date function:

• Result shows a number, not a date → cell is formatted as General/Number. Apply Short Date (Ctrl+Shift+3).
• Subtraction returns a date instead of a day count → set the result cell to General or Number.
• Formula returns an error on “dates” → they’re text. Use DATEVALUE or Text to Columns.
• DATEDIF won’t autocomplete → expected, it’s undocumented. Type the full formula manually.
• Dates are left-aligned → almost certainly text, not real dates.

All of this assumes a genuine, activated copy of Excel. Date functions behave consistently across Excel 2021, 2024 and Microsoft 365, so a clean licensed install saves a lot of “is it me or is it the software” guessing. For anyone setting up a new machine or replacing a trial version, Keyfy.com offers genuine Office and Windows license keys delivered by email after checkout, which keeps the formulas above working the way the documentation says they should.

FAQ

Why does Excel show a number instead of a date?
The cell is formatted as General or Number, so it displays the underlying serial number. Apply a date format with Ctrl+Shift+3 and it reads as a date.

How do I add days to a date in Excel?
Just add the number: =A2+30 gives thirty days later. Excel handles month and year rollovers automatically because dates are stored as serial numbers.

Is DATEDIF a real function if Excel doesn’t suggest it?
Yes. It works in Excel 2021, 2024 and Microsoft 365. Microsoft keeps it for compatibility and doesn’t surface it in autocomplete, so you type the full formula yourself.

What’s the best way to get the gap between two dates?
=DATEDIF(A2,B2,"d") for whole days, or =NETWORKDAYS(A2,B2) when you want business days only. Plain subtraction =B2-A2 also works if the cell result is formatted as a number.

My dates won’t calculate at all. What’s wrong?
They’re probably text, not real dates. Check the alignment: text sits left, dates sit right. Convert with =DATEVALUE(A2) or run Data → Text to Columns.

Wrapping up

Five excel date formula examples do the heavy lifting: TODAY, DATE, EDATE, DATEDIF and NETWORKDAYS. What stops people isn’t the formulas, it’s the serial number showing up instead of a readable date, and that’s a formatting fix, not a formula failure. Once you know 45292 is just a date in disguise, the whole thing stops feeling fragile.