Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.