Creating array of dates between two dates

[tomas]

Hi

I have a start and end date and would like to create array of all dates including these two.
Then I will use weekday function to find out whether array contains weekend.

Obviously there are other option how to solve this but if I can do array it seems the best.

 

[Hui]

I would iuse a Named Formula
MyDates: =List1!$A$2+ROW(OFFSET(List1!$A$1,,,List1!$B$2-List1!$A$2+1,1))-1

To find out if the list includes a Weekend
=MAX(WEEKDAY(MyDates,2))>5 Ctrl+Shift+Enter

 

[Ashhu]

I would iuse a Named Formula
MyDates: =List1!$A$2+ROW(OFFSET(List1!$A$1,,,List1!$B$2-List1!$A$2+1,1))-1

To find out if the list includes a Weekend
=MAX(WEEKDAY(MyDates,2))>5 Ctrl+Shift+Enter

Dear Hui
I could not understand the solution part, can you please do me favour by uploading the solution file. thanks.

 

[tomas]

I would iuse a Named Formula
MyDates: =List1!$A$2+ROW(OFFSET(List1!$A$1,,,List1!$B$2-List1!$A$2+1,1))-1

To find out if the list includes a Weekend
=MAX(WEEKDAY(MyDates,2))>5 Ctrl+Shift+Enter

Thanks works nicely

 

[Hui]

Dear Hui
I could not understand the solution part, can you please do me favour by uploading the solution file. thanks.

The solution is in my post above

 

[Ashhu]

The solution is in my post above

Got it, thanks

 

[NARAYANK991]

Hi ,

Another way of obtaining an array of values , where the start value is in one cell reference , say A2 , and the end value is in another cell reference , say B2 , is :

=ROW(INDEX($A:$A,$A$2):INDEX($A:$A,$B$2))

Wrapping the WEEKDAY function around this , and then using the OR function will allow you to check whether the array of dates contains any particular week day.

Since the weekends are at the higher end of the range of values for the week days , the MAX function will work , but if you wish to check whether the array of dates contains a Tuesday , the MAX function cannot be used , because it checks for only one value.

The usage of OR will always work , because the OR function looks at an array of values.

For example to check whether a range of dates contains a Tuesday , which is 2 when the WEEKDAY function is used with a second parameter value of 2 , the following array formula will work :

=OR(WEEKDAY(ROW(INDEX($A:$A,A2):INDEX($A:$A,B2)),2)=2)

Narayan

 

[Jojo3]

I'd like to generate the list of dates between a start and end date... any help please.

 

[vletm]

Jojo3
Did You checked answers from this thread?
or
You should open a new thread.

 

[Peter Bartholomew]

Or you could buy an Office 365 subscription and use
= SEQUENCE( 1+enddate-startdate, 1, startdate )

Then the fun can start. If you named the formula 'interval.days' you could use it within
= FILTER( interval.days, WEEKDAY( interval.days, 2 ) <= 5 )
to give a list of weekdays within the specified interval.

p.s. If you like your timelines horizontal, all you need do is reverse the parameters in the name 'interval.days' in Name Manager to read
= SEQUENCE( 1, 1+enddate-startdate, startdate )