1

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.

5
  • You need to provide more information. What do you mean by "auto-fill"? Down one column? Across a row? How many dates in the sequence? What should the starting or ending dates be?
    – Erik Tyler
    Feb 22, 2021 at 7:46
  • Thank you for reaching out. It will be down one column and there will be no limitation of the dates in the sequence. As for the start date, it can be any but not a Friday. Feb 22, 2021 at 9:54
  • "There are the formula to skip weekends but ..." Please edit the question and share the formula for skipping weekends. Feb 22, 2021 at 9:58
  • Please give a concrete example of a plausible start date and number of returned dates. It is virtually impossible to suggest a formula based on "any date" and "no limitation." If you mean that you want to start at X date and FILL the entire column from there, let us know this. Also in which cell of the sheet will the initial date go? Details matter in the interest of efficiently offering a solution.
    – Erik Tyler
    Feb 22, 2021 at 10:03
  • docs.google.com/spreadsheets/d/… Feb 22, 2021 at 10:50

2 Answers 2

0

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)

0

EDITED (following OP's sharing a test sheet)

Delete everything bellow B6 and use

=INDEX(IF(WEEKDAY(B6:B)=6,,B6:B))

enter image description here


Initial answer

You can use the following

=ArrayFormula(WORKDAY.INTL(C3,sequence(13,1),"0000100"))

enter image description here

C3 can be any date of your choice.
You can also expand the sequence to any number of rows/dates by changing 13 to any number you like.

Functions used:

1
  • Please remember that you can also contribute to this community that helps you. As per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Feb 22, 2021 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.