Displaying a date range derived from WEEKNUM

[Guest]

How can I calculate and display the actual dates (workdays, not incl weekends
preferred but not essential) in, say, column A which refer to WEEKNUM in
column B?

 

[Rick Rothstein \(MVP - VB\)]

How can I calculate and display the actual dates (workdays, not incl

weekends
preferred but not essential) in, say, column A which refer to WEEKNUM in
column B?

Let us assume that the year number is in A1 and that the week number is in
B1. To list the 5 weekdays in that week number, put this formula

=DATE($A$1,1,1+MOD((9-MOD(WEEKDAY(DATE($A$1,1,1)),7)),7))+7*($B$1-1)+ROWS($1:1)-1

in any cell and drag down through the next 4 cells.

Rick

 

[Guest]

Absolutely brilliant.
Thanks very much.



Jock

Let us assume that the year number is in A1 and that the week number is in
B1. To list the 5 weekdays in that week number, put this formula

=DATE($A$1,1,1+MOD((9-MOD(WEEKDAY(DATE($A$1,1,1)),7)),7))+7*($B$1-1)+ROWS($1:1)-1

in any cell and drag down through the next 4 cells.

Rick

 

[Rick Rothstein \(MVP - VB\)]

How can I calculate and display the actual dates (workdays, not incl

Let us assume that the year number is in A1 and that the week number is in
B1. To list the 5 weekdays in that week number, put this formula

=DATE($A$1,1,1+MOD((9-MOD(WEEKDAY(DATE($A$1,1,1)),7)),7))+7*($B$1-1)+ROWS($1:1)-1

in any cell and drag down through the next 4 cells.

Looks like we can simplify that expression a little bit...

=DATE($A$1,1,1+MOD(9-MOD(DATE($A$1,1,1),7),7))+7*($B$1-1)+ROWS($1:1)-1

Rick