To automatically fill weekdays, excluding weekends, in Google Sheets, there is a handy array formula. Simply input a starting date and the desired number of consecutive dates (excluding weekends) to generate the sequence. The array formula will handle the rest!
For instance, if you want to populate 10 days, excluding weekends, starting from Tuesday, 1 Oct 2019, input only the date (Tuesday, 1 Oct 2019) and the number 10 into the formula. We’ll revisit the specifics of the array formula later.
Before delving into the array formula, let me share a non-array formula. This enables you to drag and drop the formula to fill weekdays in rows.
Sequential Dates Excluding Weekends – Non-Array Formula
Let’s choose a specific date in cell C2: Tuesday, 1 Oct 2019. To generate sequential dates, excluding weekends from this starting date, input either of the following WORKDAY.INTL formulas in cell C3 and drag it down:
=WORKDAY.INTL($C$2, ROW(A1), 1)
=WORKDAY.INTL($C$2, ROW(A1), "0000011")The formula will only autofill the dates Monday-Friday skipping Saturday and Sunday as below.
Note: We will explore how to specify different weekends in the explanation provided below.
How WORKDAY.INTL Generates Weekdays in Google Sheets
I used the WORKDAY.INTL function in Google Sheets to populate sequential dates (workdays) while excluding weekends. This function facilitates the retrieval of the date after a specified number of workdays.
For an in-depth tutorial on the WORKDAY.INTL function, you can refer to my comprehensive date functions guide available here – “How to Utilize Google Sheets Date Functions.”
Now, to explain the formula above, let’s review the syntax of the WORKDAY.INTL function:
WORKDAY.INTL(start_date, num_days, [weekend], [holidays])In our case, we do not need the last argument, ‘holidays.’ You can simply disregard it, as our objective is to generate sequential dates while skipping weekends, not holidays.
Let’s focus on the essential first three arguments and understand how they are used in my formula.
WORKDAY.INTL – Explanation of Required Arguments and Their Use in Formula
start_date: Tuesday, 1 Oct 2019
This is the date from which the number of days will advance.
num_days: 1
This represents the number of working days to advance from start_date.
weekend: 1 or “0000011”
The weekend parameter designates Saturday and Sunday as weekends. You can specify a different weekend by referring to the table below:
| Weekend Number | Weekend String | What they Represent |
| 1 | “0000011” | Saturday/Sunday |
| 2 | “1000001” | Sunday/Monday |
| 7 | “0000110” | Friday/Saturday |
| 11 | “0000001” | Sunday |
| 12 | “1000000” | Monday |
| 13 | “0100000” | Tuesday |
| 14 | “0010000” | Wednesday |
| 15 | “0001000” | Thursday |
| 16 | “0000100” | Friday |
| 17 | “0000010” | Saturday |
To comprehend the formula’s behavior when copied down, let’s examine the formulas in cells C3, C4, and C5.
1. Formula in C3:
=WORKDAY.INTL($C$2, ROW(A1), 1) // advances 1 day from C22. Formula in C4:
=WORKDAY.INTL($C$2, ROW(A2), 1) // advances 2 days from C23. Formula in C5:
=WORKDAY.INTL($C$2, ROW(A3), 1) // advances 3 days from C2The start_date in cell C2 is Tuesday, 1 Oct 2019.
In the formula in cell C3, the num_days is ROW(1), equivalent to 1. Thus, the formula advances by 1 working day while skipping weekends, resulting in Wednesday, 2 Oct 2019 in cell C3.
For cell C4, num_days (number of working days to advance, excluding Saturday and Sunday) is ROW(A2), i.e., 2 days. Consequently, the formula returns Thursday, 3 Oct 2019.
In cell C5, num_days is 3 days (ROW(A3)), leading to the formula returning the date Friday, 4 Oct 2019. Once again, there are no weekends to skip in this case.
However, in cell C6, the formula =WORKDAY.INTL($C$2, ROW(A4), 1) advances by 4 days, excluding weekends.
The output in this cell is Monday, 7 Oct 2019, not Saturday, 5 Oct 2019, as the formula skips Saturday, 5 Oct 2019, and Sunday, 6 Oct 2019 while advancing num_days.
This is how the formula can return sequential dates excluding weekends in a non-array form.
Sequential Dates Excluding Weekends – Array Formula
If you input sequential numbers from 1 to the desired number of days to advance in the num_days argument of WORKDAY.INTL, the formula can generate an array of sequential dates, excluding weekends.
To implement this, replace ROW(A1) in the formula provided above in cell C3 with either ROW(A1:A13) or SEQUENCE(13) to generate 13 days while skipping weekends in Google Sheets. You can adjust the number 13 to match the total days you want to populate, excluding weekends. Also, wrap the formula with the ArrayFormula function.
Use either of the following formulas in cell C3 to populate sequential dates, excluding weekends, in Google Sheets:
Array Formula to Autofill Weekdays Excluding Weekends in Google Sheets:
=ArrayFormula(WORKDAY.INTL(C2, SEQUENCE(13, 1), 1))
=ArrayFormula(WORKDAY.INTL(C2, ROW(A1:A13), 1))Resources
This tutorial elaborated on the process of populating sequential dates excluding specific weekends in Google Sheets, utilizing the WORKDAY.INTL function. Additionally, we employed ROW or SEQUENCE functions. Here are some other topics related to sequential dates.
- Find Missing Sequential Dates in a List in Google Sheets [Array Formula]
- How to Auto Populate Dates Between Two Given Dates in Google Sheets
- Populate a Full Month’s Dates Based on a Drop-down in Google Sheets
- Calendar Week Formula in Google Sheets to Combine Week Start and End Dates
- Convert Dates to Week Ranges in Google Sheets (Array Formula)
- Fully Flexible Fiscal Year Calendar In Google Sheets
- Create Monthly Calendars in Google Sheets (Single & Multi-Cell Formulas)
- Google Sheets: List All Sundays from a Start and End Date
- How to Autofill Days of the Week in Google Sheets
 while excluding weekends.){kind=link}
