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-E5Entering 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-E5Entering 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-E5Entering 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-E5Entering 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))-1Doing 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-H5Doing 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
Get FREE Advanced Excel Exercises with Solutions!
