Spreadsheet: 861287d1709206964-adding-days-to-a-start-date-including-weekends-and-holidays-end-date-only-on-workday-workday-on-steroids1
Sheet: Sheet1

Ignore dates below completely (don't count/don't land on them) | Ignore dates below completely (don't count/don't land on them) | Ignore August completely (don't count/don't land on a date in August) | holidays to be included in counting but then if the result lands on them go to next working
2024-07-02 00:00:00 | 2024-07-17 00:00:00 | 2024-08-01 00:00:00 | 2024-07-23 00:00:00 | 2024-07-01 00:00:00
2024-07-03 00:00:00 | 2024-07-18 00:00:00 | 2024-08-02 00:00:00 | 2024-07-29 00:00:00 | 2024-07-02 00:00:00 |  | start date | add days
2024-07-04 00:00:00 | 2024-07-19 00:00:00 | 2024-08-03 00:00:00 |  | 2024-07-03 00:00:00 |  | 2024-07-01 00:00:00 | 22 |  |  |  | need this to be 31/7/2024 (ie not count blue at all! count weekends, count orange holidays but go to next working day if it lands on them
2024-07-05 00:00:00 | 2024-07-20 00:00:00 | 2024-08-04 00:00:00 |  | 2024-07-04 00:00:00 |  | 2024-07-01 00:00:00 | 2 |  |  |  | need this to be 8/7/2024 (ie not count blue at all! count weekends but go to next working day if it lands on them
 |  | 2024-08-05 00:00:00 |  | 2024-07-05 00:00:00 |  | 2024-07-01 00:00:00 | 18 |  |  |  | need this to be 30/7/2024 (ie not count blue at all! Count weekends, count orange holidays but go to next working day if it lands on them
 |  | 2024-08-06 00:00:00 |  | 2024-07-06 00:00:00 |  | 2024-07-01 00:00:00 | 30 |  |  |  | need this to be 9/9/2024 (ie not count blue at all! don't count August at all! Count weekends, count orange holidays but go to next working day if it lands on them
 |  | 2024-08-07 00:00:00 |  | 2024-07-07 00:00:00
 |  | 2024-08-08 00:00:00 |  | 2024-07-08 00:00:00
 |  | 2024-08-09 00:00:00 |  | 2024-07-09 00:00:00
 |  | 2024-08-10 00:00:00 |  | 2024-07-10 00:00:00
 |  | 2024-08-11 00:00:00 |  | 2024-07-11 00:00:00 |  |  |  |  |  |  | this works for weekends and holidays (ie it counts them when calculating but if it lands on them skips to next working day. But I cannot make it exclude/ignore predefined ranges)
 |  | 2024-08-12 00:00:00 |  | 2024-07-12 00:00:00 |  |  |  |  |  | A | =WORKDAY($G4+$H4-1;1;$D$2:$D$3)
 |  | 2024-08-13 00:00:00 |  | 2024-07-13 00:00:00
 |  | 2024-08-14 00:00:00 |  | 2024-07-14 00:00:00 |  |  |  |  |  |  | this only works for many prefefined blue ranges (ie excludes/ignores them completely from counting when calculating. Cannot put weekends and orange holidays in it (ie count weekends and orange holidays but go to next working day if it lands on them)
 |  | 2024-08-15 00:00:00 |  | 2024-07-15 00:00:00 |  |  |  |  |  | B | =WORKDAY.INTL(G4;H4;"0000000";SMALL((A2:A5;B2:B5;C2:C32);ROW(INDIRECT("1:"&COUNT(A2:A5;B2:B5;C2:C32)))))
 |  | 2024-08-16 00:00:00 |  | 2024-07-16 00:00:00
 |  | 2024-08-17 00:00:00 |  | 2024-07-17 00:00:00
 |  | 2024-08-18 00:00:00 |  | 2024-07-18 00:00:00 |  |  |  |  |  |  | is there a way to merge A and B and make it behave in a way that it combines both behaviours ?
 |  | 2024-08-19 00:00:00 |  | 2024-07-19 00:00:00 |  |  |  |  |  |  | A) count weekends and orange holidays when calculating but go to next working day if the calculation ends on them
 |  | 2024-08-20 00:00:00 |  | 2024-07-20 00:00:00 |  |  |  |  |  |  | B) avoid/ignore completely blue ranges. Don't even count when calculating but stop counting and resume after the range has ended and again the same for next blue range. Keep doing so until we have counted x number of days.
 |  | 2024-08-21 00:00:00 |  | 2024-07-21 00:00:00
 |  | 2024-08-22 00:00:00 |  | 2024-07-22 00:00:00
 |  | 2024-08-23 00:00:00 |  | 2024-07-23 00:00:00
 |  | 2024-08-24 00:00:00 |  | 2024-07-24 00:00:00
 |  | 2024-08-25 00:00:00 |  | 2024-07-25 00:00:00
 |  | 2024-08-26 00:00:00 |  | 2024-07-26 00:00:00
 |  | 2024-08-27 00:00:00 |  | 2024-07-27 00:00:00
 |  | 2024-08-28 00:00:00 |  | 2024-07-28 00:00:00
 |  | 2024-08-29 00:00:00 |  | 2024-07-29 00:00:00
 |  | 2024-08-30 00:00:00 |  | 2024-07-30 00:00:00
 |  | 2024-08-31 00:00:00 |  | 2024-07-31 00:00:00