Google-sheets – How to autofill date but skip Friday of every week

google sheetsgoogle-sheets-arrayformulagoogle-sheets-dates

I was trying to skip Friday of every week when I am trying to autofill the date in Google sheet.

There are formulas to skip weekends but I couldn't find any formula to skip any specific day of the week while doing autofill.

Best Answer

I'll offer a suggestion, but understand that I'm making it based on limited information as I still await answers to my comment-questions on the original post.

If you wanted every non-Friday date between today and June 1, 2021:

=FILTER(SEQUENCE((DATEVALUE("June 1, 2021")-TODAY())+1,1,TODAY()),WEEKDAY(SEQUENCE((DATEVALUE("June 1, 2021")-TODAY())+1,1,TODAY()))<>5)

The basic shell of this is as follows:

=FILTER [array of dates] such that the WEEKDAY of [array of dates] <> 5

That array of dates is made with SEQUENCE.

SEQUENCE can take four arguments (though the third and fourth can be left to defaults):

1.) number of rows

2.) number of columns

3.) starting number (with the default being 1)

4.) interval value (i.e., how many to skip between each, with the default being 1)

In my example, the number of rows is found by (DATEVALUE("June 1, 2021")-TODAY())+1.

The number of columns is 1.

The starting number is TODAY().

The interval is the default: 1.

If you don't want to use an exact end date but rather just want X number of dates from today (e.g. 25), you'd use this version:

=FILTER(SEQUENCE(25,1,TODAY()),WEEKDAY(SEQUENCE(25,1,TODAY()))<>5)

Related Topic