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