Hello, Please help me with my timeline tracker. I just want to exclude SUNDAYS and PHILIPPINE HOLIDAYS on the total days count. Thank you so much!
Hi Angel,
You can use the NETWORKDAYS.INTL function in Google Sheets to exclude weekends and holidays from your timeline tracker.
Hi, can you help me with my formula?
I need to determine if my date in column K is a Philippine holiday and weekend, then would provide me a true or false answer in column M.
Thanks.
Hi, Yeo,
It’s possible if you hand-enter the holidays (not weekends) as a list in another range.
Example:
1. Enter the holidays in L2:L.
2. Empty M2:M.
3. Then insert the following formula in M2.
=ArrayFormula(if(not(K2:K),,if(ifna(match(K2:K,L2:L,0))+(weekday(K2:K)=7)+(weekday(K2:K)=1),TRUE,)))It will highlight all the weekends and holidays in the list.
If you want to mark the holidays that fall on a weekend, use the below code instead.
=ArrayFormula(if(ifna(match(K2:K,L2:L,0)*(weekday(K2:K100)=7)+(weekday(K2:K100)=1)),TRUE,))I am trying to use this formula to fill in dates for a school project.
But I am having a problem with it, i.e., skipping over the Fridays and leaving the Sundays.
Hi, Noah,
If you don’t want to skip weekends, try something like this.
=ArrayFormula(to_date(sequence(10,1,C2)))I’m using this formula to populate the dates for an upcoming class. But we also have 5 Saturdays during that date range that I need to include.
=ArrayFormula(WORKDAY.INTL(D4,sequence(36,1),"0101111",Holidays!A2:A49))Is there any way to inject a list of dates into the list of dates this creates?
Thank you.
Hi, Marc,
Insert those days in Holidays!B1:B5.
You can include them in the formula as shown below.
=SORT({WORKDAY.INTL(D4,sequence(36,1),"0101111",Holidays!A2:A49);Holidays!B1:B5})Prashanth, is there a way to modify this formula to exclude weekend dates and also insert a blank line for weekly subtotals?
Try the following formula (select the result and apply Format > Number > Date).
=ARRAYFORMULA(LET(range, WORKDAY.INTL(C2, SEQUENCE(30), 1),
header, TOCOL(, 1),
dt, range,
at, 11,
helper, WEEKNUM(dt,at),
REDUCE(header,TOCOL(UNIQUE(helper),3),
LAMBDA(a,v,IFERROR(VSTACK(a,FILTER(range,helper=v),))))
))
In this WORKDAY.INTL part, i.e.,
WORKDAY.INTL(C2, SEQUENCE(30), 1), replace C2 with the cell reference of the starting date cell and SEQUENCE(30) with the number of rows you want to fill the date.This formula is already featured in my tutorial here where I used a table range instead of the WORKDAY.INTL formula.