Заполните список дат воскресенья и игнорируйте воскресенья в списке исключений в Excel

Question

Я работаю над листом, который мне нужно заполнить датами воскресенья, и убедиться, что даты из списка исключений не присутствуют в моем окончательном списке

Цель: иметь список дат только рабочих воскресений

вот предварительный просмотр

working days    .........                  Vacation days  
07/01/2012         ............               04/02/2012  
14/01/2012         ............               11/02/2012  
21/01/2012  
28/01/2012{{                  
18/02/2012  
25/02/2012  
03/03/2012  
10/03/2012  
17/03/2012  
24/03/2012  
31/03/2012    

Пока что я заполнил список в столбце А следующим образом:

A1 =2012  
A3 =DATE(A1;1;1)+7-WEEKDAY(DATE(A1;1;1))  
A4 =IF(YEAR(A3+7)=$A$1;A3+7;"")  

Как я могу улучшить это, чтобы он мог игнорировать даты отпуска при распечатке рабочих воскресений?

Answer 1

Те даты, которые вы перечислили, выглядят как субботы, но при условии, что вы хотите воскресенья, как указано в Excel 2010, вы можете использовать эту формулу в A3

=WORKDAY.INTL(DATE(A$1;1;0);1;"1111110";C$2:C$10)

Предполагая даты отпуска, указанные в C2:C10

тогда эта формула в А4 скопирована до А55

=IF(A3="";"";IF(YEAR(WORKDAY.INTL(A3;1;"1111110";C$2:C$10))=A$1;WORKDAY.INTL(A3;1;"1111110";C$2:C$10);""))

В Excel 2007 вы можете использовать эти формулы для A3 и A4, скопированные соответственно:

=DATE(A1;1;1)-WEEKDAY(DATE(A1;1;7))+MIN(IF(COUNTIF(C$2:C$10;DATE(A1;1;1)-WEEKDAY(DATE(A1;1;7))+7*{1;2;3;4;5;6;7;8;9})=0;{1;2;3;4;5;6;7;8;9}))*7

а также

=IFERROR(SMALL(IF(COUNTIF(C$2:C$10;A3+7*{1;2;3;4;5;6;7;8;9})=0;IF(YEAR(A3+7*{1;2;3;4;5;6;7;8;9})=A$1;A3+7*{1;2;3;4;5;6;7;8;9}));1);"")

последние формулы предполагают, что у вас никогда не будет более 8 последовательных воскресных отпусков