In Excel, we often need to calculate the age or duration of different products. We also need to know the time period excluding the weekends. If you are baffled to solve this problem with these specific criteria, this article may come in handy for you. In this article, we are going to show how you can calculate the days in between certain dates excluding weekends using the different ageing formula in Excel.

## How to Use Ageing Formula in Excel Excluding Weekends:Â 4 Suitable Ways

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.

### 1. Combination of SUM and WEEKDAYS Functions

We are going to use the combination of **SUM**, **INT****, **and **WEEKDAY** functions in order to extract the age or the inventory time of the fishes in the Item column. Here the **WEEKDAY **function will extract the number of weeks each single working day gets in between the total **Inventory Time**. The **INT **function will turn the fraction week number from the **WEEKDAY **function. The **SUM** function then will just add the weekdays excluding the weekends.

**Steps**

- In the beginning, select the cell
**G5**and enter the following formula

`=F5-E5`

Entering this formula will calculate the number of days between the **Arrival **and the **Expiry Date **including the weekends.

- Now we drag the
**Fill Handle**to cell**G8**. - Doing this will fill the range of cells
**G5:G8**with the difference of days between the**Arrival**column and**Expiry**column dates.

- After that, select the cell
**H5**and enter the following formula

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

Doing this will get the **Inventory time **of the fishes in the Item column (cell **B5**) excluding weekends.

- Now we drag the
**Fill Handle**to cell**H8**. - Doing this will fill the range of cells
**H5:H8**with the inventory time between the**Arrival**column and**Expiry**column dates, excluding the weekends.

**How Does the Formula Work?**

**WEEKDAY(E5-{2,3,4,5,6}):**This function will return the**Arrival**date weekday number minus the other day’s number in the week.**(WEEKDAY(E5-{2,3,4,5,6})+F5-E5):**In this part, we add the total interval days with the values returned in the previous function.**INT((WEEKDAY(E5-{2,3,4,5,6})+F5-E5)/7):**This formula will divide the value returned in the previous function by 7. They are actually the number of weeks each weekday got. Many of the values could be decimals. The**INT**function will turn the decimals into integer values.**SUM(INT((WEEKDAY(E5-{2,3,4,5,6})+F5-E5)/7))**: Finally, this formula will sum the array of values returned in the previous function.

### 2. Using NETWORKDAYS Function

In this method, we are going to use the **NETWORKDAYS** function to extract only the workdays in between inventory days.

**Steps**

- In the beginning, select the cell
**G5**and enter the following formula

`=F5-E5`

Entering this formula will calculate the number of days between the** Arrival** and the **Expiry date** including the weekends.

- Now we drag the
**Fill Handle**to cell**G8**. - Doing this will fill the range of cells
**G5:G8**with the difference of days between the**Arrival**column and**Expiry**column dates.

- After that, select the cell
**H5**and enter the following formula:

`=NETWORKDAYS(E5,F5)`

Doing this will get the **Inventory time **of the fishes in the Item column (cell **B5**) excluding weekends.

- Now we drag the
**Fill Handle**to cell**H8**. - Doing this will fill the range of cells
**H5:H8**with the inventory time between the**Arrival**column and**Expiry**column dates, excluding the weekends.

**Note**

In this method, the** NETWORKDAYS** Function will consider the weekends like **Saturday **and **Sunday**. And this is fixed. If your weekends are different in your region, then you should choose the **NETWORKDAYS.INTL** function.

### 3. Utilizing NETWORKDAYS.INTL Function

We also can use the **NETWORKDAYS.INTL **function which is an extension of the **NETWORKSDAYS **function to get only the workdays between inventory time.

**Steps**

- In the beginning, select the cell
**G5**and enter the following formula

`=F5-E5`

Entering this formula will calculate the number of days between the **Arrival **and the **Expiry Date **including the weekends.

- Now we drag the
**Fill Handle**to cell**G8**. - Doing this will fill the range of cells
**G5:G8**with the difference of days between the**Arrival**column and**Expiry**column dates.

