Printable View
I have a calendar with 2 rows in between every row of dates for notes.
The first row of dates are correct, but the following 5 rows are all repeat of the first row.
I need help to modify the formula for the rest of the dates to populate correctly.
Thank you
The formula you are using is an array formula. The reason why it was repeating the same dates for each row was because of the transposed numeric array {0;1;2;3;4;5} that was meant for each row.
So in your case, you should split this formula in to 6 individual rows, each with seperate row index, with the first index starting with 0.
=IF(MONTH(DATE(YEAR(I1),MONTH(I1),1))<>MONTH(DATE( YEAR(I1),MONTH(I1),1)-(WEEKDAY(DATE(YEAR(I1),MONTH(I1),1))-1)+{0}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(I1),MONTH(I1),1)-(WEEKDAY(DATE(YEAR(I1),MONTH(I1),1))-1)+{0}*7+{1,2,3,4,5,6,7}-1)
Oh man, thank you very much.
you are a genius Excel Fox....