How to Use Ageing Formula in Excel Excluding Weekends (4 Easy Ways)

In Excel, we often need to calculate the age or duration of different products. We also need to know the time period excluded the weekends from it. 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.


Download Practice Workbook

Download this practice workbook below.


4 Suitable Ways to Use Ageing Formula in Excel 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.

Use Ageing Formula in Excel Excluding Weekends


1. Combination of SUM and WEEKDAYS Functions

We are going to use the combination of SUM, INT, and WEEKDAYS functions in order to extract the age or the inventory time of the fishes in the Item column. Here the WEEKDAYS 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.

Combination of SUM and WEEKDAYS Function to Use Ageing Formula in Excel Excluding 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.

Combination of SUM and WEEKDAYS Function to Use Ageing Formula in Excel 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.

Combination of SUM and WEEKDAYS Function to Use Ageing Formula in Excel Excluding Weekends

How Does the Formula Work?

  • WEEKDAY(E5-{2,3,4,5,6}): This function will return the Arrival dates 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.

Read More: How to Make Inventory Aging Report in Excel (Step by Step Guidelines)


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.

Using NETWORKDAYS Function to Use Ageing Formula in Excel Excluding 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.

Using NETWORKDAYS Function to Use Ageing Formula in Excel 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.

Using NETWORKDAYS Function to Use Ageing Formula in Excel Excluding 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.


Similar Readings


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.

Utilizing NETWORKDAYS.INTL Function to Use Ageing Formula in Excel Excluding 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 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.

Utilizing NETWORKDAYS.INTL Function to Use Ageing Formula in Excel Excluding 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 following below:

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.

Incorporating SUMPRODUCT, WEEKDAY, ROW and INDIRECT Functions to Use Ageing Formula in Excel Excluding 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.

Incorporating SUMPRODUCT, WEEKDAY, ROW and INDIRECT Functions to Use Ageing Formula in Excel Excluding Weekends

  • 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.

Incorporating SUMPRODUCT, WEEKDAY, ROW and INDIRECT Functions to Use Ageing Formula in Excel Excluding Weekends

  • 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.

Incorporating SUMPRODUCT, WEEKDAY, ROW and INDIRECT Functions to Use Ageing Formula in Excel 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.

Incorporating SUMPRODUCT, WEEKDAY, ROW and INDIRECT Functions to Use Ageing Formula in Excel Excluding 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.

Conclusion

To sum it up, the issue “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.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo