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)

SnapShot.png
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
trowaAsked:
Who is Participating?
 
Rob HensonFinance 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
 
trowaAuthor 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)

Open in new window


Is there any better solution?

Thank you.
0
 
Subodh Tiwari (Neeraj)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))

Open in new window

and copy it down.
Costs.xlsx
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance 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
 
trowaAuthor Commented:
Hi Rob,

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

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

Thank you.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Did you test my version?
0
 
Rob HensonFinance AnalystCommented:
Yes it can. I will take a look later.
0
 
trowaAuthor 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
 
Subodh Tiwari (Neeraj)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
 
Rob HensonFinance AnalystCommented:
And an additional bit of Conditional Formatting to highlight the Public Holidays:

=ISNUMBER(MATCH(B4,'Public Holidays'!$B$2:$B$26,0))
1
 
trowaAuthor 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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's correct. The range referred should be C4:G4 in the second part of the formula. Good catch. :)
0
 
trowaAuthor Commented:
Thank you both, well solutions again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.