Introduction to Excel Date Functions Part 2

Introduction to Excel Date Functions Part 2 Twitter

Welcome to this second blog post in a three-part series introducing how to work with dates and Excel date functions. In the first part of this series, we looked at how Excel stores dates using the date serial numbers, how to enter and format dates and how to use the TODAY, DATE, and DAYS functions. If you haven’t read the first blog post, then I suggest you do first by clicking here.

In this blog post, we’re going to look at some more date functions that enable you to identify the day, month, and year number in a date. We will also look at functions like the  WEEKDAY, WORKDAY and NETWORKDAYS that can be used to calculate dates and the number of days between two dates, taking into account weekends and holidays.

The DAY, MONTH, and YEAR Functions

The DAY, MONTH, and YEAR functions work in much the same way. They use a valid date serial number and return an integer representing either the day, month, or year number.

DAY Function

The DAY function returns the day of a date serial number. The value returned is an integer between 1 and 31.

The syntax of the DAY function is DAY(serial_number).

ParameterDescription
serial_numberThis parameter is a valid date serial number that the day number will be extracted from. It is best to use the value returned from another date function such as the DATE function to ensure you are using a valid date serial number. This parameter is required.

DAY Function Example

This DAY function example returns the day number from the date Thursday, 18 August 2016, using the following formula.

DAY Function Example Formula

In cell A2, we use the DATE function to ensure we have a valid date serial number. Then, in cell B2, we input the formula above.

DAY Function Example Formula Spreadsheet

As you can see, the formula returns the value 18 which is the 18th day of the month.

MONTH Function

The MONTH function returns the month number from a valid date serial number. The value returned is an integer between 1 and 12.

The syntax of the MONTH function is MONTH(serial_number).

ParameterDescription
serial_numberThis parameter is a valid date serial number from which the month number will be extracted. It is best to use the value returned from another date function such as the DATE function to ensure you are using a valid date serial number. This parameter is required.

MONTH Function Example

This MONTH function example returns the month number from the date Thursday, 18 August 2016, using the following formula.

MONTH Function Example Formula

Again, in cell A2, we use the DATE function to ensure we have a valid date serial number. Then, in cell B2, we input the formula above.

MONTH Function Example Formula Spreadsheet

This time, the formula returns the value 8, which is the 8th month of the year.

YEAR Function

The YEAR function returns the year number of a date serial number. The value returned is an integer between 1900 and 9999.

The syntax of the YEAR function is YEAR (serial_number).

ParameterDescription
serial_numberThis parameter is a valid date serial number from which the year number will be extracted. It is best to use the value returned from another date function such as the DATE function to ensure you are using a valid date serial number. This parameter is required.

YEAR Function Example

The YEAR function example will use the same date as the previous examples and will simply return the year number from the date Thursday, 18 August 2016, using the following formula.

YEAR Function Example Formula

Like before, in cell A2, we use the DATE function to ensure we have a valid date serial number. Then, in cell B2, we input the formula above.

YEAR Function Example Formula Spreadsheet

This time, the formula returns the value 2016, which is the year number.

The WEEKDAY Function

The WEEKDAY function returns a value that identifies the day of the week for a given date serial number. The value returned is an integer between (and including) 1 and 7.

The syntax of the WEEKDAY function is WEEKDAY(serial_number, [return_type]).

ParameterDescription
serial_numberThis parameter is the date serial number to be evaluated by the WEEKDAY function. Only valid date serial numbers can be used, so it is best to use a value returned by another date function. This parameter is required.
[return_type]The return_type is a number that identifies the return type of the value returned by the WEEKDAY function. This is an optional parameter. If omitted, the value returned is 1 for Sunday through 7 for Saturday.

The following table describes the return_type values that can be used for the WEEKDAY function return_type parameter.

return_type Description
1 or omitted Numbers 1 (Sunday) through 7 (Saturday)
2 Numbers 1 (Monday) through 7 (Sunday).
3 Numbers 0 (Monday) through 6 (Sunday).
11 Numbers 1 (Monday) through 7 (Sunday).
12 Numbers 1 (Tuesday) through 7 (Monday).
13 Numbers 1 (Wednesday) through 7 (Tuesday).
14 Numbers 1 (Thursday) through 7 (Wednesday).
15 Numbers 1 (Friday) through 7 (Thursday).
16 Numbers 1 (Saturday) through 7 (Friday).
17 Numbers 1 (Sunday) through 7 (Saturday).

If either the return_type or the serial_number parameters are invalid values, then the WEEKDAY function will return a #NUM! error.

WEEKDAY Function Example with No Return Type Parameter

In this first example, we are going to return the weekday value for the date Thursday, 18 August 2016. We are going to use the default Excel weekday values meaning Sunday is day 1; therefore, we won’t use the optional parameter return_type. To do this, we will use the following formula.

WEEKDAY Function Example Formula

