Have you ever needed to generate a sequence of dates in Google Sheets, but wanted to exclude weekends? Well, you’re in luck! In this article, we’ll explore how you can automatically fill weekdays, excluding weekends, using a simple formula. So let’s dive right in!
Sequential Dates Excluding Weekends – Non-Array Formula
Before we delve into the array formula, let’s start with a non-array formula that allows you to drag and drop the formula to fill weekdays in rows.
To begin, choose a specific date in a cell, for example, Tuesday, 1 Oct 2019. Then, input either of the following formulas in the next cell and drag it down:
=WORKDAY.INTL($C$2, ROW(A1), 1)
=WORKDAY.INTL($C$2, ROW(A1), "0000011")The formula will automatically fill in the dates from Monday to Friday, skipping weekends. It’s as simple as that!
How WORKDAY.INTL Generates Weekdays in Google Sheets
To achieve our goal of populating sequential dates while excluding weekends, we’ll be using the WORKDAY.INTL function in Google Sheets. This function allows us to retrieve the date after a specified number of workdays.
For a more detailed tutorial on the WORKDAY.INTL function, you can refer to my comprehensive date functions guide available here.
Now, let’s break down the formula and understand how it works.
The syntax of the WORKDAY.INTL function is as follows:
WORKDAY.INTL(start_date, num_days, [weekend], [holidays])In our case, we don’t need the last argument, “holidays,” so you can simply disregard it.
Let’s focus on the first three arguments and see how they are used in the formula.
WORKDAY.INTL – Explanation of Required Arguments and Their Use in Formula
-
start_date: Tuesday, 1 Oct 2019- This is the starting date from which the number of days will advance.
-
num_days: 1- This represents the number of working days to advance from the 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 and Sunday |
To better understand the formula’s behavior, let’s look at some examples.
- Formula in C3:
=WORKDAY.INTL($C$2, ROW(A1), 1)(advances 1 day from C2) - Formula in C4:
=WORKDAY.INTL($C$2, ROW(A2), 1)(advances 2 days from C2) - Formula in C5:
=WORKDAY.INTL($C$2, ROW(A3), 1)(advances 3 days from C2)
In cell C2, we have Tuesday, 1 Oct 2019 as the start_date. In the formula in cell C3, the num_days is 1 (ROW(1)), so the formula advances by 1 working day while skipping weekends, resulting in Wednesday, 2 Oct 2019.
Similarly, in cell C4, the num_days is 2 (ROW(A2)), so the formula returns Thursday, 3 Oct 2019. And in cell C5, the num_days is 3 (ROW(A3)), leading to the formula returning the date Friday, 4 Oct 2019. As you can see, weekends are automatically skipped.
This is how the formula can help you generate sequential dates, excluding weekends, in a non-array form.
Sequential Dates Excluding Weekends – Array Formula
If you need to generate a longer sequence of dates, you can use an array formula. By inputting sequential numbers from 1 to the desired number of days in the num_days argument, the formula can generate an array of sequential dates, excluding weekends.
To implement this, replace ROW(A1) in the previous formula 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. Additionally, 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:
=ArrayFormula(WORKDAY.INTL(C2, SEQUENCE(13, 1), 1))
=ArrayFormula(WORKDAY.INTL(C2, ROW(A1:A13), 1))And voila! You have a sequence of dates, excluding weekends, in your Google Sheets.
Resources
This tutorial covered the process of populating sequential dates while excluding specific weekends in Google Sheets using the WORKDAY.INTL function. Additionally, we explored the use of ROW and SEQUENCE functions. If you’re interested in learning more about working with dates in Google Sheets, here are some other topics you might find helpful:
- 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
I hope you found this tutorial helpful and that it makes your date-related tasks in Google Sheets a breeze! For more tips and tricks, be sure to visit Crawlan.com.
