Exclude Certain Days from a Formula

[kborgers]

Please see attached. The first tab (Holidays) contains English public holidays through to 2020. The next (Unedited Dates) contain formulas that calculate the workdays of each month in a given year by excluding weekends and public holidays (as defined on the Holidays sheet). Cell C6 is the driver of all calculations on this sheet and begins each business year (in this case) on 1st April, therefore as you will see, the last day of the previous month (always 31st March of course) is required for my formula in D6 to correctly begin at the start of April. The formula is then extended to the entire form to ‘catch every possible work day in every month – different months have different amount of work days. The start of each month after April (column C) is formula based by looking at the last value from the previous month. In order for this to work, I have to manually delete any dates at the end of each month that do not belong in that month – see final tab Required Dates as an example. I need to do this each time I change the start year in C6. Is it possible, perhaps using the EDATE function to only return dates that apply to each month in the month rows, so that manual editing is not required? Many thanks for any advice!

[Kaper]

You can do EMONTH checking or for instance MONTH checking. for instance D6:

Please Login or Register  to view this content.

[Kaper]

PS. The one with EOMONTH could be (leave column D as is and for E6 and copy down and right:

Please Login or Register  to view this content.

By the way - C7 and down shall be corrected to cover all columns to AB, like:

Please Login or Register  to view this content.

[kborgers]

Hi Kaper - Genius! Also, thanks for pointing out the range error in my column C!! Thanks so much - really appreciated!!