Date Range based on start date and end date -...

Ellen_Sligh1 · 06-02-2018 05:00 PM

Date Range based on start date and end date - I want to autofill a column with a range of dates given a start date and end date that skips weekends.

Possible?

Also, I want to have another column where holiday dates are listed and are skipped in the above mentioned column.

any ideas of how that can happen?

Grant_Stead

As long as you have your holidays/weekends listed in a range you can use the following in google sheets… Just throwing that at you.

Mike_A

For the first part, if you are using google sheets as your source, i would think that is easier to do with and Arrayformula in the sheet.

Would have to think about the second part, but also might be best done in the source sheet?

Grant_Stead

Sounds like an advanced scheduling tool is what you need. Like a primavera p6… I mean you could hack something together, but…

Grant_Stead

Is the finish date based on duration?

Grant_Stead

A simple way to start would be to create a static table in which you drag down every day for the next 5 years. Then you can have a column where you can denote true/false for holiday, another column that denotes the day of the week. Then you have future capability for adding a column for different shift coverage, etc.

Grant_Stead

Then in your expressions you do count (select off of your date ranges the select/filters can pull out the dates that mean something to you…

It’s a little crude, but it works… (And crude becomes relatively necessary when you stay taking about designated work days…)

Grant_Stead

Cause, you don’t want to hardcode this…

Ellen_Sligh1

It’s all based on next year’s school calendar, for example - given dates - I just want it to be easy to auto populate the column depending on the school’s year.

I don’t want to drag down… because I want something simple to use for the average teacher using this app who probably won’t look at the spreadsheet - so they can just put in their 1st day of school, last day of school, and holidays - done the list is created.

I got this formula from another google sheets community that works perfectly… but was wondering if there is a shorter version

"With a start date in B2, and an end date in C2 and with holidays in column D starting at D2, I think this formula will do what you are hoping.

=ARRAYFORMULA(FILTER(B2+ROW(INDIRECT(“A1:A”&C2-B2+1))-1,WEEKDAY(B2+ROW(INDIRECT(“A1:A”&C2-B2+1))-1,2)<6,ISNA(MATCH(B2+ROW(INDIRECT(“A1:A”&C2-B2+1))-1,D2:D,0))))"