Autopopulate a calendar? Fix the duplicate issue

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

This is what I have so far. The Excel generates calendars based on value selected in A1.

SnapShot.png
But I got one issue, pls check formula of J18, the formula returns 3 for total working days, which exclude the Public Holidays and Off Days.

My problem comes in if I accidentally entered 25 Dec 17 into the worksheet: Off Days. Then the formula in J18 returns as 2, which is incorrect (because 25 Dec 17 was double-counted).

Can anyone advise what need to adjust?

Thank you.
Costs.xlsx
trowaAsked:
Who is Participating?
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.

Rob HensonFinance AnalystCommented:
I would suggest you put data validation in place on the list of Off Days so that dates cannot be duplicated.

See attached. I have converted the Date lists to tables so that any references to them will adjust. I have added Data Validation to the Off Days list so that it checks if the date already exists in the Public Holidays and there is also a formula in column B advising.

This is two options rather than using both. With the Data Validation it shouldn't be possible to enter an invalid date so the formula shouldn't ever flag up. The one way that I know of to get around Data Validation is to enter the value elsewhere and then copy and paste into the cell.

I see you're not using the formula I provided for the count of working days, although you accepted my solution and actually said you did not understand the SUMPRODUCT formula provided by another EE member.

I see the SUMPRODUCT formula now includes the Off Days in the Working Days calculation. It is possible to include two lists of dates with NETWORKDAYS function but they have to be next to each other so that the Holidays range in the calculation can include them all in one range.

Thanks
Costs.xlsx
1
trowaAuthor Commented:
Hi Rob,

I would suggest you put data validation in place on the list of Off Days so that dates cannot be duplicated.
That's a good idea!

I see you're not using the formula I provided for the count of working days, although you accepted my solution and actually said you did not understand the SUMPRODUCT formula provided by another EE member
It's because I thought NETWORKDAYS can only refer to one list (either Public Holidays or Off Days, but not both)?

It is possible to include two lists of dates with NETWORKDAYS function but they have to be next to each other so that the Holidays range in the calculation can include them all in one range

Is that mean the Public Holidays and Off Days need to be converted into one table? If yes, how will it looks like?

Thank you.
0
trowaAuthor Commented:
Hi Rob,

Wondering what do you mean by below before closing this question

It is possible to include two lists of dates with NETWORKDAYS function but they have to be next to each other so that the Holidays range in the calculation can include them all in one range

Thank you.
0
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.

The lists of holidays are all in one table. The data validation is still in place on the off days so that duplicate dates can't be entered. Press F2 on the repeated date at the bottom of Off Days list (cell C13) and try to re-enter.

The NETWORKDAYS calculation on the calendar sheet also refers to the Table so addition of extra dates will automatically get included.
Costs.xlsx
0

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.

Start your 7-day free trial
trowaAuthor Commented:
I kinda get what you mean now.

The trick is on Holidays[[Dates]:[Off Days] which refers to 2 columns in Holidays range?
0
Rob HensonFinance AnalystCommented:
Yes that refers to 2 columns; it could be more than 2 if required but they have to be next to each other. The colon between the field names treats the columns as a range, much the same as A:E would be columns A to E.
0
trowaAuthor Commented:
Thank you for clearing my doubts!
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.

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.