Excel date functions
Excel stores every date as a number: a count of days in which January 1, 1900 is day 1. That is why you can subtract one date from another, add 30 to get a due date, or sort a column chronologically. Date functions build dates from parts, pull parts back out, and move by months or working days so you don't have to count calendars by hand.
- Functions
- 24
- With a graded exercise
- All 24
- Start with
DATE
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Build a real date from a year, month and day | DATE | Handles month and day overflow for you, so DATE(2026,13,1) becomes January 1, 2027. |
| Add or subtract whole months | EDATE | Keeps the day of the month and clamps to the month's last day when it has to. |
| Get the last day of a month | EOMONTH | The standard way to find month-end for billing, reporting and due dates. |
| Count working days between two dates | NETWORKDAYS | Skips weekends and an optional list of holidays. |
| Find a deadline a number of working days away | WORKDAY | Moves forward or back by business days, skipping weekends and holidays. |
| Calculate age or tenure in whole years | DATEDIF | Counts complete years, months or days between two dates. |
Try DATE live
The most useful date function, on a sample sheet you can edit. Change a value and watch the results update.
F2=DATE(A2,B2,C2) 7/15/2026 Builds July 15, 2026 from three numbersF3=DATE(A3,B3,C3) 2/1/2027 Month 14 rolls over into February of the next yearF4=DATE(YEAR(E2),MONTH(E2),1) 9/1/2026 First day of the month for the order date in E2
All 24 date functions
| Function | What it does | Practice exercise |
|---|---|---|
DATE function | DATE builds a real date from a year, month and day. | Build due dates |
DATEDIF function | DATEDIF counts the complete years, months or days between two dates. | Years and months of service |
DATEVALUE function | DATEVALUE converts a date that is stored as text into a real date that Excel can sort, subtract and format. | Fix imported dates |
DAY function | DAY returns the day of the month from a date, as a number from 1 to 31. | Bill due days |
DAYS function | DAYS returns the number of days between two dates. | Days to pay |
EDATE function | EDATE returns the date a given number of months before or after a start date, keeping the same day of the month. | Contract end dates |
EOMONTH function | EOMONTH returns the last day of the month a number of months before or after a date. | Bill at month-end |
HOUR function | HOUR returns the hour of a time as a number from 0 to 23, so 1:45 PM gives 13. | Hour of each order |
ISOWEEKNUM function | ISOWEEKNUM returns the ISO 8601 week number of a date: weeks start on Monday, and week 1 is the week that contains the year's first Thursday. | ISO week numbers |
MINUTE function | MINUTE returns the minutes part of a time as a number from 0 to 59, so 1:45 PM gives 45. | Minutes past the hour |
MONTH function | MONTH returns the month of a date as a number from 1 to 12. | Year and month |
NETWORKDAYS function | NETWORKDAYS counts the working days between two dates, Monday to Friday, including both the start and end date. | Business days |
NETWORKDAYS.INTL function | NETWORKDAYS.INTL counts the working days between two dates, like NETWORKDAYS, but lets you say which days are the weekend: Friday and Saturday, Sunday only, or any pattern written as a seven-character string such as "0000011". | Six-day weeks |
NOW function | NOW returns the current date and time, and updates whenever the workbook recalculates. | Hours since opened |
SECOND function | SECOND returns the seconds part of a time as a number from 0 to 59. | Seconds of each lap |
TIME function | TIME builds a time from hours, minutes and seconds, such as TIME(13,30,0) for 1:30 PM. | Finish times |
TIMEVALUE function | TIMEVALUE converts a time written as text, such as "1:30 PM" or "13:30", into a real time you can add, subtract and format. | Shift lengths from text |
TODAY function | TODAY returns the current date, with no time attached, and updates every time the workbook recalculates. | Days until due |
WEEKDAY function | WEEKDAY returns the day of the week for a date as a number. | Number the shift days |
WEEKNUM function | WEEKNUM returns the week number of a date in its year, 1 to 54. | Week of each order |
WORKDAY function | WORKDAY returns the date a given number of working days before or after a start date, skipping weekends and any holidays you list. | Ship dates |
WORKDAY.INTL function | WORKDAY.INTL returns the date a number of working days before or after a start date, skipping the weekend days you choose and any holidays. | Due dates with Sunday off |
YEAR function | YEAR returns the four-digit year from a date. | Year and month |
YEARFRAC function | YEARFRAC returns the fraction of a year between two dates, such as 0.5 for six months. | Prorated bonus |
Learn them in order
These functions are taught step by step in the Dates track. Each lesson ends with graded exercises on real-looking data.
- DATE, YEAR, MONTH & TEXT8 min · Pro
- EDATE, EOMONTH & NETWORKDAYS9 min · Pro
- Tenure, quarters & weekdays10 min · Pro
Other function groups
Updated Checked by running: every formula on this page was run by our formula engine on .