200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Dear Experts,
Please find enclosed file, I have used formula of "Network days Intl" but unable to calculcate total number of working days and working days from 1st Oct to 31st Oct.
Kindly guide the formula to be used for automatically calculating:
1. Days worked
2. Total no. of absent days
3. Total no. of weekends
4. Total no. of holidays
Thanks and Regards,
Akash Sharma
Hello Akash,
Please ask one question per thread. I shall try to answer your first question only in this one.
It appears that you use Excel 2003. The function NETWORKDAYS.INTL() requires Excel 2007 or higher. This function allows you to specify the weekend. Read more about this adjustment here. You might also look at this link.
However, in your formula you specify the weekend as Sat/Sun. For that you don't need the INTL version. NETWORKDAYS() can do that because Sat/Sun is also the default..
In order to exclude holidays from the count of net workdays you need a list of the holiday dates, usually a named range somewhere in your workbook. Just as an example, write the date of 31 Oct 2018 in K44 of your worksheet (any valid date format). Then modify the formula in C44 as follows.
=NETWORKDAYS(C11,C41,K44)The result will change to 22 days. The list in K44 could be extended to K20:K50, for example, meaning any number of dates, and would normally be a named range on a dedicated sheet bcause you need to update it regularly, usually once a year. The international version of the same formula would be =NETWORKDAYS.INTL(C11,C41,1,K44) where the 1 indicates Sat/Sun as the weekend.