# EXCEL populate a list of sunday's dates and ignore the sundays in an exception list

Hi i'm working on a sheet which i have to populate with sundays dates, and make sure that dates from an exception list don't be present in my final list

Goal: is to have the list of dates of only working sundays

here is a preview

``````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
``````

So far I populated the list in column A with this :

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

How to improve this so it can ignore vacation dates when listing working Sundays? Any help apreciated.

-

Those dates you listed appear to be Saturdays but assuming you want Sundays as stated then in Excel 2010 you can use this formula in A3

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

Assuming vacation dates listed in C2:C10

then this formula in A4 copied down to A55

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

In Excel 2007 you can use these formulas for A3 and A4 copied down respectively:

`=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`

and

`=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);"")`

latter formulas assume you never have more than 8 consecutive Sunday vacactions

-
That was all i need thanks so much... it's working like a charm. both formulas on 2007 and excel 2010 tested. i confirm what u said on 2007 it doesn't work for more than 8 consecutive sunday vacations i tried to vote up for your answer but i don't have enougn reputation points thanks again – kamalam Jan 6 '13 at 19:17
You should be able to "accept" my answer.....If you want more than 8 consecutive Sundays you can just extend the {1;2;3;4;5;6;7;8;9} part to be one more than you need as maximum – barry houdini Jan 6 '13 at 20:28