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).**

Parameter | Description |
---|---|

serial_number | This 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.

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.

As you can see, the formula returns the value 18 which is the 18^{th} 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).**

Parameter | Description |
---|---|

serial_number | This 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.

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.

This time, the formula returns the value 8, which is the 8^{th} 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).**

Parameter | Description |
---|---|

serial_number | This 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.

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.

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]).**

Parameter | Description |
---|---|

serial_number | This 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.

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.

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.

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.

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]).**

Parameter | Description |
---|---|

start_date | A valid date serial number that represents the start date. This parameter is required. |

days | A 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.

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.

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.

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.

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]).**

Parameter | Description |
---|---|

start_date | A valid date serial number that represents the start date. This parameter is required. |

end_date | A 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.

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.

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.

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.

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]).**

Parameter | Description |
---|---|

start_date | A valid date serial number that represents the start date. This parameter is required. |

end_date | A 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 value | Description |
---|---|

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.

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.

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.

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.

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.