Excel Date Formulas: every one you need, with examples
Excel stores a date as a whole number: the count of days since 1 January 1900. That is why you can add and subtract dates like numbers. Every formula below uses that idea.
Get a date
| Need | Formula |
|---|---|
| Today, updates daily | =TODAY() — more |
| Now, with time | =NOW() |
| Build from year, month, day | =DATE(2026,9,29) |
| Text to a real date | =DATEVALUE("29/09/2026") — fixes |
Move a date
| Need | Formula |
|---|---|
| Add 30 days | =A2+30 |
| Add 3 months | =EDATE(A2,3) — more |
| End of the month | =EOMONTH(A2,0) |
| First of the month | =EOMONTH(A2,-1)+1 |
| 10 working days later | =WORKDAY(A2,10) — more |
Take a date apart
| Need | Formula |
|---|---|
| Year | =YEAR(A2) — more |
| Month number or name | =MONTH(A2) / =TEXT(A2,"mmmm") — more |
| Day of week | =TEXT(A2,"dddd") — more |
| Quarter | ="Q"&ROUNDUP(MONTH(A2)/3,0) — more |
| Week number | =ISOWEEKNUM(A2) — more |
Compare two dates
| Need | Formula |
|---|---|
| Days between | =B2-A2 — more |
| Working days between | =NETWORKDAYS(A2,B2) — more |
| Whole years (age) | =DATEDIF(A2,TODAY(),"y") — more |
| Years, months, days | DATEDIF |
If a date formula shows a number like 46294
The formula is right; the cell format is wrong. Select it › Ctrl+1 › Date. See date format.
If it shows #VALUE!
One of the “dates” is text. Test with =ISNUMBER(A2): FALSE means text. Convert it with the 3 fixes.
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
