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-...
excel
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)))