Like before, in cell A2, we use the DATE function to ensure we have a valid date serial number. Then, in cell B2, we input the formula above.

WEEKDAY Function Example Formula Spreadsheet

The formula returns the value 5, which is correct as Thursday is the fifth day of the week when Sunday is day 1.

WEEKDAY Function Example With a Return Type Parameter

In the next example, we are going to use the same date, but this time we will input the number 11 into the return_type parameter. Using a return_type 11 alters the first day of the week to Monday, see the previous table of values for the return_type parameter. To do this, we use a modified version of the previous formula as follows.

WEEKDAY Function Example 2 Formula

Again, in cell A2, we use the DATE function to ensure we have a valid date serial number. Then, in cell B2, we input the formula above.

WEEKDAY Function Example 2 Formula Spreadsheet

This time, the formula returns the value 4. Because we are using a return_type 11, the first day of the week has been altered to Monday, meaning Thursday is the fourth day of the week.

The WORKDAY Function

The WORKDAY function calculates an end date to a given start date based on the number of workdays, and it excludes weekends and, if required, holidays. So if you had a start date of 20 August 2016, and you are given 40 working days to complete a task, the WORKDAY function can calculate and return the end date.

The syntax of the WORKDAY function is WORKDAY(start_date, days, [holidays]).

ParameterDescription
start_dateA valid date serial number that represents the start date. This parameter is required.
daysA positive or negative number of workdays to calculate the end date. If you use a negative number, the WORKDAYS function will return a date in the past. If the number entered is not an integer, then any values entered after the decimal point will be truncated.
[holidays]This parameter can take either a reference to a range of cells or an array of valid date serial numbers that will be treated as non-working days when calculating an end date.

If invalid date serial numbers are used, the WEEKDAY function will return a #VALUE! error.

WORKDAY Function Example with No Holidays Parameter

In this WORKDAY function example, we are going to calculate what the date will be in 20 working days’ time from the start_date Thursday, 18 August 2016. We’re not going to provide any values for the optional holidays parameter; therefore, the WORKDAY function assumes that only Saturday and Sunday are non-working days. We will use the following formula.

WORKDAY Function Example Formula

In cell A2, we use the DATE function to ensure we have a valid date serial number. Then, in cell B2, we have the value 20 which is the number of working days; then, in cell C2, we input the formula above.

WORKDAY Function Example Formula Spreadsheet

The formula returns the date serial number for the date Thursday, 15 September 2016. This is correct because Thursday, 15 September 2016 is 20 working days after the start_date Thursday, 18 August 2016 if only Monday through Friday are included as working days, and there are no holidays.

WORKDAY Function Example with a Reference to a Range of Cells for the Holidays Parameter

In this second WORKDAY function example, we calculate what the date will be in 20 working days’ time from the date Thursday, 18 August 2016, but this time, we are going to add in a couple of holidays as non-working days as well as Saturday and Sunday. We will use the following formula.

WORKDAY Function Example 2 Formula

In cell A2, we use the DATE function to ensure we have a valid date serial number; in cell B2, we have the value 20 to represent the number of working days, and in cell C2, we input the formula above. However, this time in cells B5 and B6 we add the dates Monday, 29 August 2016 and Tuesday, 13 September 2016. Notice that the formula above references the range of cells B5:B6 in the optional holidays parameter of WORKDAY function.

WORKDAY Function Example 2 Formula Spreadsheet

The formula returns the date serial for the date Monday, 19 September 2016. This is correct because Monday, 19 September 2016 is 20 working days after the start_date Thursday, 18 August 2016 assuming Monday, 29 August 2016, Tuesday, 13 September 2016, and every Saturday and Sunday are non-working days

The NETWORKDAYS Function

The NETWORKDAYS functions is more or less the opposite of the WORKDAY function. You give the function a start date and end date, and it will return you the number of working days between the start date and the end date excluding weekends and, if required, holidays.

The syntax of the NETWORKDAYS function is NETWORKDAYS(start_date, end_date, [holidays]).

ParameterDescription
start_dateA valid date serial number that represents the start date. This parameter is required.
end_dateA valid date serial number that represents the end date. This parameter is required.
[holidays]This parameter can take either a reference to a range of cells or an array of valid date serial_numbers that will be treated as non-working days when calculating the number of days.

If any value is not a valid date serial number, the NETWORKDAYS function will return a #VALUE! error.

NETWORKDAYS Function Example with No Holidays Parameter

In this first NETWORKDAYS function example, we will calculate the number of working days between Thursday, 18 August 2016 and Thursday, 22 September 2016. We will not provide a value for the optional holidays parameter; therefore, the function will base its calculation on Saturday and Sunday being the only non-working days.

NETWORKDAYS Function Example Formula

 

In cells A2 and B2, we use the DATE function to ensure we have a valid date serial number for the start_date and end_date parameters. Then, in cell C2, we input the formula above.

