How to calculate date of the next DayOfWeek after given date?

Hello guys,

I got to a very challenging Excel problem. I would like to get the list of all [GivenDayOfWeek1] and [GivenDayOfWeek2] in the given month.

Month is set by a date in A1. [GivenDayOfWeek1] and [GivenDayOfWeek2] can be any day of week from Monday to Sunday and it's set in Cells B1 and C1.

For example I would like to get all Thursdays and Saturdays from November 2011.

The result would be (in column A starting at A2):

Thursday 3. 11. 2011
Saturday 5. 11. 2011
Thursday 10. 11. 2011
Saturday 12. 11. 2011
Thursday 17. 11. 2011
Saturday 19. 11. 2011
Thursday 24. 11. 2011
Saturday 26. 11. 2011

Is that even possible? I was able to get the list of all [GivenDayOfWeek1] (let's say Thursdays at cell B1), but I can't add the other day to the formula to be able to fill the rows with that.

Please Login or Register  to view this content.

Thank you very much for any help you can provide.

Does the attached help ?

It is based on the WEEKDAY function using 2 as argument ( Monday is 1, Tue, 2, etc..)

arthurbr thanks for the file. I need to have all the dates in one column, are you able to combine those two formulas together?

daddylonglegs thank you for the solution! It looks like it works!

The only problem is, that I work with MS Excel 2010 without SP1 in Czech version. When I open the file from yours and I confirm the security warning (it would be "Allow editing" in English i guess), I need to manually change the name of the English dates to it's Czech equivalent. Then the function starts working. But when I try to modify the function to my own needs (B1 -> D5 ; C1 -> D6 ; A1 -> B11 ; A2 -> B12) it does't work. At first I thought that I made a typo, but when I tried to change it back to the exactly same state as you send it, it didn't work either. I triple-checked it for typos and nothing was there. The only solution was to re-download your file again. Even if I add a space at the end of your formula
(the re-downloaded one) and then I delete it, it doesn't work afterwards.

I also tried to rewrite yours formula (with the same Cells) into the Czech form manually, using http://wwwhome.cs.utwente.nl/~trieschn/excel/excel.html , but it didn't work either.

Target users will have mostly Czech versions of MS Office ranging from 2003 to 2010, do you know how to edit the function to work at all MS Office product versions?

Do you know how to solve

It worked! Once again thank you for the help.