How to Exclude Weekends in Employee Absence Tracker Count

Hello experts, Please need help. I am creating a employee holiday and absence calendar and how can I exclude specific columns which is weekend with =countif()

I suggest you take a look at the functions in Excel
NETWORKDAYS will exclude the weekends and if you add a range that contains holidays even those will not be counted.

Thanks for updating the title.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

So, for example in AH20 (September sheet):

=SUMPRODUCT((C20:AG20="x")*(WEEKDAY(C6:AG6,1)<6))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Dear to you too, first let's put things right.
The worksheet for September has errors. because you enter a date in two different locations, C6 and C7 you make mistakes, C7 is for 2019 and C6 is for 2018
I corrected this for you and made it a little more efficient.
1. In B5 you enter the date for the the first day of that month.
2. C6 is = to B5
3. C7 is = B5
4. Then conditional formatting for the area $C$8:$AG$45 to highlight the weekend days so that you do not have to do that every time you create a new month

I have not done the formula yet, I am not that great with formulas, but will try and see if I can help.
For now just uploading the correct version (only September) you can do that for the other month

'Dear' is the English translation of 'habibi', which is used often in Arabic countries as a term of endearment (men to men as well as men to women and women to men). It's a bit like saying 'mate' or 'love' in English.

We often see it used here in posts by members from Arabic-speaking countries.

Thank you for the lesson, will keep it in mind, I used to be able to read and write it (1966) but lack of practice well... lost it a bit, but did not know that 'Dear' translated from 'habibi' , learned something new

Okay, I got a working solution for September.
I don't know if I can explian it correctly but here goes.
I used the area C5 AG5 to place a 1 if it's a weekend day, else it's a 0
I also unmerged the cells under TOTAL DAYS and placed the letter for that option in the row below (row 7) and this I placed in the formula's

The formula in row 20

Please Login or Register  to view this content.

Hope that when you look at it, it becomes clear.

About the holidays, I added a worksheet named Holidays.
Fill in the dates for the public holidays and the other Holidays.
These are two dynamic ranges named Public_Holidays and Holidays, you can use these to show these too.
Fill these in and I'll help you once you're done.
August is updated too.

You will have to make minor modifications once you have the public holidays in the sheet

Dear Keeballah, Mainly this Holiday and absence planing is for the year 2019 and beyond and I am just starting to prepare this earlier. So About the holiday is undecided yet my most concern now is how can I change weekend to Fri, Sat? I am trying but cant make it

My mistake, I did not take it into account that the weekends are Friday and Saturday in Islamic countries
in row 5

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Weekdays start with 1 (Sunday) through 7 (Saturday), so weekend days for you are 6 (Friday) and 7 (Saturday)

The same with the Conditional formatting

Corrected for Islamic weekends

I added two Islamic public holidays and as holidays the beginning of Ramadan for 2018

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I am very happy that my solution pleases you.
Excel permits you to do many things, the only thing you really need is time and not be afraid to try things out.
Just post if you have questions.
Happy coding.