Forum Discussion

Olivier_Maltais's avatar
Olivier_Maltais
Copper Contributor
Oct 18, 2023

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...
excel
Formulas and Functions
Olivier_Maltais's avatar
Olivier_Maltais
Copper Contributor
Oct 18, 2023

PeterBartholomew1Is 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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 18, 2023

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

Resources