Excel FormulasDate formulas in Excel: age, days between, due dates
Dates in Excel follow one idea. Once it is clear, the formulas for ages, deadlines and month ends become straightforward. This page starts with that idea and then gives a formula for each common task.
A date is a number
Excel stores every date as a count of days. Day 1 is 1 January 1900, and each day after it adds one. What you see as 12-Nov-2026 is the number 46338 with a date format on top. A time of day is a fraction: 0.5 is noon.
Three things follow.
- Subtracting two dates gives days.
- Adding a number to a date gives a later date.
- A date cell that shows a five-digit number, or a number cell that shows a date in 1900, simply has the wrong format. Change the format. The value is fine.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Born | Joined | Holiday |
| 2 | Anita | 19-Jul-1988 | 01-Apr-2019 | 26-Jan-2026 |
| 3 | Rohit | 30-Nov-1992 | 15-Jun-2020 | 04-Mar-2026 |
| 4 | Deepa | 29-Feb-1996 | 01-Sep-2021 | 15-Aug-2026 |
| 5 | Manoj | 05-Dec-1999 | 10-Jan-2022 | 02-Oct-2026 |
The examples use a fixed date, 12 November 2026, in place of TODAY(), so that the results on this page do not change. In your own sheet, write TODAY() there.
Days between two dates
=DATE(2026,11,12)-C2Result: 2782=D5-D2Result: 249
Just subtract: the later date minus the earlier. Format the result as General or Number. If it appears as a date in 1900, the cell has inherited a date format.
To count both the first and the last day, add 1.
Age in completed years
=DATEDIF(B2,DATE(2026,11,12),"Y")Result: 38=DATEDIF(B3,DATE(2026,11,12),"Y")Result: 33=DATEDIF(B2,DATE(2026,11,12),"Y")&" years, "&DATEDIF(B2,DATE(2026,11,12),"YM")&" months"Result: 38 years, 3 months
DATEDIF counts whole years, so the age rises on the birthday and not before. Rohit's birthday falls at the end of November, so on 12 November he has not yet turned a year older. Dividing the days by 365 would get cases like his wrong.
Length of service is the same formula with the joining date.
A date some days, months or years ahead
=C2+90Result: 30-Jun-2019=EDATE(C2,3)Result: 01-Jul-2019=EDATE(C2,12)Result: 01-Apr-2020
Add days directly. For months and years use EDATE, which knows that months have different lengths. Ninety days and three months are not the same date.
The end and the start of a month
=EOMONTH(C3,0)Result: 30-Jun-2020=EOMONTH(C3,-1)+1Result: 01-Jun-2020=EOMONTH(C3,0)+1Result: 01-Jul-2020=DAY(EOMONTH(C3,0))Result: 30
In order: the last day of the month of the date in C3, the first day of that month, the first day of the next month, and the number of days in the month.
The day of the week
=TEXT(D2,"dddd")Result: Monday=WEEKDAY(D2,2)Result: 1=IF(WEEKDAY(D4,2)>5,"Weekend","Working day")Result: Weekend
TEXT gives the name. WEEKDAY with 2 as its second argument gives a number where Monday is 1 and Sunday is 7, so anything above 5 is the weekend.
Working days
=NETWORKDAYS(DATE(2026,1,1),DATE(2026,1,31),D2:D5)Result: 21=WORKDAY(DATE(2026,1,22),10,D2:D5)Result: Fri 06-Feb-2026
NETWORKDAYS counts Monday-to-Friday days between two dates, less any holidays in the list. WORKDAY finds the date a number of working days ahead. For a six-day week, use the .INTL versions with weekend code 11.
Year, month, quarter and financial year
=YEAR(C3)Result: 2020=TEXT(C3,"mmm-yyyy")Result: Jun-2020="Q"&ROUNDUP(MONTH(C3)/3,0)Result: Q2=YEAR(C3)-(MONTH(C3)<4)&"-"&RIGHT(YEAR(C3)+(MONTH(C3)>=4),2)Result: 2020-21
The last formula gives the Indian financial year, April to March, in the form 2020-21. A date from January to March belongs to the year that began the previous April. See YEAR, MONTH and DAY.
Overdue, due soon, on time
With a due date in a cell and today's date, a status is a pair of comparisons.
=IF(D3<DATE(2026,11,12),"Overdue by "&(DATE(2026,11,12)-D3)&" days","Not yet due")Result: Overdue by 253 days=IF(D5<DATE(2026,11,12),"Past","Coming")Result: Past
Used as Conditional Formatting rules, the same tests colour overdue rows red.
The next birthday or anniversary
=DATE(2026,MONTH(B2),DAY(B2))Result: 19-Jul-2026=EDATE(B3,12*(DATEDIF(B3,DATE(2026,11,12),"Y")+1))Result: 30-Nov-2026=EDATE(B3,12*(DATEDIF(B3,DATE(2026,11,12),"Y")+1))-DATE(2026,11,12)Result: 18
The first is this year's birthday. The second is the next birthday on or after today: the completed years plus one, added to the date of birth. The third counts the days until it.
Entering dates so that Excel understands them
- Type dates in a form your computer's regional settings expect, such as 12-11-2026 or 12-Nov-2026 on an Indian system. Using the month's name removes any doubt about day and month.
- A real date sits at the right of the cell. One at the left is text, and no date formula will work on it.
- Ctrl and semicolon enters today's date as a fixed value.
- Inside formulas, build dates with DATE, never as text in quotation marks.
Dates that are really text
Imported data often has dates as text. Signs: left-aligned, and =ISNUMBER(A2) is FALSE. Cures:
- Text to Columns on the Data tab: select the column, click Next twice, choose Date and the order of day, month and year, then Finish. This is the most dependable method.
- For text in a fixed pattern such as 20261112, rebuild it:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)).
Common mistakes
- Using TODAY() for a date that should never change, such as an invoice date.
- Adding 30 for "a month" or 365 for "a year".
- Reading a wrongly formatted result as a wrong answer.
- Two-digit years. Type four digits.
- Dividing days by 365 to get age.
- Day and month swapped in data that came from a system with American date order. Check a date where the day is above 12.
Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.