Autopopulate a calendar? Fix the duplicate issue

trowa
trowa used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst
Commented:
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

Author

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.

Author

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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Finance Analyst
Commented:
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

Author

Commented:
I kinda get what you mean now.

The trick is on Holidays[[Dates]:[Off Days] which refers to 2 columns in Holidays range?
Rob HensonFinance Analyst
Commented:
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.

Author

Commented:
Thank you for clearing my doubts!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial