You can easily convert a date into the week number in Excel with the WeekNum Function. On the other hand, you can also calculate the days in the week if you know the week number in the year.
Since there are always 7 days in a week and to calculate the first day of the week based on the week number, you will need to figure out the very first day in the first week of the year, then to add the days to the week number.
1. The First Day of the Week
Please copy the formula below, change "YYYY" and "WK#" to the year and the week number to get the first day of the week.
=MAX(DATE(YYYY,1,1), DATE(YYYY,1,1) – WEEKDAY(DATE(YYYY,1,1),1) + 1 + (WK#-1)*7)
For example, the first day January 1, 2016 is the 6th day of the week if you use Sunday the first day of the week. The very first day of the week which has January 1, 2016 is December 27, 2015.
To get "December 27, 2015" based on January 1, please use the following formula:
=DATE(2016,1,1) – WEEKDAY(DATE(2016,1,1),1)+1
where 1 in the weekday function is the weekday type, please see below for the full list. If you want Monday as the first day of the week, please use 2 instead of 1.
If the week number is 2, for example, you need to use the very first day to add 7 days which is (2-1)*7, or if you know the week number is 3, you need to use the very first day to add (3-1)*7 days to have the first day of the 3rd week. To get the first day of any week in 2016, please use the formula below.
=DATE(2016,1,1) – WEEKDAY(DATE(2016,1,1),1) + 1 + (WK# – 1)*7
The Max Function is to avoid the first day in the first week falls in the year before (e.g., December 27, 2015).
2. The Last Day of the Week
To get the last day of the week given the week number, please use the formula below and change YYYY and WK# to the values you have:
=MIN(DATE(YYYY,12,31), DATE(YYYY,1,1) -WEEKDAY(DATE(YYYY,1,1),1) + 7*WK#)
The logic is to get the last day of the last full week before the week with January 1, and then to add days in weeks to the week number.
For example, the last day of the last full week is December 26, 2015 calculated by the formula:
=DATE(2016,1,1) – WEEKDAY(DATE(2016,1,1),1)
where 1 is the week type. Adding the weeks to get the last day of the week in the week number.
The Min Function is to avoid the situation that the last day in the last week falls in the next year, and December 31 will be the last day in that case.
