This returns only Saturdays or Sundays as the formula is copied down. The list is dynamic – when start date is changed, the new dates are generated. One of the the arguments provided to WORKDAY.INTL is called “weekend”, and indicates which days are considered non-working days. The weekend argument can be provided as a number linked to a preconfigured list, or as a 7-character code that covers all seven days of the week, Monday through Saturday. This example uses the code option. In the code, 1’s represent weekend days (non-working days) and zeros represent work days, as illustrated with the table in D4:K5. We only want to to see Saturdays and Sundays in the output, so use 1 for all days Monday-Friday, and zero for Saturday and Sunday: The days argument is input as 1. As the formula is copied down, the WEEKDAY.INTL function returns the next date that is either Saturday or Sunday.
Another way
If you have an older version of Excel without the WEEKDAY.INTL function, you can an alternative formula based on the WEEKDAY function: By default, WEEKDAY will return 1 for Sunday and 7 for Saturday. If the result is 7, the result is B5+1 (Sunday). If not, the result is Which resolves like this, depending on day of week: B5+(7-2) = B5+5 <– Mon B5+(7-3) = B5+4 <– Tue B5+(7-4) = B5+3 <– Wed B5+(7-5) = B5+2 <– Thu B5+(7-6) = B5+1 <– Fri Note: you’ll need to supply a date at least one day before the first Saturday you want to generate.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
