Forum Discussion

gabbym's avatar
gabbym
Copper Contributor
Jan 13, 2023

sequence by week period

Hello,

 

How can I get dates to be in sequence per week period with a formula?

 

for example, I would want column on left to be separated by week period in each cell like shown: 

 

12/26/22-01/01/23Week 1
01/02/23-01/08/23Week 2
01/09/23-01/15/23Week 3
01/16/23-01/22/23Week 4
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 13, 2023

    gabbym 

    A couple of modifications to Patrick2788 's solution

    = LET(
    n,     QUOTIENT(DAYS(end, start),7),
    dates, SEQUENCE(n, , start, 7),
    HSTACK(
        TEXT(dates, "mm/dd/yyyy") & "-" & TEXT(dates + 6, "mm/dd/yyyy"),
        "Week " & WEEKNUM(dates + 7)
    )
  )

    Start and end are now named cells.  The variable 'n' has been converted to weeks by dividing by 7.  The end on week has been set to 6 days after the start.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 13, 2023

    gabbym 

    This may work for you. You can adjust 'start' and 'end' dates as needed.

     

     

    =LET(
    start, DATE(2022, 12, 26),
    end, DATE(2023, 12, 31),
    n, DAYS(end, start),
    dates, SEQUENCE(n, , start, 7),
    HSTACK(
        TEXT(dates, "m/d/yyy") & "-" & TEXT(dates + 7, "m/d/yyy"),
        "Week " & WEEKNUM(dates + 7)
    )
)

     

    Without HSTACK version:

    =LET(start,DATE(2022,12,26),end,DATE(2023,12,31),n,DAYS(end,start),dates,SEQUENCE(n,,start,7),IF({1,0},TEXT(dates,"m/d/yyy")&"-"&TEXT(dates+7,"m/d/yyy"),"Week "&WEEKNUM(dates+7)))

     

Resources