Forum Discussion

cmoulthrop's avatar
cmoulthrop
Copper Contributor
Jan 17, 2023
Solved

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

  • cmoulthrop 

    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_'s avatar
    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.

    • cmoulthrop's avatar
      cmoulthrop
      Copper Contributor

      Rodrigo_ Thank you soooo much! I will finally be able to sleep at night! Appreciate your time looking into that for me! 

      • cmoulthrop's avatar
        cmoulthrop
        Copper Contributor

        Rodrigo_ , would you please able to provide a screenshot with you in the formula cell so I can see the cell references? 

  • Rodrigo_'s avatar
    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 - syntax

    The 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 

    • cmoulthrop's avatar
      cmoulthrop
      Copper Contributor

      Rodrigo_ Thank you, sorry if there was confusion in my question. I am trying to have excel populate the date ranges for me. 

       

Resources