Date and time functions in Excel build dates, take them apart, add months or working days, and measure the gap between two dates. They all rest on one idea: Excel stores a date as a serial number (the count of days since 1 January 1900) and a time as a fraction of a day. Once you see dates as numbers, adding 30 days or subtracting two dates is ordinary arithmetic, and most date problems become easy to explain.
This guide covers DATE, YEAR, MONTH, DAY, DATEDIF, EDATE, EOMONTH, WORKDAY, NETWORKDAYS, week numbers, times, and how to repair text that only looks like a date. Every result was produced by running the formula in Microsoft Excel. TODAY() and NOW() are left out of the examples on purpose, because their results change every day.
In this guide
The short version
- A date is a whole number, a time is a fraction. Format the cell as a date to see it as one.
- Subtract dates to get days.
DATEDIF(start, end, "y")gives complete years, useful for ages. EDATEadds months andEOMONTHfinds month ends. Both handle short months correctly, which adding 30 days does not.WORKDAYandNETWORKDAYSskip weekends and any holidays you list.
Dates are serial numbers
Below, A1 holds 1 January 2024 and A2 holds 15 March 2024, both formatted as dates. The formulas in the table use the General format so you can see the underlying numbers:
| Formula | Excel shows | Why |
|---|---|---|
| =A1 | 45292 | the serial number behind 1 January 2024 |
| =A1+1 | 45293 | one day later is one more |
| =TEXT(A1+1,"yyyy-mm-dd") | 2024-01-02 | shown as a date again |
| =A2-A1 | 74 | days between the two dates |
| =TEXT(A2,"dddd") | Friday | the weekday |
| =INT(45366.75) | 45366 | a time is the fraction after the point; INT removes it |
| =TEXT(45366.75,"yyyy-mm-dd hh:mm") | 2024-03-15 18:00 | 0.75 of a day is 18:00 |
Because dates are numbers, a date result in a General-format cell shows as a number such as 45293. That is not an error: give the cell a date format (Ctrl + 1, Date) and it turns back into a date.
Building a date and taking it apart
DATE(year, month, day) builds a date. YEAR, MONTH and DAY take one out. DATE also accepts values that are too big or too small and rolls them over, which is the trick behind many month-end formulas. A1 holds 15 March 2024:
| Formula | Excel shows | Why |
|---|---|---|
| =YEAR(A1) | 2024 | |
| =MONTH(A1) | 3 | |
| =DAY(A1) | 15 | |
| =TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)),"yyyy-mm-dd") | 2024-03-15 | taken apart and rebuilt |
| =TEXT(DATE(2024,14,1),"yyyy-mm-dd") | 2025-02-01 | month 14 rolls into February of next year |
| =TEXT(DATE(2024,1,0),"yyyy-mm-dd") | 2023-12-31 | day 0 is the last day of the previous month |
| =TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"yyyy-mm-dd") | 2024-03-31 | day 0 of next month: the last day of this month |
| =TEXT(DATE(YEAR(A1),MONTH(A1),1),"yyyy-mm-dd") | 2024-03-01 | first day of this month |
| =WEEKDAY(A1) | 6 | Sunday is 1, so Friday is 6 |
| =WEEKDAY(A1,2) | 5 | with type 2 Monday is 1, so Friday is 5 |
| =TEXT(A1,"mmmm") | March | month name |
| =TEXT(A1,"ddd") | Fri | short weekday name |
| =TEXT(DATE(24,1,1),"yyyy-mm-dd") | 1924-01-01 | a two-digit year 24 is read as 1924, so always write four digits |
The gap between two dates
Subtracting two dates gives the number of days. DAYS(end, start) does the same with the arguments in the other order. For complete years or months, use DATEDIF(start, end, unit), where unit is "y" (years), "m" (months), "d" (days) or "ym" (months left over after the years). It is a legacy function, so it may not show up in the suggestions as you type, but it works. A1 is a birth date, 25 August 1990, and B1 is 15 March 2024:
| Formula | Excel shows | Why |
|---|---|---|
| =B1-A1 | 12256 | days between the dates |
| =DAYS(B1,A1) | 12256 | DAYS takes the end first |
| =DATEDIF(A1,B1,"y") | 33 | complete years, the classic age formula |
| =DATEDIF(A1,B1,"m") | 402 | complete months |
| =DATEDIF(A1,B1,"ym") | 6 | months after the last birthday |
| =DATEDIF(A1,B1,"d") | 12256 | days |
| =DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&" months" | 33 years, 6 months | a readable age |
| =YEARFRAC(A1,B1) | 33.55555556 | years as a fraction |
| =ROUND(YEARFRAC(A1,B1),2) | 33.56 |
If the start date is later than the end date, subtraction gives a negative number, and DATEDIF gives an error:
| Formula | Excel shows | Why |
|---|---|---|
| =B1-A1 | -74 | the gap is negative |
| =DAYS(B1,A1) | -74 | |
| =IFERROR(DATEDIF(A1,B1,"d"),"error") | error | DATEDIF refuses a start date after the end date |
Adding months and finding month ends
Adding 30 days is not the same as adding a month, because months have different lengths. EDATE(date, months) moves a date by whole months and keeps the day, or the last day of a shorter month. EOMONTH(date, months) returns the last day of the month that is that many months away. A1 is 31 January 2024 and A2 is 15 March 2024:
| Formula | Excel shows | Why |
|---|---|---|
| =TEXT(EDATE(A1,1),"yyyy-mm-dd") | 2024-02-29 | 31 January plus one month stops at the end of February (2024 is a leap year) |
| =TEXT(EOMONTH(A1,1),"yyyy-mm-dd") | 2024-02-29 | the last day of next month |
| =TEXT(EDATE(A2,12),"yyyy-mm-dd") | 2025-03-15 | one year later |
| =TEXT(EDATE(A2,-1),"yyyy-mm-dd") | 2024-02-15 | a negative number goes back |
| =TEXT(EOMONTH(A2,0),"yyyy-mm-dd") | 2024-03-31 | 0 means the end of the same month |
| =TEXT(A1+30,"yyyy-mm-dd") | 2024-03-01 | 30 days after 31 January is 1 March, not one month |
| =TEXT(A1+31,"yyyy-mm-dd") | 2024-03-02 | |
| =TEXT(EOMONTH(A2,-1)+1,"yyyy-mm-dd") | 2024-03-01 | the day after the previous month ends is the first of this month |
Working days
WORKDAY(start, days, [holidays]) returns the date that is a number of working days away, skipping Saturdays, Sundays and any dates listed in holidays. NETWORKDAYS(start, end, [holidays]) counts the working days between two dates, including both ends. The .INTL versions let you choose the weekend: code 11 means only Sunday is a day off. Here A1 is Friday 15 March 2024, B1 is a holiday on Monday 18 March, A2 and B2 are 1 and 31 March 2024, and A3 is Saturday 16 March:
| Formula | Excel shows | Why |
|---|---|---|
| =TEXT(WORKDAY(A1,1),"yyyy-mm-dd") | 2024-03-18 | the next working day after Friday is Monday |
| =TEXT(WORKDAY(A1,1,B1),"yyyy-mm-dd") | 2024-03-19 | with Monday a holiday, it is Tuesday |
| =TEXT(WORKDAY(A1,5),"yyyy-mm-dd") | 2024-03-22 | five working days after Friday |
| =NETWORKDAYS(A2,B2) | 21 | weekdays in March 2024 |
| =NETWORKDAYS(A2,B2,B1) | 20 | one fewer when the holiday falls on a weekday |
| =TEXT(WORKDAY(A3,0),"yyyy-mm-dd") | 2024-03-16 | zero working days from a Saturday stays on the Saturday |
| =TEXT(WORKDAY(A3,1),"yyyy-mm-dd") | 2024-03-18 | one working day from Saturday is Monday |
| =TEXT(WORKDAY.INTL(A1,1,11),"yyyy-mm-dd") | 2024-03-16 | with only Sunday off, the next working day is Saturday |
| =NETWORKDAYS.INTL(A2,B2,11) | 26 | 26 days when only Sundays are off |
List holidays in a range of cells, give the range as the third argument, and every date in it is skipped.
Weekdays and week numbers
WEEKDAY(date, [type]) returns a number for the day of the week: with the default type Sunday is 1, and with type 2 Monday is 1. WEEKNUM(date) counts the week of the year starting from the week that contains 1 January. ISOWEEKNUM(date) uses the international standard, where weeks start on Monday and week 1 contains the first Thursday of the year. The two disagree near new year: A1 is Monday 30 December 2024 and A2 is 1 January 2024:
| Formula | Excel shows | Why |
|---|---|---|
| =WEEKNUM(A1) | 53 | 30 December is in week 53 of the Excel system |
| =ISOWEEKNUM(A1) | 1 | but in week 1 of the ISO system, because it belongs to 2025's first week |
| =WEEKNUM(A2) | 1 | |
| =ISOWEEKNUM(A2) | 1 | |
| =TEXT(A1,"dddd") | Monday | |
| =WEEKDAY(A1,2) | 1 | Monday is 1 with type 2 |
Times and durations
A time is a fraction of a day: 12:00 is 0.5 and 18:00 is 0.75. TIME(hour, minute, second) builds one, HOUR, MINUTE and SECOND take it apart, and multiplying by 24 turns it into decimal hours. A1 holds 14:45:30:
| Formula | Excel shows | Why |
|---|---|---|
| =HOUR(A1) | 14 | |
| =MINUTE(A1) | 45 | |
| =SECOND(A1) | 30 | |
| =A1*24 | 14.75833333 | decimal hours |
| =TEXT(A1,"hh:mm AM/PM") | 02:45 PM | 12-hour clock |
| =INT(A1*24*60) | 885 | whole minutes since midnight |
| =TIME(25,0,0)*24 | 1 | 25 hours rolls over to 1:00, the next day |
| =INT(45366.75)-45366 | 0 | the date part of a date-time |
Adding up hours
Three shifts of 9 hours 30 minutes make 28 hours 30 minutes. The h:mm format only shows the time of day, so it wraps around after 24 hours. Put the hours in square brackets to show the total:
A4 uses h:mm, B4 uses [h]:mm; both hold the same total
| A | B | |
|---|---|---|
| 1 | 9:30 | |
| 2 | 9:30 | |
| 3 | 9:30 | |
| 4 | 4:30 | 28:30 |
What is inside the cells
| A | B | |
|---|---|---|
| 1 | =TIME(9,30,0) | |
| 2 | =TIME(9,30,0) | |
| 3 | =TIME(9,30,0) | |
| 4 | =SUM(A1:A3) | =SUM(A1:A3) |
A shift that crosses midnight
Start 22:30, end 06:15 the next morning. Subtracting gives a negative number, and Excel cannot show a negative time, so the cell shows hash signs. MOD(end - start, 1) fixes it by wrapping the result into one day:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 22:30 | 6:15 | ######### | 7:45 | 7.75 |
Text that should be a date
Dates that come from other systems are often text: they sit at the left of the cell, and SUM, sorting and date functions ignore them. The safest conversions are those that do not depend on your regional settings. Below, A1 is the text 2024-03-15, A2 is 20240315, A3 is 2024-03 and A4 is 15 March 2024:
| Formula | Excel shows | Why |
|---|---|---|
| =ISNUMBER(A1) | FALSE | it is text |
| =TEXT(DATEVALUE(A1),"yyyy-mm-dd") | 2024-03-15 | DATEVALUE understands the ISO layout yyyy-mm-dd |
| =TEXT(–A1,"yyyy-mm-dd") | 2024-03-15 | a double minus does the same |
| =TEXT(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),"yyyy-mm-dd") | 2024-03-15 | for compact yyyymmdd, cut it up and rebuild it with DATE |
| =IFERROR(DATEVALUE(A3),"#VALUE!") | #VALUE! | a year and month without a day cannot be converted |
| =TEXT(DATEVALUE(A4),"yyyy-mm-dd") | 2024-03-15 | a day, month name and year works too |
Dates written as 05/03/2024 are read as 5 March or 3 May depending on the computer’s regional settings, so do not trust a conversion that relies on them. Use the year-first layout, or cut the text apart and rebuild it with DATE.
Common mistakes
Mistake 1: a result shown as a number
A formula that returns a date can appear as 45293 when the cell has the General format. Format the cell as a date.
Mistake 2: adding 30 days for a month
31 January plus 30 days is 1 March, while EDATE(A1, 1) gives the end of February. Use EDATE for “same day next month”.
Mistake 3: comparing a date to a date-time
A cell that holds a date and a time is not equal to the plain date, because of the time fraction. Compare INT(cell) with the date:
A1 is a date, B1 is that date plus half a day; C1 =A1=B1, D1 =INT(B1)=A1
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2024-03-15 | 2024-03-15 12:00 | FALSE | TRUE |
What is inside the cells
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2024-03-15 | =A1+0.5 | =A1=B1 | =INT(B1)=A1 |
Mistake 4: a two-digit year
DATE(24, 1, 1) is the year 1924, not 2024. Always write four digits.
Mistake 5: forgetting the Excel calendar starts in 1900
Excel counts from 1 January 1900 and, for compatibility with an old spreadsheet program, treats 1900 as a leap year. Day number 60 is therefore a 29 February 1900 that never existed. It only matters for dates before March 1900, but it is why very old dates in Excel are unreliable:
| Formula | Excel shows | Why |
|---|---|---|
| =TEXT(59,"dd-mmm-yyyy") | 28-Feb-1900 | |
| =TEXT(60,"dd-mmm-yyyy") | 29-Feb-1900 | a date that did not exist |
| =TEXT(61,"dd-mmm-yyyy") | 01-Mar-1900 | |
| =TEXT(DATE(1900,3,1)-DATE(1900,2,28),"0") | 2 | two days between 28 February and 1 March 1900, instead of one |
Cheat sheet
| Task | Formula | Note |
|---|---|---|
| Build a date | =DATE(2024, 3, 15) | always four-digit years |
| Year, month, day of a date | =YEAR(A1), =MONTH(A1), =DAY(A1) | |
| Days between | =B1-A1 or =DAYS(B1, A1) | format the result as General |
| Complete years (age) | =DATEDIF(A1, B1, "y") | hidden function, start must be earlier |
| Add months | =EDATE(A1, 3) | negative goes back |
| Last day of month | =EOMONTH(A1, 0) | or DATE(YEAR(A1), MONTH(A1)+1, 0) |
| First day of month | =EOMONTH(A1, -1) + 1 | |
| Add working days | =WORKDAY(A1, 10, holidays) | skips weekends |
| Count working days | =NETWORKDAYS(A1, B1, holidays) | includes both ends |
| Weekday name | =TEXT(A1, "dddd") | or WEEKDAY for a number |
| ISO week number | =ISOWEEKNUM(A1) | WEEKNUM differs near new year |
| Hours between times | =MOD(B1 – A1, 1) * 24 | works across midnight |
Try it yourself
Work out each answer first, then open the solution.
1. A1 holds a birth date (8 November 1995) and B1 holds 15 March 2024. Write a formula for the age in complete years.
Show solution
| Formula | Excel shows | Why |
|---|---|---|
| =DATEDIF(A1,B1,"y") | 28 | the birthday has not come yet this year, so 28 |
| =INT(YEARFRAC(A1,B1)) | 28 | a fraction-based alternative |
2. An invoice is dated 31 January 2024 and is due one month later. What do A1+30, EDATE(A1,1) and EOMONTH(A1,0) return?
Show solution
| Formula | Excel shows | Why |
|---|---|---|
| =TEXT(A1+30,"yyyy-mm-dd") | 2024-03-01 | not a month |
| =TEXT(EDATE(A1,1),"yyyy-mm-dd") | 2024-02-29 | one month later, stopping at month end |
| =TEXT(EOMONTH(A1,0),"yyyy-mm-dd") | 2024-01-31 | the end of the same month |
3. A project runs from Monday 11 March 2024 to Friday 22 March 2024, and Monday 18 March is a holiday. How many working days is that?
Show solution
| Formula | Excel shows | Why |
|---|---|---|
| =NETWORKDAYS(A1,B1) | 10 | two full weeks of weekdays |
| =NETWORKDAYS(A1,B1,C1) | 9 | minus the holiday |
4. A1 holds 17 May 2024. Give the last day of that month, the first day of that month, and the number of days left in the month.
Show solution
| Formula | Excel shows | Why |
|---|---|---|
| =TEXT(EOMONTH(A1,0),"yyyy-mm-dd") | 2024-05-31 | |
| =TEXT(EOMONTH(A1,-1)+1,"yyyy-mm-dd") | 2024-05-01 | |
| =EOMONTH(A1,0)-A1 | 14 | days left after the 17th |
Frequently asked questions
How does Excel store dates?
As serial numbers: the number of days since 1 January 1900, so 1 January 2024 is 45292. Times are fractions of a day. A date format only changes how the number is displayed.
How do I calculate age in Excel?
Use =DATEDIF(birth_date, TODAY(), "y") for complete years, and "ym" for the months after the last birthday.
What is the difference between EDATE and EOMONTH?
EDATE returns the same day in a month that is a number of months away. EOMONTH returns the last day of that month.
How do I count working days between two dates in Excel?
=NETWORKDAYS(start, end, holidays) counts weekdays including both dates and skips any holidays in the list. Use NETWORKDAYS.INTL for a different weekend.
Why does Excel show my date as a number?
The cell has a General or Number format. Apply a date format with Ctrl + 1 and it displays as a date again.
Why does my date formula give #VALUE!?
One of the dates is probably text. Convert it with DATEVALUE or DATE, or with Text to Columns, before doing arithmetic.
How do I get the last day of a month in Excel?
=EOMONTH(A1, 0). The first day is =EOMONTH(A1, -1) + 1.
Why can’t I show a negative time in Excel?
In the default 1900 date system a negative time shows as hash signs. Use MOD(end - start, 1) for durations that cross midnight.
Test yourself
Timed questions on Dates and Times, with an explanation for every answer.