NETWORKDAYS Function Example Formula Spreadsheet

The formula returns the value 26 because there are 26 workdays (Mondays, Tuesdays, Wednesdays, Thursdays, and Fridays) between the start_date and end_date parameters.

NETWORKDAYS Function Example with a Reference to a Range of Cells for the Holidays Parameter

In the second NETWORKDAYS function example, we will again calculate the number of working days between Thursday, 18 August 2016 and Thursday, 22 September 2016. This time, we will provide a reference to a range of cells for the optional holidays parameter, which adds a couple of dates as non-working days as well Saturday and Sunday. To do this, we will use the following formula.

NETWORKDAYS Function Example 2 Formula

Again, in cell A2 and B2, we use the DATE function to ensure we have a valid date serial number for the start_date and end_date parameters. Then, in cell C2, we input the formula above. However, this time in cells B5 and B6, we add the dates Monday, 29 August 2016 and Tuesday, 13 September 2016, these will be our holidays.

NETWORKDAYS Function Example 2 Formula Spreadsheet

This time formula returns the value 24. This is because we have 24 workdays (Mondays, Tuesdays, Wednesdays, Thursdays, and Fridays) between the start_date and end_date parameters with the exception of Monday, 29 August 2016 and Tuesday, 13 September 2016 which are holidays.

NETWORKDAYS.INTL

The NETWORKDAYS.INTL function works in much the same way as the NETWORKDAYS function except it allows you to specify which days are the weekend dates. You give the function a start date and end date, and it will return you the number of working days between the start date and the end date excluding weekends and, if required, holidays.

The syntax of the NETWORKDAYS.INTL function is NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]).

ParameterDescription
start_dateA valid date serial number that represents the start date. This parameter is required.
end_dateA valid date serial number that represents the end date. This parameter is required.
[weekend]This parameter can either be a number that is used to identify a weekend date format of days that are considered weekends (non-working days). This parameter can also take a string of seven ones (1) and zeros (0), which represents the days of the week starting from Monday. The character 1 represents a non-workday, and 0 represents a workday. For example, 1001000 means Monday and Thursday are non-working days.
[holidays]This parameter can take either a reference to a range of cells or an array of valid date serial numbers that will be treated as non-working days when calculating the number of days.

The following table describes the different weekend parameter values that can be used in the NETWORKDAYS.INTL function weekend parameter.

weekend parameter valueDescription
1 or omitted Saturday and Sunday
2 Sunday and Monday
3 Monday and Tuesday
4 Tuesday and Wednesday
5 Wednesday and Thursday
6 Thursday and Friday
7 Friday and Saturday
11 Sunday
12 Monday
13 Tuesday
14 Wednesday
15 Thursday
16 Friday
17 Saturday

NETWORKDAYS.INTL Example with a Weekend Parameter Number

For our first NETWORKDAYS.INTL example, we will use the value 11 for the weekend parameter of the NETWORKDAYS.INTL function, meaning that there are 6 working days in a week, with only Sunday being classed as a non-working day. To do this, we will use the following formula.

NETWORKDAYS.INTL Function Example Formula

Like the NETWORKDAYS function in cells A2 and B2, we use the DATE function to ensure we have a valid date serial number for the start_date and end_date parameters. Then, in cell C2, we input the formula above.

NETWORKDAYS.INTL Function Example Formula Spreadsheet

 

The formula returns the value 31. This is because we have 31 workdays (Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, and Saturday) between the start_date and end_date parameters.

NETWORKDAYS.INTL Example with a String of 0’s and 1’s for the Weekend Parameter

For our final example in this blog, we are going to use a string of 0‘s and 1‘s to represent working and non-working days. More precisely, we will use the string “1110000,” which means that Monday, Tuesday, and Wednesday are non-working days and Thursday, Friday, Saturday, and Sunday are working days. To do this, we will use the following formula.

NETWORKDAYS.INTL Function Example 2 Formula

Just like the previous example, in cells A2 and B2, we use the DATE function to ensure we have a valid date serial number for the start_date and end_date parameters. Then, in cell C2, we input the formula above.

NETWORKDAYS.INTL Function Example 2 Formula Spreadsheet

The formula returns the value 21. This is because we only have 4 workdays a week—Thursdays, Fridays, Saturday, and Sunday—and 21 working days in total between the start_date and end_date parameter dates.

Wrap-up

Okay, that’s it for this blog post. As you can see, there are several functions to help you calculate an end date based on various combinations of start date, holiday(s), and working and non-working days. This can be useful when you have to calculate employees’ pay, employees’ holidays, and end days for activities that are based on work day availability. These functions also give you the flexibility to work with different work day calendars that might be used in different states and countries.

In the next blog post, the final in this series, we will look at some more functions such as the DATEDIF, YEARFRAC, and WEEKNUM functions.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post and receive a copy of our newsletter, sign up to our email list below.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit