Exclude Holidays in Total

**sickreto** · 03-10-2024, 06:24 PM

Hi,

I can total all columns by simply adding SUM formula, but what I don't know is, if a date in a column falls on a holiday then it should not be counted as part of the total taken.

I can subtract the column if it falls on a holiday, but not all holidays are on the same column, also every year the holidays falls on a different day/date from Jan - Dec.

This is what I'm trying to achieve:

A holiday is counted if given or filled, but doesn't need to be taken away from the entitlement. So it will only count the number of week days, weekends, and total taken. The holidays is counted on its own.

I have attached a sample of what I was trying to achieve.

Thank you very much in advance.

**AliGW** · 03-11-2024, 02:54 AM

Jan AO4 - just change the formula to this:

=SUM(D5:AH5)-AK3

You ought to have asked this in the original thread: https://www.excelforum.com/excel-gen...-weekends.html

**bebo021999** · 03-11-2024, 05:06 AM

@sikreto
I guess your requirement isn't as straightforward as the formula in #2.
Is it like this:
AK2:AN2 shouldn't actually appear in the worksheet; they are just intermediate steps. And your goal is AO3 and AP3, calculated directly without intermediaries, right?

**sickreto** · 03-11-2024, 05:27 AM

Hi AliGW,

Thanks for your response. The other post I have was about a different topic and I have just used the same sample workbook, I hope this is ok.

@bebo021999, I need AO3 and AP3 only.

AK3 will only be counted totally separate and does not require to be taken away from AO3 and AP3.

I feel like having a MATCH formula may work not to include the dates in AO3 and AP3 if filled but I don't know where to fit it in.

Appreciate all your help.

Thank you.

**AliGW** · 03-11-2024, 05:28 AM

But it is not the case of changing the column to subtract, as my template has a format of days of the month where a year may change then the holidays will fall on a different column.

Can't see from the sample workbook how that would make any difference at all.

**sickreto** · 03-11-2024, 06:30 AM

Hi,

Thanks for looking into this. But I think I got it sorted.
Apologies for the confusion. I was confused myself

.
As always, thank you very much for a very kind support.

**AliGW** · 03-11-2024, 06:31 AM

Glad to have helped.

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

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**sickreto** · 03-11-2024, 04:27 PM

Hey guys,

Sorry I'm re-opening this thread.

I think I know now where I got confused earlier :-)

Anyway, I will give it a try again to explain what I'm trying to achieve.

So, I need to calculate the total of Weekdays and Weekend and should give me a total of what was taken and will show me the total balance.

But here is the challenge, the holidays may fall on any day, and holiday may fall on a weekend, if you can see, I count the number of weekends too, so if the holiday falls on a weekend I can't take it away. The holiday should be counted if filled for the year, but if it falls on a weekend, my formula counts it as a weekend and a holiday count.

I hope I have explained it well. If not please let me know and I will try to explain it with a better scenario.

Thank you in advance.

**DJunqueira** · 03-11-2024, 05:35 PM

A formula to not count holiday if it falls on weekend.

Formula:

Please Login or Register  to view this content.

**sickreto** · 03-11-2024, 05:46 PM

Hi DJunqueira,

Thank you for checking on this.

But I think I didn't explain it well. Apologies for the confusion.

What I'm trying to achieve is:

On January sheet, the Holiday falls on a weekend, therefore Holiday Count column should have a count if Row 6 is filled. Then weekdays counts the weekdays, but if holiday is on a weekday then it doesn't count as part of the weekdays but it is counted for the Holiday count. Then Weekend Count column should only count if Row 6 is filled but if the Holiday is on the weekend then it should be added on the Holiday Count column only and not added in the Weekend count column only if it is a holiday.

I hope this explains well.

Thank you.

**DJunqueira** · 03-11-2024, 06:04 PM

It is much better if you show the expected result in the sheet.
By the way with the pointed results of 'Total Taken' and 'Balance' the formula bellow works fine.

AK5

Formula:

Please Login or Register  to view this content.

AO5

Formula:

Please Login or Register  to view this content.

**sickreto** · 03-11-2024, 06:23 PM

Hi,

Please see sample of what I was trying to achieve.

Thanks in advance.

**bebo021999** · 03-11-2024, 09:34 PM

My formula is in row 4, in green cells
Holiday count:

Please Login or Register  to view this content.

Weekday count

Please Login or Register  to view this content.

Weekend count:
=Weekend count - Holiday falls in Weekend

Please Login or Register  to view this content.

Hope it works

**sickreto** · 03-12-2024, 04:49 AM

Hi bebo021999,

Thank you very much. Your formula works, but it only works if the holiday falls on a weekend.

On the same formula, I would also wanted to count it if the holidays falls on a weekdays. When I changed the date, and the holiday falls on a weekday it is then counted as part of the weekday but I need to separate it.

Any chance on giving a light on that please?

Thank you.

**bebo021999** · 03-12-2024, 05:43 AM

Yes, add more SUMPRODUCT for weekday

Please Login or Register  to view this content.

**sickreto** · 03-12-2024, 01:16 PM

Hi,

I tried the formula and it's giving me a negative answer if the holiday falls on a weekday. And if I add your last formula, it's giving me zero.

I'm not sure now what part of the formula needs adjustment.

Thanks.

**sickreto** · 03-12-2024, 06:16 PM

Hi,

I'm attaching another sample explaining and showing what I'm trying to achieve.

Any help is greatly appreciated.

Thank you.

**bebo021999** · 03-13-2024, 01:04 AM

Try again:
Weekday

Please Login or Register  to view this content.

Weekend

Please Login or Register  to view this content.

**sickreto** · 03-13-2024, 03:29 PM

Thank you very much bebo021999.

Your last formula works and that is what exactly I'm looking for.

I do appreciate the effort and knowledge you are sharing.

**sickreto** · 03-15-2024, 08:45 AM

Hi,

Apologies again for re-opening this thread as I come across with a new error.

The weekend seems to identify the New Year if falls on a Weekend, but When I changed the date and looked into December, the Christmas Holiday that falls on Weekend (01/12/202 Sunday) was subtracting from the weekend. So then I'm getting a negative 1 instead of zero result.

My goal here is any holiday that falls on a weekend should only be calculated under the Holiday column, if it falls on a weekend then it should not be calculated as part of weekend, if the rest of the month has the weekend filled, then it should only count the weekends excluding holiday.

From the attached file sample I have above, The January 1st that falls on a weekend seems to work but not with other holidays like Christmas that falls on a weekend.

Any light on this is greatly appreciated.

Thank you in advance.

**sickreto** · 03-16-2024, 04:55 AM

Hi,

Any suggestion please?

I just don't know the formula to make it work.

I appreciate your help in advance.

Thank you..

**JeteMc** · 03-19-2024, 03:18 PM

Perhaps changing the weekend formula in cell AM4 (Jan sheet) as follows will help:

Formula:

Please Login or Register  to view this content.

If not, please upload a sample file illustrating the issue and providing the expected result.
Let us know if you have any questions.

**sickreto** · 03-20-2024, 07:50 AM

Hi,

Thanks for the suggestion. I think I got where the problem is coming from. All of the solutions above works anyway.

Thank you.

**JeteMc** · 03-20-2024, 10:35 AM

Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Re: Exclude Holidays in Total

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Exclude Holidays from two different times

[SOLVED] Exclude holidays from list but NOT weekends

[SOLVED] Use networkdays INCLUDE weekends, Exclude holidays

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions