Table of Contents
You can use the following formula in Excel to create a list that contains weekend dates only:
=WORKDAY.INTL(A2,1,"1111100")
This particular formula assumes that the first weekend date is manually entered in cell A2. It then generates the next weekend date that comes after the date in cell A2.
You can click and drag this formula down to as many cells as you’d like in an Excel spreadsheet to automatically generate a list of weekends only.
The following example shows how to use this formula in practice.
Example: How to Create List of Weekends Only in Excel
Suppose we would like to create a list of weekend dates only in Excel, starting with 1/6/2024, which is a Saturday.
We can start by typing this date into cell A2:
We can then type the following formula into cell A3 to automatically generate the next weekend date:
=WORKDAY.INTL(A2,1,"1111100")
We can then click and drag this formula down to as many cells as we would like in column A:
Column A now contains a list of weekend dates, starting with 1/6/2024.
We can verify that each of the dates in the list occur on a weekend (Saturday or Sunday) by viewing the calendar for the month of January 2024:
Note that each date shown in the list in Excel occurs on a Saturday or Sunday on the calendar.
How This Formula Works
Recall the formula that we used to generate a list of weekends:
=WORKDAY.INTL(A2,1,"1111100")
This formula utilizes the WORKDAY.INTL function, which uses the following syntax:
WORKDAY.INTL(start_date, days, [weekend])
where:
- start_date: The start date
- days: Number of days before or after the start date
- weekend: A string pattern where 1 = non-workday, 0 = workday
By using the pattern “1111100” for the weekend argument, we specify that Monday through Friday should not be included when adding a day to the existing day in cell A2.
This formula allows us to generate a list of dates that only occur on the weekends.
Note: You can find the complete documentation for the WORKDAY.INTL function in Excel .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: