Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Create an interval of date

Copper Contributor

# Create an interval of date

Hello, I would like to have a formula to have a date interval. I want that the cell looks like this:
2023/12/31 to 2024/01/06

I would like to use a formula to set all the week of the year like this with one cell by week

Is that possible ?

6 Replies

# Re: Create an interval of date

@Olivier_Maltais If you are on a modern version of Excel you can use the formula below as demonstrated in the attached file.

``=TEXT(SEQUENCE(52,,A1,7),"yyyy/mm/dd") & " to " & TEXT(SEQUENCE(52,,A1+6,7),"yyyy/mm/dd")``

where A1 contains the first day of the sequence (in your case 2023/12/31).

# Re: Create an interval of date

This takes @Riny_van_Eekelen 's formula a step or two further, for better or worse!

I finished with the worksheet formula

``= Weeksλ(baseDate, 26)``

where Weeksλ is a Lambda function that returns one week intervals as text, here starting with 'baseDate' and returning 26 weeks.  The function executes the steps

``````Weeksλ
= LET(
intervals, SEQUENCE(count, , base, 7) + {0, 6},
week,      TEXT(intervals, "yyyy/mm/dd"),
BYROW(week, Concatλ)
)``````

Adding {0, 6} to the week starting dates spills dynamically to give start and end dates for each week.  The TEXT function is applied to each date, returning dates in international format.  BYROW takes the dates in pairs and concatenates them using Concatλ to insert the time interval using the preposition 'to' as the separator,

``````Concatλ
= TEXTJOIN(" to ", , w)``````

Of course, all this requires Excel 365.

# Re: Create an interval of date

Showing various steps along the way ...

# Re: Create an interval of date

@Peter BartholomewIs that possible to have the list but in horizontal cells ? I tried to copy the formula but it didn't work on my excel sheet

# Re: Create an interval of date

``=TEXT(SEQUENCE(,52,A1,7),"yyyy/mm/dd") & " to " & TEXT(SEQUENCE(,52,A1+6,7),"yyyy/mm/dd")``

This would be the fix if you would slightly alter my earlier formula. The key element, also in @Peter Bartholomew 's formulas, is the SEQUENCE function. The first two arguments set the 'shape' of the array in the number of rows and columns.

=SEQUENCE(52,1, start, step) will create a list of 52 rows long, 1 column wide.

=SEQUENCE(1,52, start, step) will create a list of 1 row long, 52 columns wide.

The 1's may be omitted.

# Re: Create an interval of date

This is the changed workbook showing both @Riny_van_Eekelen 's formula and my function.

I had to change the {0,6} to {0;6} (row to column) but also concatenate the dates by column.