Date sequence formula for Excel 2010

[Peak Seagull]

Hi,

I am using the following function to create a date range from a series of date (see attached spreadsheet) =SEQUENCE(1,26,MIN(B6:B27)). This is fine for Office 365 but what is the equivalent function that will work in Excel 2010?

Thanks a lot.

 

[GraH - Guido]

If it is fixed on 2 days, why not use MIN (date) to get the first date and then simply for the next 25 columns add 1?

 

[Hui]

I'm unsure what the column is for as it only uses the first date
If you want the column transposed use:
D5: =INDEX($B$6:$B$27,COLUMNS($D$5:D5)) , copy across
D5: =TRANSPOSE(B6:B27) array entered into the row

If you want a Row starting at the value in B6 incrementaly by 1
D5: $B$6+COLUMNS($D$5:D5)-1

 

[Peak Seagull]

If it is fixed on 2 days, why not use MIN (date) to get the first date and then simply for the next 25 columns add 1?
View attachment 66763

That's great - thanks Guido - a simple solution - I like it

 

[Peak Seagull]

I'm unsure what the column is for as it only uses the first date
If you want the column transposed use:
D5: =INDEX($B$6:$B$27,COLUMNS($D$5:D5)) , copy across
D5: =TRANSPOSE(B6:B27) array entered into the row

If you want a Row starting at the value in B6 incrementaly by 1
D5: $B$6+COLUMNS($D$5:D5)-1

Thanks a lot Hui - the solution D5: $B$6+COLUMNS($D$5:D5)-1 workd nicely. Why is it -1 instead of +1?

 

[Hui]

COLUMNS($D$5:D5) returns 1
but for that you want 0

When you copy it across 1 cell it becomes: COLUMNS($D$5:E5) which is 2
but you only want 1