Displaying a date range derived from WEEKNUM
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? -- tia Jock |
Displaying a date range derived from WEEKNUM
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 |
Displaying a date range derived from WEEKNUM
Absolutely brilliant.
Thanks very much. Jock "Rick Rothstein (MVP - VB)" wrote: 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 |
Displaying a date range derived from WEEKNUM
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. 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 |
All times are GMT +1. The time now is 01:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com