- After that, select the cell
**H5**and enter the following formula:

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

Doing this will get the **Inventory time **of the fish in the **Item** column (cell **B5**) excluding weekends.

- Now we drag the
**Fill Handle**to cell**H8**. - Doing this will fill the range of cells
**H5:H8**with the inventory time between the**Arrival**column and**Expiry**column dates, excluding the weekends.

**Note**

Here the last argument in the formula denotes which one or 2 days are the weekends in your region that you want to omit from the inventory time. There is a set of arguments that denotes which number denotes which set of weekends. The list is as follows:

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.

### Â 4. Combining SUMPRODUCT, WEEKDAY, ROW, and INDIRECT Functions

We can use the combination of the **SUMPRODUCT**, **WEEKDAY**, **ROW****,** and **INDIRECT** functions to count the **workdays** excluding **weekends** between **Inventory times**. The** ROW** and the **INDIRECT **functions will organize the data for the **WEEKDAY **function. The** SUMPRODUCT** function then final counts the weekdays excluding the weekends

**Steps**

- In the beginning, select the cell
**G5**and enter the following formula

`=F5-E5`

Entering this formula will calculate the number of days between the **Arrival **and the** Expiry Date** including the weekends.

- Now we drag the
**Fill Handle**to cell**G8**. - Doing this will fill the range of cells
**G5:G8**with the difference of days between the**Arrival**column and**Expiry**column dates.

- After that, select the cell
**H5**and enter the following formula:

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

Doing this will get the **Number of Weekends **in between the **Inventory time**.

- Now we drag the
**Fill Handle**to cell**H8**. - Doing this will fill the range of cells
**H5:H8**with the number of weekends between the inventory time mentioned in the**Inventory Time**column in the range of cells**G5:G8.**

- After that, select the cell
**I5**and enter the following formula:

`=G5-H5`

Doing this will get the **Inventory time **of the fishes in the** Item** column excluding weekends.

- Now we drag the
**Fill Handle**to cell**I8**. - Doing this will fill the range of cells
**I5:I8**with the inventory time between the**Arrival**column and**Expiry**column dates, excluding the weekends.

This is how we managed to calculate the workdays in Excel excluding weekends using the ageing formula.

**How Does the Formula Work?**

**INDIRECT(E5&”:”&F5):**In this function, the**Arrival**date and the**Expiry**date are going to be converted into range.**ROW(INDIRECT(E5&”:”&F5)):**This formula will return the row of numbers between the ranges returned in the**INDIRECT**function. In other words, all of the dates mentioned between cell**E5**and the**F5**will be listed vertically**WEEKDAY(ROW(INDIRECT(E5&”:”&F5)),2)**: This formula will then return each of the date’s days’ names in numerical format. Numbers**1**means (Monday) through**7**(Sunday).**(WEEKDAY(ROW(INDIRECT(E5&”:”&F5)),2)>5)):**This formula will return**True**or**False,**whether the value is greater than**5**or not. If it is**>5**(Monday), then the function will return**True**. Otherwise, it will return**False.**In this way, we can filter out the weekends by**True**returns.**SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(E5&”:”&F5)),2)>5))-1:**Finally we can get the count of the**True**returns in the formula. Previously all the weekends are filtered out as**True**. Now we count the**True**values as each of them turned to 1, because of the double dash in front of the**WEEKDAY**So counting the True value means we can get the**weekend**number. We need to subtract**1**because it comes off as extra.

**Download Practice Workbook**

Download this practice workbook below.

## Conclusion

To sum it up, the issue of â€œAgeing formula in Excel excluding weekends” is answered here by 4 different methods. First, using the combination of **SUM,** and** WEEKDAYS** functions. Then using the **NETWORKDAYS **and the** NETWORKDAYS.INTL **functions. Finally, we used the combination of **SUMPRODUCT**, **WEEKDAY**, **ROW,** and **INDIRECT** functions.

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