Date and time functions

The functions in this category allow you to work with Excel defined date and time values. Excel stores dates as integers, 1 is 1/1/1900 and 2 is 1/2/1900. This explains why Excel can't work with dates earlier than 1/1/1900.

Today is 9/17/2018 and the Excel date is represented by 43360. The weekday is Monday and the week starts with Sunday in North America thus Monday is the second weekday in the week, demonstrated in cell C9.

Excel converts a date automatically, try and type a date in a cell and then select the cell, press CTRL + 1 to open the "Format cells" dialog box. Now change the format to "General" and press OK button. You can now see the integer in the cell that represents the date value.

Date functions Returns
YEAR function The year of an Excel date.
MONTH function The month of an Excel date.
DAY function The day of the month from an Excel date.
TODAY function Returns the current date. Note, this function is volatile.
WEEKDAY function Returns the weekday of an Excel date.

Excel stores time as a decimal from 0 (zero) to 1. 0 is 12.00 a.m and 0.5 is 12.00 p.m. Each hour is 1/24 or approx. 0.041666667.

Time functions Returns
HOUR function The hour of an Excel defined time value.
MINUTE function The minute of an Excel defined time value.
SECOND function The second of an Excel defined time value.
NOW function Returns the current time. Note, this function is volatile.

Excel also allows you to store date and time combined. For example, 1.5 is 1/1/1900 12.00 p.m and 2.5 is 1/2/1900 12.00 p.m. The following functions lets you create an Excel defined date and time value.

Functions Returns
DATE function An Excel date based on year, month and day.
TIME function Excel time based on hour, minute and second.
DATEVALUE function Excel date based on a text string.
TIMEVALUE function Excel time based on a text string.

These functions perform calculations to Excel defined date and time values.

Functions Returns
DATEDIF function Returns the number of years, month and days between two dates.
EDATE function Returns a date based on a start date and a given number of months.
EOMONTH function The last date in a given month based on a start date and a number that represents the number of months.
NETWORKDAYS function Returns the number of workdays between two dates.
WORKDAY function The date based on a start date and a given number of workdays.
YEARFRAC function The fraction of a year based on a start and end date and then number of days between.

How to use the WEEKNUM function

The WEEKNUM function calculates a given date's week number based on a return_type parameter that determines which day the week […]

How to use the NETWORKDAYS.INTL function

The NETWORKDAYS.INTL function calculate the number of working days between two dates, excluding weekends. It also allows you to ignore […]

How to use the WORKDAY function

The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and […]

How to use the YEARFRAC function

The YEARFRAC function returns the fraction of the year based on the number of whole days between a start date […]

How to use the EOMONTH function

The EOMONTH function returns an Excel date for the last day of a given month using a number and a start […]

How to use the EDATE function

The EDATE function returns a date determined by a start date and a number representing how many months. Use EDATE […]

How to use the DAYS function

The DATE function calculates the number of days between two dates. Formula in cell D3: =DAYS(B3,C3) Excel Function Syntax DAYS(end_date, […]

How to use the NOW function

The NOW function returns the current date and time. Formula in cell B3: =NOW() Excel Function Syntax NOW() Arguments The […]

How to use the TIMEVALUE function

The TIMEVALUE function returns a decimal number based on a text string. Excel uses decimal numbers between 0 and 0.99988426 […]

How to use the TODAY function

The TODAY function returns the Excel date (serial number) of the current date. Warning! This function is volatile meaning it […]

How to use the DATEVALUE function

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. Excel Function Syntax […]

How to use the DATEDIF function

The DATEDIF function returns the number of days, months, or years between two dates. The DATEDIF function exists in order to […]

How to use the MINUTE function

Returns a whole number representing the minute based on an Excel time value. The returned number is ranging from 0 […]

How to use the SECOND function

Returns an integer representing the second based on an Excel time value. The returning number is ranging from 0 to […]

How to use the HOUR function

Returns an integer representing the hour of an Excel time value. The returning number is ranging from 0 (12:00 A.M.) […]

How to use the NETWORKDAYS function

The NETWORKDAYS function returns the number of working days between two dates, excluding weekends. It also allows you to ignore […]

How to use the TIME function

The TIME function returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M. Formula in cell […]

How to use the WEEKDAY function

Converts a date to a weekday number from 1 to 7. Formula in cell D3: =WEEKDAY(B3) Excel Function Syntax WEEKDAY(serial_number,[return_type]) […]

How to use the DAY function

The DAY function extracts the day as a number from an Excel date. Formula in cell C3: =DAY(B3) Excel Function […]

How to use the MONTH function

Extracts the month as a number from an Excel date. Formula in cell C3: =MONTH(B3) Excel Function Syntax MONTH(serial_number) Arguments […]

How to use the YEAR function

The YEAR function converts a date to a number representing the year in the date. The number is between 1900 […]

How to use the DATE function

The DATE function returns a number that acts as a date in the Excel environment. The image above shows you […]