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 start date of each week.
Syntax
=MAX(DATE(current year,1,1),DATE(current year,1,1)-WEEKDAY(DATE(current year,1,1),2)+(week number-1)*7+1)
Steps
- Start with the MAX function =MAX(
- First argument of the MAX function is the first day of the current year DATE($C$2,1,1),
- Enter the first day of current year again 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)
- Subtract 1 from the current week day and multiply it by 7, then add it to formula +(B5-1)*7
- Finally, add 1 +1
- Type in ) to close MAX function and press Enter 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 MAX function place a role as a fail-safe for first 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 MAX 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.
=MAX(DATE($C$2,1,1),DATE($C$2,1,1)-WEEKDAY(DATE($C$2,1,1),2)+(B5-1)*7+1)
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)
