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

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

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

 

@Olivier_Maltais 

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.

Showing various steps along the way ...

@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

@Olivier_Maltais 

 

 

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

@Olivier_Maltais 

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

image.png

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