9 Excel DATE Functions You Should Know

Excel is more than a tool for basic calculations and data organization; it's also an effective tool for handling and analyzing date-related calculations. You can use Excel's date functions to calculate durations, find the difference between dates, determine working days, and even forecast future dates.

So, we'll explore various date functions that you should be familiar with and provide examples of how you can use them effectively in Excel.

1. DATE

The DATE function in Excel is used to create a date based on the individual year, month, and day components that you specify. It takes three arguments: year, month, and day.

=DATE(year, month, day)

For example, the following formula would return the date August 14, 2023:

=DATE(2023, 8, 14)

2. EDATE

The EDATE function calculates a date that is a specified number of months before or after a given start date. It is commonly used for financial calculations, such as forecasting future dates based on a given timeframe. The syntax for the EDATE function is:

=EDATE(start_date, months)

Where:

• start_date is the starting date from which you want to calculate the new date
• months is the number of months you want to add (if positive) or subtract (if negative) from the start date.

For example, to find the date that is 3 months after the date in cell A1, you'll use the formula:

=EDATE(A1, -3)

This formula will return the date of May 14, 2023.

3. DAY

The DAY function extracts the day of the month from a given date. It returns the day as a number between 1 and 31. The syntax of the DAY function is:

=DAY(serial_number)

Where serial_number is the date from which you want to extract the day. For example, if you have a date in cell A1 and want to extract the day from it, you can use the formula:

=DAY(A1)

This formula will return the value 14, which represents the day of the month.

4. TODAY

The TODAY function in Excel is used to display the current date. It doesn't take any arguments, and it updates automatically each day. The syntax of the TODAY function is simple:

=TODAY()

5. DAYS360

The DAYS360 function allows you to calculate between two dates in Excel. It returns the number of days between two dates using the 360-day year method, also known as the European method. Here's the syntax for the DAYS360 function:

=DAYS360(start_date, end_date, [method])

Where:

• [method] is an optional argument that specifies the method to use for calculating the difference. If omitted, Excel uses the U.S. (NASD) method.

For example, if you use the following formula:

=DAYS360("2023-08-14", "2023-09-08", TRUE) 

It will return the number of days between August 14, 2023, and September 8, 2023.

6. WEEKNUM

The WEEKNUM function is used to determine the week number of a given date. It takes a date as input and returns the corresponding week number based on the specified system. The syntax of the function is:

=WEEKNUM(serial_number, [return_type]) 

Where:

• serial_number is the date for which you want to determine the week number.
• return_type is an optional argument that specifies the week numbering system to use. If omitted, it defaults to the U.S. system, where weeks start on Sunday.

For example, to calculate the week number of the date in cell A2, you will use the following formula:

=WEEKNUM(A2)

This formula will return the week number for the specified date using the default system.

7. EOMONTH

The EOMONTH function in Excel returns the last day of the month that is a specified number of months before or after a given date. The syntax of the EOMONTH function is:

=EOMONTH(start_date, months)

Where start_date is the initial date from which you want to calculate the end of the month, and months is the number of months to add or subtract from the start date.

For example, if you want to find the last day of the month that is 3 months after the date in cell A1, you will use the formula below:

=EOMONTH(A1, 3)

Similarly, to find the last day of the month that is 2 months before the date in cell A1, you will use:

=EOMONTH(A1, -2)

8. WORKDAY

The WORKDAY function calculates a date that is a specified number of work days before or after a given date, excluding weekends and optionally, specified holidays. Here's the syntax for the WORKDAY function:

=WORKDAY(start_date, days, [holidays])

Where:

• start_date: This is the initial date from which you want to calculate the new date.
• days: This is the number of workdays you want to add (if positive) or subtract (if negative) from the start date.
• [holidays]: This is a range of holidays that you want to exclude from the calculation. This parameter is optional.

Suppose you have a project starting on August 14, 2023, and you want to calculate the completion date that is 15 work days away, excluding weekends and specified holidays. If you have the start date in cell A2 and a list of holiday dates in cells B2:B4, you will use the formula below:

=WORKDAY(A2, 15, B2:B4)

The result will automatically update if you change the start date or the number of workdays.

9. NETWORKDAYS

The NETWORKDAYS function in Excel is used to calculate the number of workdays between two dates, excluding weekends and specified holidays. The syntax for the NETWORKDAYS function is:

=NETWORKDAYS(start_date, end_date, [holidays])

Where start_date is the start date of the period, end_date is the end date of the period, and [holidays] is a range of holiday dates that you want to exclude from the calculation.

For example, you have the start date for a task in cell A2, the end date in B2, and holidays in cells C2:C3:

=NETWORKDAYS(A2, B2, C2:C3)

This formula will calculate and return the number of workdays between the dates in cells A1 and B1, excluding weekends and the dates listed in cells C2 to C3.

Excel's Date Functions Explained

Excel provides a wide range of date functions, such as TODAY, NETWORKDAYS, and many more, to help you calculate time differences and manipulate date formats for better presentation. By mastering these functions, you'll be better equipped to handle date-related data and make informed decisions in your personal or professional projects.