Although Excel has a function to get week number of a date, WEEKNUM; there isn't any to make reverse calculation. This method will help you answer the question - what week of the year is it, and give you the end date of each week.
Syntax
=MIN(DATE(current year+1,1,0),DATE(current year,1,1)-WEEKDAY(DATE(current year,1,1),2)+week number*7)
Steps
- Start with the MIN function =MIN(
- First argument of MIN function is the last day of current year DATE($C$2+1,1,0),
- Enter the first day of current year for the second argument DATE($C$2,1,1)
- Subtract the week number of the first day of the current year -WEEKDAY(DATE($C$2,1,1),2)
- Add a multiplier for the current week day and 7 +B5*7
- Type in ) to close MIN function and press the Enter key to complete formula
How
Excel keeps date and time values as numbers. Excel assumes that the dates started with Jan 1st, 1900, and considers this date "1". While whole numbers represents days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 0.5. As a result, adding or subtracting a number from a date value means adding or removing days from a date.
The idea behind the formula combination we are creating is to add 7 days for each week that has passed. Also, the MIN function acts as a a fail-safe for the last week of the year. However, not all years starts on a Monday. To account for this difference, we need to get back from the first day of the year as many days as necessary to find the first Monday.
Excel's the WEEKDAY function returns the number of the "day" in the specified format. While the function's first argument gets the date value, the second argument gets a number that can tell us where a week starts. We choose "2" which represents Monday as the first day of the week.
WEEKDAY(DATE($C$2,1,1),2)
Subtracting the week day value from the first day of the year returns the real date that the first week starts.
DATE($C$2,1,1)-WEEKDAY(DATE($C$2,1,1),2)
The MIN function makes a check that if the first day of the week is in the previous year, it returns the first day of the year.
=MIN(DATE($C$2+1,1,0),DATE($C$2,1,1)-WEEKDAY(DATE($C$2,1,1),2)+B5*7)
The DATE function simply returns a date value of the selected year, month and day arguments. We set year in a separate cell to make the formula dynamic.
DATE($C$2,1,1)
Also see What week of the year is it and how to get its start date to find the start date of weeks.
