Excel Trick: Create Custom Day Sequences With WORKDAY.INTL Function

Cre­at­ing a cus­tom sequence of days in Excel can stream­line sched­ul­ing for spe­cif­ic busi­ness needs or project time­lines. By using the WORKDAY.INTL func­tion, you can gen­er­ate sequences that include only cer­tain week­days, allow­ing you to build sched­ules that skip spe­cif­ic days like Fri­days or only include par­tic­u­lar days like Mon­days, Wednes­days, and Fridays.

This tech­nique is espe­cial­ly valu­able for plan­ning recur­ring meet­ings, shift sched­ules, or deliv­ery dates that fol­low non-stan­dard patterns.

In my video, I demon­strate how to cre­ate these cus­tom day sequences in Excel:

Standard Weekday Sequences in Excel

Excel pro­vides a sim­ple built-in method for cre­at­ing stan­dard week­day sequences (Mon­day through Fri­day). When you need a list of dates that excludes week­ends, you can use Excel’s fill han­dle with the “Fill Week­days” option. This approach is straightforward:

1. Enter your start­ing date in a cell
2. Hold the right mouse but­ton and drag down as far as needed
3. Release and select “Fill Week­days” from the con­text menu

This cre­ates a sequence that auto­mat­i­cal­ly skips Sat­ur­days and Sun­days, giv­ing you only the stan­dard work­ing days. How­ev­er, this default method is lim­it­ed to the stan­dard Mon­day-Fri­day work week and does­n’t allow for customization.

Using WORKDAY.INTL for Custom Day Sequences

For more flex­i­bil­i­ty in day selec­tion, Excel’s WORKDAY.INTL func­tion becomes invalu­able. This pow­er­ful func­tion allows you to define which days of the week should be con­sid­ered week­ends (non-work­ing days), effec­tive­ly let­ting you cre­ate sequences with only your desired days.

The basic syn­tax of the WORKDAY.INTL func­tion is:

WORKDAY.INTL(start_date, days, [weekend], [holidays])

Where:

• start_date: The ini­tial date from which to begin counting
• days: The num­ber of work­ing days to add to the start date
• week­end: A para­me­ter that defines which days are con­sid­ered weekends
• hol­i­days: An option­al para­me­ter for spe­cif­ic hol­i­day dates to exclude

Weekend Parameter Options

The week­end para­me­ter is what gives this func­tion its flex­i­bil­i­ty. Excel offers mul­ti­ple ways to spec­i­fy which days should be treat­ed as non-work­ing days:

1. Using pre­de­fined week­end codes (1–7 for dif­fer­ent stan­dard week­end patterns)
2. Spec­i­fy­ing a sin­gle day of the week as a weekend
3. Cre­at­ing a cus­tom 7‑character text string of 1s and 0s

The third option is the most ver­sa­tile and the focus of my demon­stra­tion. The 7‑character string rep­re­sents the days of the week start­ing with Mon­day (posi­tion 1) through Sun­day (posi­tion 7). Using “0” marks a work­ing day, while “1” marks a week­end/non-work­ing day.

Creating Custom Day Sequences

To cre­ate a sequence that excludes spe­cif­ic days, we can lever­age the text string week­end para­me­ter. For exam­ple, if we want to exclude Fri­days, Sat­ur­days, and Sun­days from our sequence, we would use:

=WORKDAY.INTL(start_date, 1, "0000111")

In this string, the “1“s in posi­tions 5, 6, and 7 rep­re­sent Fri­day, Sat­ur­day, and Sun­day as non-work­ing days, while the “0“s in posi­tions 1–4 indi­cate Mon­day through Thurs­day as work­ing days. By set­ting the days para­me­ter to 1, each appli­ca­tion of the func­tion adds exact­ly one work­ing day to the sequence.

When this for­mu­la is dragged down, it cre­ates a sequence of dates that includes only Mon­day through Thurs­day, skip­ping over the days we’ve des­ig­nat­ed as “week­ends.”

Example: Monday-Wednesday-Friday Sequence

If we want to cre­ate a sequence that includes only Mon­days, Wednes­days, and Fri­days, we would define all oth­er days as non-work­ing days:

=WORKDAY.INTL(start_date, 1, "0101011")

This pat­tern sets Tues­day, Thurs­day, Sat­ur­day, and Sun­day as non-work­ing days (rep­re­sent­ed by “1“s in posi­tions 2, 4, 6, and 7), while Mon­day, Wednes­day, and Fri­day remain as work­ing days (with “0“s in posi­tions 1, 3, and 5). When applied and dragged down, this for­mu­la cre­ates a sequence that cycles through only the three days we want: Mon­day, Wednes­day, Fri­day, Mon­day, Wednes­day, Fri­day, and so on.

Practical Applications

This tech­nique has numer­ous prac­ti­cal appli­ca­tions in busi­ness and per­son­al planning:

• Meet­ing sched­ules for recur­ring team meet­ings on spe­cif­ic days
• Class or train­ing sched­ules that occur on select days of the week
• Shift work pat­terns for employ­ees with cus­tom work­ing days
• Deliv­ery sched­ules for ser­vices that oper­ate only on cer­tain days
• Pay­ment or billing cycles that fol­low spe­cif­ic day patterns

By mas­ter­ing the WORKDAY.INTL func­tion with cus­tom week­end para­me­ters, you can cre­ate high­ly spe­cial­ized date sequences that match exact­ly the pat­tern you need, with­out hav­ing to man­u­al­ly select or fil­ter dates.

Additional Considerations

While the exam­ples in my demon­stra­tion focus on the week­end para­me­ter, remem­ber that the WORKDAY.INTL func­tion also accepts a hol­i­days para­me­ter which can fur­ther refine your date sequences by exclud­ing spe­cif­ic hol­i­day dates.

For more com­plex sched­ul­ing needs, you might com­bine this func­tion with oth­er Excel fea­tures like con­di­tion­al for­mat­ting to high­light cer­tain dates or cus­tom num­ber for­mat­ting to dis­play the dates in your pre­ferred format.

The beau­ty of using func­tions like WORKDAY.INTL is that your sequences will auto­mat­i­cal­ly adjust if you change the start­ing date, mak­ing this a dynam­ic solu­tion for sched­ul­ing that can be eas­i­ly updat­ed as needed.