## Dataset Overview

In Microsoft Excel, we often need to determine the age or duration of various items. Additionally, there are cases where we want to exclude weekends from the calculation. If you’re puzzled about how to achieve this with specific criteria, this article will guide you through four suitable methods for calculating the days between certain dates while excluding weekends.

For the demonstration purpose we are going to use the below dataset, In the dataset, we got the fish items listed in the** Item** column and the **Price**, **Quantity**, **Arrival Date,** and **Expiry Date** in their respective columns. We are going to calculate the workdays in between the dates excluding the weekends.

### Method 1 – Combination SUM and WEEKDAYS Functions

**Objective**: Calculate the inventory time for fish items listed in the dataset, considering their Arrival Date and Expiry Date columns.

**Steps**

- Select cell
**G5**and enter the formula for the interval:

`=F5-E5`

- Drag the
**Fill Handle**down to fill cells**G6:G8**with the formula.

- Select cell
**H5**and enter the following formula to calculate the inventory time (excluding weekends):

`=SUM(INT((WEEKDAY(E5-{2,3,4,5,6})+F5-E5)/7))`

- Drag the
**Fill Handle**down to fill cells**H6:H8**with the**inventory time**for each fish item.

**How Does the Formula Work?**

**WEEKDAY(E5-{2,3,4,5,6})**: This function returns the weekday number for the**Arrival**Date, adjusted for Monday to Friday (excluding weekends).**(WEEKDAY(E5-{2,3,4,5,6}) + F5 – E5)**: Adds the total interval days to the weekday values obtained in the previous step.**INT((WEEKDAY(E5-{2,3,4,5,6}) + F5 – E5) / 7)**: Divides the result by**7**to find the number of weeks each weekday contributes. Some values may be decimals, but the**INT**function converts them to integers.**SUM(INT((WEEKDAY(E5-{2,3,4,5,6}) + F5 – E5) / 7))**: Finally, this formula sums up the array of values obtained in the previous step.

### Method 2 – Using the NETWORKDAYS Function

In this method, we’ll utilize the **NETWORKDAYS** function to extract workdays between inventory dates.

**Steps**

- Select cell
**G5**and enter the following formula:

`=F5-E5`

This calculates the number of days between the **Arrival** and **Expiry** dates, including weekends.

- Drag the
**Fill Handle**down to fill cells**G6:G8**with the**interval values**.

- Select cell
**H5**and enter the following formula:

`=NETWORKDAYS(E5,F5)`

This provides the **inventory time** for fish items in the Item column (cell **B5**), excluding weekends.

- Drag the Fill
**Handle down**to fill cells**H6:H8**with the inventory time for each fish item.

**Note:**

The **NETWORKDAYS** function considers weekends as **Saturday** and **Sunday**. If your weekends differ in your region, consider using the **NETWORKDAYS.INTL** function.

### Method 3 – Utilizing NETWORKDAYS.INTL Function

The **NETWORKDAYS.INTL** function extends the **NETWORKDAYS** functionality to account for specific weekend days.

**Steps**

- Select cell
**G5**and enter the following formula:

`=F5-E5`

Calculate the days between **Arrival** and **Expiry** Dates, including weekends.

- Drag the
**Fill Handle**down to fill cells**G6:G8**with the**interval values**.

- Select cell
**H5**and enter the following formula:

`=NETWORKDAYS.INTL(E5,F5,1)`

This provides the** inventory time** for fish in the **Item** column (cell **B5**), excluding weekends.

- Drag the
**Fill Handle**down to fill cells**H6:H8**with the inventory time.

**Note**

The last argument in the formula specifies which days are considered weekends. The default is Saturday and Sunday. Adjust this based on your region.

If you do not mention any number, then the default is set to **Saturday** and **Sunday**. You need to choose your desired one from the list above.

### Method 4 – Combining SUMPRODUCT, WEEKDAY, ROW, and INDIRECT Functions

This method counts workdays (excluding weekends) between inventory times using a combination of functions.

**Steps**

- Select cell
**G5**and enter the following formula:

`=F5-E5`

Calculate the days between **Arrival** and **Expiry** Dates, including weekends.

- Drag the
**Fill Handle**down to fill cells**G6:G8**with the interval values.

- Select cell
**H5**and enter the following formula:

`=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E5&":"&F5)),2)>5))-1`

This counts the **number of weekends** between the **inventory** **times**.

- Drag the
**Fill Handle**down to fill cells**H6:H8**with the weekend count.

- Select cell
**I5**and enter:

`=G5-H5`

This calculates the** inventory time** for fish items, excluding weekends.

- Drag the
**Fill Handle**down to fill cells**I6:I8**with the inventory time.

**How Does the Formula Work?**

**INDIRECT(E5&“:”&F5)**:- The
**INDIRECT**function converts the**Arrival**date and**Expiry**date into a range. - It creates a reference to all the dates between cell
**E5**and**F5**.

- The
**ROW(INDIRECT(E5&“:”&F5))**:- This formula returns the
**row numbers**corresponding to the dates in the specified range. - Essentially, it lists all the dates vertically.

- This formula returns the
**WEEKDAY(ROW(INDIRECT(E5&“:”&F5)), 2)**:- The
**WEEKDAY**function returns the numerical representation of each day of the week. - Here,
**1**represents**Monday**,**2**represents**Tuesday**, and so on, up to 7 (Sunday).

- The
**(WEEKDAY(ROW(INDIRECT(E5&“:”&F5)), 2) > 5)**:- This part of the formula evaluates whether the weekday value is greater than 5 (i.e., Saturday or Sunday).
- If the value is
**greater than 5**, it returns**True**; otherwise, it returns False. - By doing this, we
**filter out weekends**(True values).

**SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(E5&“:”&F5)), 2) > 5)) – 1**:- The
**SUMPRODUCT**function counts the number of True values obtained from the previous step. - The double dash (
**– –**) converts**True**to**1**and**False**to**0**. - Subtracting
**1**accounts for the extra weekend counted.

- The

**Download Practice Workbook**

You can download the practice workbook from here:

**<< Go Back to ****Ageing ****| Formula List | Learn Excel**