Filter for working days

Dear all,

I have a dataset with stockprices and dayreturns of the Dutch Stock Market in the period of 1990-2015. I would like to test a sort of theory, which states that the first 3 exchange days and the last exchange day of the month generates a significantly higher dayreturn compared to the other days of the month. I would like to give these days a dummy number of 1. All the other days of the month are given the number of 0. The problem contains the follows: Due to the large amount of dayreturns I couldn't configure this manually. I probably need a formula which helps me to get further on. The second problem is that the exchange days differ from month to month and year to year due to different ending dates (28,30,31) and including weekends and holidays (these weekends and holidays are not considerd in my dataset). And even if I would do this manually, I first have to find out which days are last days of the month and that takes a lot of time. I hope that excel contains a formula which can help me. I'm thinking about an If condition, but don't know the exact characters.

I attached my workfile to this message.

Best regards,

Tim Lader

Thank you both guys! You really helped me out. The weekend and Dutch bank holidays were indeed already filtered out. You both came with different formulas, but with the right solution.

I would like to thank you both.

Best regards,

Tim Lader