Google Spreadsheet Alternating dates

Question:

( by Daz A )

Right now I have a project that updates once a week, which is very easy to enter into Sheets: I just add in two Wednesday dates and drag down to auto-fill the column for every upcoming Wednesday. Super easy!

But in the future I'm switching to twice weekly updates, every Wednesday and Sunday. I've tried doing the same thing, this time entering in four dates to give Sheets a pattern to recognize and drag down... But this doesn't work. It comes off in a different format (including hourly time) and starts becoming inaccurate after more than two steps (so it switches to Thursday/Sunday). 

Is there any other way to get alternating dates like this to auto-fill? I want to check if there's any easier way to do this before trying to fill it in by hand or trying out Apps Script or something...

Solution:

Have a look at the following screenshot:

To get auto filled 10 dates starting from "3/15/2013", I have the following formula in Cell A1:

=arrayformula(if(row(A1:A10);datevalue("3/15/2013")+row(A1:A10)-1;""))

To get auto filled 10 dates starting from today(), I have the following formula in Cell C1:

=arrayformula(if(row(A1:A10);today()+row(A1:A10)-1;""))

To get only dates for only Wednesdays out of "10 dates starting from today()", I have the following formula in Cell E1:

=arrayformula(if(weekday(today()+row(A1:A10)-1)=4;today()+row(A1:A10)-1;""))

in the above formula 4 is for Wednesday..

To get only dates for only Wednesdays and Sundays, I have the following formula in Cell F1:

=arrayformula(if( (weekday(today()+row(A1:A10)-1)=4)+(weekday(today()+row(A1:A10)-1)=1) ;today()+row(A1:A10)-1;""))

in the above formula 4 is for Wednesday and 1 is for Sunday..

To avoid blank rows, I have the following formula in Cell G1:

=query(arrayformula(if(weekday(today()+row(A1:A10)-1)=4;today()+row(A1:A10)-1;""));"select Col1 where Col1 is not null";0)

And I have the following formula in Cell H1:

=query(arrayformula(if( (weekday(today()+row(A1:A10)-1)=4)+(weekday(today()+row(A1:A10)-1)=1) ;today()+row(A1:A10)-1;""));"select Col1 where Col1 is not null";0)

And finally, to get N dates which are Wednesdays and Sundays starting from today, I have the following formula in Cell I1:

=query(arrayformula(if( (weekday(today()+row(A1:A1000)-1)=4)+(weekday(today()+row(A1:A1000)-1)=1) ;today()+row(A1:A1000)-1;""));"select Col1 where Col1 is not null limit 20";0)

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive