How to calculate absent and weekend days in attendance?


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



Selected Answer

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.


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.



I am using excel 2016. When I use Networkdays formula, I can specify the holiday date. Although, imagine a scenario in which office management has suddenly declared holiday for tomorrow, in that case, it is time consuming to update in formula again.
Kindly suggest formula in which working days get calculated automatically whenever "Holiday" is typed in the last column. i.e if working days were 31 at start of the month with no holidays, now if someone enters holiday for 4th then the cell below should reflect as 30 automatically as user has typed "Holiday" . 

Hope I am clear.
Thanks and Regards,
Akash Sharma   
Akash Sharma (rep: 28) Nov 11, '18 at 6:25 am
Hello Akash,
Create a named range where all the holidays are listed. When the boss declares a holiday tomorrow you add it to the list.
The formula in your workbook refers to the named range. It is never touched. The method you suggest is more complicated than the one designed by Microsoft. It needs a longer formula and more maintenance work. In my opinion it would also be more error prone because holidays would be marked in a column potentially consisting of many thousands of cells whereas in the named range it would be a single cell with an explanation adjacent to it where you can write a name or explanation.
Variatus (rep: 4148) Nov 11, '18 at 6:47 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login