trowa
asked on
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.
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
This is what I have so far. The Excel generates calendars based on value selected in A1.
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
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Rob,
Wondering what do you mean by below before closing this question
Thank you.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I kinda get what you mean now.
The trick is on Holidays[[Dates]:[Off Days] which refers to 2 columns in Holidays range?
The trick is on Holidays[[Dates]:[Off Days] which refers to 2 columns in Holidays range?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for clearing my doubts!
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
That's a good idea!
It's because I thought NETWORKDAYS can only refer to one list (either Public Holidays or Off Days, but not both)?
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.