Forum Discussion
cmoulthrop
Jan 17, 2023Copper Contributor
How to auto-populate the weeks of the year
How to auto-populate the weeks of the year with their beginning and ending date (Ex: 1/2 - 1/8) and autofill
Hello cmoulthrop,
sorry for misunderstanding your question, not really good in English.
anyway... based on your image.the sample is on the attached.
- PeterBartholomew1Silver Contributor
The same formula as that introduced by Rodrigo_ but optimised for readability in Excel 365
= LET( counter, SEQUENCE(1, weeks, weeks-1, -1), startweek, TEXT(start + 7*counter, "m/d"), endweek, TEXT(start + 7*counter+6, "m/d"), startweek & " - " & endweek )
- Rodrigo_Steel Contributor
Hello cmoulthrop,
sorry for misunderstanding your question, not really good in English.
anyway... based on your image.the sample is on the attached.
- cmoulthropCopper Contributor
Rodrigo_ Thank you soooo much! I will finally be able to sleep at night! Appreciate your time looking into that for me!
- cmoulthropCopper Contributor
Rodrigo_ , would you please able to provide a screenshot with you in the formula cell so I can see the cell references?
- Rodrigo_Steel Contributor
Hello cmoulthrop,
Using a single date, you can determine the week number of the year
Assuming you have a date in A2.
enter this formula on B2: =WEEKNUM(A2)
WEEKNUM function - syntaxThe WEEKNUM function is used in Excel to return the week number of a specific date in the year (a number between 1 and 54). It has two arguments, the 1st is required and the 2nd is optional:
=WEEKNUM(serial_number, [return type])
- Serial_number - any date within the week whose number you are trying to find. This can be a reference to a cell containing the date, a date entered by using the DATE function or returned by some other formula.
- Return_type (optional) - a number that determines on which day the week begins. If omitted, the default type 1 is used (the week beginning on Sunday).
source: WEEKNUM function
- cmoulthropCopper Contributor
Rodrigo_ Thank you, sorry if there was confusion in my question. I am trying to have excel populate the date ranges for me.