Still needs help with date formulas

[Guest]

My initial question was as follow below:

“Is there a formula to automatic fill out the right corresponding dates, or
other ways to do it??
I.e.
a1=2007 (year)
b1= 2 (week nr)

and in c1-c7 I want it automatically to say 8, 9, 10 and so fort.

Can anyone help me??â€

I got the following answer from teethless mama:

“C1 =DAY(DATE($A$1,1,1)-WEEKDAY(DATE($A$1,1,1),1)+7*$B$1-5)+ROWS($1:1)-1

Format cell as general
Drag the Fill Handle from C1 to C7â€

Witch helped a lot, but didn’t quit get me there.

This formula count to 31 and therefore when I use it in February (witch only
has 28 days) it doesn’t start at 1 again at the right day.

My problem is that I am only gone use 1 sheet whit 4 week columns (with 7
days column for every week) and change the week number and then get the right
date just from the year and week number.

So please anyone, can I get some more help?

 

[T. Valko]

Try this:

=DAY(DATE(A$1,1,B$1*7-LOOKUP(ROWS($1:1),{1,2,3,4,5,6,7},{6,5,4,3,2,1,0})))

Copy down to C7

Biff

 

[T. Valko]

Slightly shorter:

=DAY(DATE(A$1,1,B$1*7-INDEX({6,5,4,3,2,1,0},ROWS($1:1))))

Biff

 

[Guest]

Thanks T. Valko
U´re good

Try this:

=DAY(DATE(A$1,1,B$1*7-LOOKUP(ROWS($1:1),{1,2,3,4,5,6,7},{6,5,4,3,2,1,0})))

Copy down to C7

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thanks T. Valko
U´re good