Date and time functions
Date functions build, split and count dates and times. Excel stores a date as a serial number, and these functions do the arithmetic for you.
25 functions in this category.
Start here
- DATE
Builds a real date from separate year, month and day numbers. Analysts use it to assemble dates from split columns or to shift dates by adding to the month or day part.
- DATEDIF
Counts complete years, months or days between two dates, which makes it the usual choice for ages and tenure. It still works but is undocumented in the function wizard.
- DAY
Pulls the day-of-month out of a date. Often paired with MONTH and YEAR when splitting or regrouping dates.
- TODAY
Returns the current date from the computer clock and refreshes each time the workbook recalculates. Typical for ages, days-until and overdue flags.
- WEEKDAY
Tells you which weekday a date falls on as a number, handy for weekend flags or day-of-week grouping.
- MONTH
Pulls the month out of a date, handy for grouping sales or events by month in helper columns.
All date and time functions
| Function | What it does | Level | Since | Spills? |
|---|---|---|---|---|
DATE | Builds a real date from separate year, month and day numbers. Analysts use it to assemble dates from split columns or to shift dates by adding to the month or day part. | Beginner | 2007 or earlier | No |
DATEDIF | Counts complete years, months or days between two dates, which makes it the usual choice for ages and tenure. It still works but is undocumented in the function wizard. | Intermediate | 2007 or earlier | No |
DATEVALUE | Turns a date stored as text into a real date number you can sort and calculate with. Useful after importing data where dates came in as text. | Intermediate | 2007 or earlier | No |
DAY | Pulls the day-of-month out of a date. Often paired with MONTH and YEAR when splitting or regrouping dates. | Beginner | 2007 or earlier | No |
DAYS | Gives the day count between two dates. Note the argument order puts the end date first, which differs from DATEDIF. | Beginner | 2013 | No |
DAYS360 | Calculates a day count under the 30/360 convention used in some accounting and bond calculations. Most everyday work uses plain subtraction instead. | Expert | 2007 or earlier | No |
EDATE | Shifts a date by whole months and keeps the day of month where it can. Handy for renewal dates, billing schedules and due dates. | Beginner | 2007 or earlier | No |
EOMONTH | Returns the month-end date, optionally several months away. Common for month-end reporting, and EOMONTH(date,-1)+1 gives the first day of the current month. | Beginner | 2007 or earlier | No |
HOUR | Extracts the hour from a time value. Useful for bucketing timestamps by hour of day. | Intermediate | 2007 or earlier | No |
ISOWEEKNUM | Returns the ISO week number, where weeks start on Monday and week 1 contains the year's first Thursday. Use it when reports must match European or ISO calendars. | Intermediate | 2013 | No |
MINUTE | Extracts the minutes from a time value. | Intermediate | 2007 or earlier | No |
MONTH | Pulls the month out of a date, handy for grouping sales or events by month in helper columns. | Beginner | 2007 or earlier | No |
NETWORKDAYS | Counts weekdays between two dates, including both ends, minus any listed holidays. Used for lead times, SLAs and project durations. | Intermediate | 2007 or earlier | No |
NETWORKDAYS.INTL | Like NETWORKDAYS but lets you choose which days are weekends, for regions or shift patterns that differ from Saturday and Sunday. | Intermediate | 2010 | No |
NOW | Returns the current date and time from the computer clock and updates whenever the workbook recalculates. Good for timestamps on live reports, not for permanent records. | Beginner | 2007 or earlier | No |
SECOND | Extracts the seconds from a time value. | Intermediate | 2007 or earlier | No |
TIME | Builds a time value from hour, minute and second numbers, returned as a decimal part of a day. | Intermediate | 2007 or earlier | No |
TIMEVALUE | Converts a time stored as text into a numeric time you can calculate with. | Intermediate | 2007 or earlier | No |
TODAY | Returns the current date from the computer clock and refreshes each time the workbook recalculates. Typical for ages, days-until and overdue flags. | Beginner | 2007 or earlier | No |
WEEKDAY | Tells you which weekday a date falls on as a number, handy for weekend flags or day-of-week grouping. | Beginner | 2007 or earlier | No |
WEEKNUM | Returns the week of the year for a date. Week 1 is the one containing 1 January in the default system. | Intermediate | 2007 or earlier | No |
WORKDAY | Adds working days to a date, skipping Saturdays, Sundays and any listed holidays. Good for delivery dates and deadlines. | Intermediate | 2007 or earlier | No |
WORKDAY.INTL | Same idea as WORKDAY but you choose the weekend pattern, which suits regions with Friday-Saturday weekends or rotating shifts. | Intermediate | 2010 | No |
YEAR | Extracts the year from a date for grouping or filtering by year. | Beginner | 2007 or earlier | No |
YEARFRAC | Converts a span between two dates into fractional years, useful for pro-rating, tenure and interest accrual. | Intermediate | 2007 or earlier | No |
Other categories
- Lookup and reference functions
43 functions
- Math and trig functions
80 functions
- Logical functions
19 functions
- Text functions
48 functions
- Statistical functions
111 functions
- Financial functions
56 functions
- Information functions
21 functions
- Database functions
12 functions
- Engineering functions
54 functions
- Web functions
3 functions
- Compatibility functions
41 functions
- Cube functions
7 functions
- Add-in and automation functions
5 functions