Top 8 Date Functions in Excel – A Quick Reference

Date and Time Functions in

Date functions in Excel help you to simplify handling date operations easily. In this article, we’ll learn some of the date and time functions in Excel.

Date Functions in Excel

Let’s get started with the date and time functions offered by excel.

1. DATE function

The DATE function takes three separate values and combines them to represent a date.

Syntax: DATE(year, month, day), where year is a four-digit number corresponding to a calendar year, month is the month number from 1 to 12 (January to December) and day represents the day of the month from 1 to 31. The following example shows the use of DATE function:

  • Select the cell where you want to display the result.
  • Type =DATE(A2,B2,C2), where cells A2, B2, and C2 contain the year, month, and day respectively.
Date and Time Functions
  • Press the Enter key to display the result.
Date and Time Functions

2. DATEDIF function

The DATEDIF function calculates the difference between two dates in terms of the number of complete days, months, and years in the period.

Syntax: DATEDIF(start_date,end_date,unit), where start-date is the first day of the period, end_date is the last day of the period, and unit can be “Y”, “M”, or “D”. “Y” for complete years, “M” for complete months, and “D” for complete days. The following example shows the use of DATEDIF function:

  • Select the cell where you want to display the result.
  • Type =DATEDIF(A2,B2,”M”), where cells A2 and B2 contain the start date and the end date respectively.
Date and Time Functions
  • Press the Enter key to display the result. 265 months have elapsed since 8/12/1999 till 9/23/2021.
Date and Time Functions

3. DATEVALUE function

The DATEVALUE function transforms a text date into a serial number that Excel can recognize as a date. The formula =DATEVALUE(“1/1/2008”), for example, produces 39448, the date’s serial number is stored in the computer system.

Syntax: DATEVALUE(date_text), where date_text is the value whose serial number you want. The following example shows the use of DATEVALUE function:

  • Select the cell where you want to display the result.
  • Type =DATEVALUE(“9/11/2021”), where cell A2 contains the date.
datevalue formula
  • Press the Enter key to display the result.
datevalue result

4. DAY function

The DAY function extracts and returns the day from a date in Excel. The day is specified as an integer between 1 and 31.

Syntax: DAY(date), where the date is whose day you want to find. The following example shows the use of DAY function:

  • Select the cell where you want to display the result.
  • Type =DAY(A2), where cell A2 contains the date.
day formula
  • Press the Enter key to display the result.
daye result

5. DAYS function

In Excel, the DAYS function returns the number of days between the start date and the end date.

Syntax: DAYS(end_date, start_date), where start-date is the first day of the period and end_date is the last day of the period. The following example shows the use of DAYS function:

  • Select the cell where you want to display the result.
  • Type =DAYS(A2,B2), where cells A2 and B2 contain the start date and the end date respectively.
days formula
  • Press the Enter key to display the result.
days result

6. DAYS360 function

The DAYS360 function calculates the number of days between two dates using a 360-day year, which is commonly used in accounting computations. If your accounting system is based on twelve 30-day months, you can use this function to assist you to calculate payments.

Syntax: DAYS360(start_date,end_date), where start-date is the first day of the period and end_date is the last day of the period. The following example shows the use of DAYS360 function:

  • Select the cell where you want to display the result.
  • Type =DAYS(A2,B2), where cells A2 and B2 contain the start date and the end date respectively.
days360 formula
  • Press the Enter key to display the result.
days360 result

7. EDATE function

The EDATE function returns the date that occurs the specified number of months before or after the start date. It is used to calculate maturity dates or due dates that are the same day of the month as the issue date using EDATE.

Syntax: EDATE(start_date, months), where the start date is the first day of the period and months is the time period to be added to the date. The following example shows the use of the EDATE function:

  • Select the cell where you want to display the result.
  • Type =EDATE(A2,20), where cell A2 contains the date and 20 is the number of months to be added.
edate formula
  • Press the Enter key to display the result.
edate result

8. EOMONTH function

EOMONTH function returns the month’s end day, which is the specified number of months before or after the start date. It is used to calculate maturity or due dates on the last day of the month with EOMONTH.

Syntax: EOMONTH(start_date, months), where start-date is the first day of the period and months is the time period to be added to the date. The following example shows the use of the EOMONTH function:

  • Select the cell where you want to display the result.
  • Type =EOMONTH(A2,20), where cell A2 contains the date and 20 is the number of months to be added.
eomonth formula
  • Press the Enter key to display the result.
eomonth result

Conclusion

In this article, we learned some of the date and time functions in Excel.

References