# Autopopulate a calendar? - part 2

I'm trying to enhance a calendar developed by some experts.

The calendar is working fine to populate the dates of a calendar month (based on value of A1)

But I need 2 enhancements (second one is optional):

1. I got a list of days in tab: Public Holidays. What I wish is to minus the working days calculated at column J.

For example, currently cell: J8 is calculated = 5, but since 25 Dec is a public holiday, can this value be deducted to 4?

If this even possible, can we apply a Conditional Formatting to mark that cell with black background color?

2. As you can see the date "3" is selected with green background color, but since I had selected A1 = Dec 17 and it's actually not today (3 Nov 17)'s date, can the Conditional Formatting only applied when it's actual day (today), not based on month day?

Attached is what I currently have.

Thank you.
Costs.xlsx
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
For issue #1, I'm using this formula at cell: J4

=NETWORKDAYS(IF(C4<\$A\$1, \$A\$1,C4),IF(G4> DATE(YEAR(\$A\$1),MONTH(\$A\$1)+1,0), DATE(YEAR(\$A\$1),MONTH(\$A\$1)+1,0),G4),'Public Holidays'!B2:B90)

Is there any better solution?

Thank you.
0
Excel & VBA ExpertCommented:
You may try...
In J4
=SUMPRODUCT(1*(MONTH(C4:G4)=MONTH(\$A\$1)))-SUMPRODUCT(--(B4:H4='Public Holidays'!\$B\$2:\$B\$26))
and copy it down.
Costs.xlsx
1
Finance AnalystCommented:
See attached.

For working days I have used:

=NETWORKDAYS(MAX(MIN(\$B4:\$H4),\$A\$1),\$H4,'Public Holidays'!\$B\$2:\$B\$26)

For Conditional formatting use this formula as a rule:

=B4=TODAY()

The uploaded file is from your original question so has my suggested formulas for the dates, I believe it works with the other formulas as well.
calendar.xlsx
1
Author Commented:
Hi Rob,

Your formula almost works, but got problem when there are next month's days on last week's weekdays.

Extra day(s) was calculated. Can the formula be refined?

Thank you.
0
Excel & VBA ExpertCommented:
Did you test my version?
0
Finance AnalystCommented:
Yes it can. I will take a look later.
0
Author Commented:
@Rob

Thank you!

@Subodh

Yup, your solution works, but I don't quite understand your formula. Do you think you can explain a bit how you do that?

Thank you!
0
Finance AnalystCommented:
Updated:

=NETWORKDAYS(MAX(MIN(\$B8:\$H8),\$A\$1),MIN(MAX(\$B8:\$H8),EOMONTH(\$A\$1,0)),'Public Holidays'!\$B\$2:\$B\$26)

How it works:

=NETWORKDAYS(Start Date, Finish Date, Holidays)

Start Date - MAX(MIN(\$B8:\$H8),\$A\$1)
MIN takes the lowest date from the row, MAX compares this with the date in A1 and takes the highest; allows for last days of previous month

Finish Date - MIN(MAX(B8:H8),EOMONTH(\$A\$1,0)
MAX takes the highest date from the row, EOMONTH calculates the last day of the month in A1, MIN takes the lowest of the two; allows for first few days for following month.

Holidays - the list of dates to allow as holiday.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Excel & VBA ExpertCommented:
Wow, you didn't address the solution which worked for you. Strange.

I just tweaked the existing formula you already had as I didn't want to change it, I thought you understand what the existing formula is doing.

=SUMPRODUCT(1*(MONTH(C4:G4)=MONTH(\$A\$1)))-SUMPRODUCT(--(B4:H4='Public Holidays'!\$B\$2:\$B\$26))
This part SUMPRODUCT(1*(MONTH(C4:G4)=MONTH(\$A\$1))) returns the count of days where the month is equal to the month of the date entered in A1.

And this part SUMPRODUCT(--(B4:H4='Public Holidays'!\$B\$2:\$B\$26)) returns the number of dates found on Public Holidays sheet and at the end you can subtract it from the first part of the formula.
0
Finance AnalystCommented:
And an additional bit of Conditional Formatting to highlight the Public Holidays:

=ISNUMBER(MATCH(B4,'Public Holidays'!\$B\$2:\$B\$26,0))
1
Author Commented:
Hi Subodh,

And this part SUMPRODUCT(--(B4:H4='Public Holidays'!\$B\$2:\$B\$26)) returns the number of dates found on Public Holidays sheet and at the end you can subtract it from the first part of the formula.

Shouldn't this part be as SUMPRODUCT(--(C4:G4='Public Holidays'!\$B\$2:\$B\$26)) instead? Since weekends are not calculated.

I try to learn, that's why need some explanations.

Thank you.
0
Excel & VBA ExpertCommented:
That's correct. The range referred should be C4:G4 in the second part of the formula. Good catch. :)
0
Author Commented:
Thank you both, well solutions again!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.