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

**newbieee** · 11-01-2011, 03:09 PM

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.

A2=IF(A1="";"";A1+IF(WEEKDAY($B$1;2)<WEEKDAY(A1;2);7-WEEKDAY(A1;2)+WEEKDAY($B$1;2);WEEKDAY($B$1;2)-WEEKDAY(A1;2)))

Thank you very much for any help you can provide.

**arthurbr** · 11-01-2011, 04:40 PM

Does the attached help ?

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

**newbieee** · 11-01-2011, 05:05 PM

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

**martindwilson** · 11-01-2011, 07:16 PM

try this you could hide b1/c1 by putting and on another sheet

**daddylonglegs** · 11-01-2011, 07:45 PM

With A1 1st of any month, B1 any day as text and C1 any day as text try this "array formula" in A2 confirmed with CTRL+SHIFT+ENTER and copied down as far as needed and further

=IFERROR(SMALL(IF(COUNTIF(B$1:C$1,TEXT(ROW(INDIRECT(A$1&":"&EOMONTH(A$1,0))),"dddd")), ROW(INDIRECT(A$1&":"&EOMONTH(A$1,0)))),ROWS(A$2:A2)),"")

...and if you want 3 or 4 days included you can just add those in D1 and E1 etc. and change the range in the formula......

**newbieee** · 11-02-2011, 09:07 AM

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

**daddylonglegs** · 11-02-2011, 09:39 AM

The formula is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER, if you modify it that needs to be done again.

Do that like this

Select cell with formula, press F2 key to select formula, then hold down CTRLa and SHIFT keys and press ENTER. If doene correctly then curly braces like { and } will appear around the formula in the formula bar