Practice free

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

All date and time functions

FunctionWhat it doesLevelSinceSpills?
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