To fill while excluding Saturdays and Sundays, use the Fill feature's "Fill Weekdays" option — one click. But if you want to exclude only Sundays, which option do you use?
Fill dates excluding Sundays
No. The Fill feature can only skip both Saturdays and Sundays (i.e., "Fill Weekdays") and cannot skip only Sundays.
- Enter the first date manually.
- Then enter the following formula and drag down:
=IF(WEEKDAY(A1,2)=6,A1+2,A1+1)
=IF(WEEKDAY(A1,2)=6,A1+2,A1+1)
- WEEKDAY returns the weekday number corresponding to a date. By default this is an integer from 1 (Sunday) to 7 (Saturday).
- Syntax: WEEKDAY(serial_number,[return_type])
- The WEEKDAY function has the following arguments:
- Serial_number Required. The serial value representing the date to evaluate. Dates must be entered using the DATE function or be the result of other formulas or functions. For example, DATE(2008,5,23) represents May 23, 2008. Entering dates as text may cause problems.
- Return_type Optional. A number that determines the type of value returned.
| Return_type | Return values |
|---|---|
| 1 or omitted |
Numbers 1 (Sunday) through 7 (Saturday). Same behavior as earlier versions of Microsoft Excel. |
| 2 |
Numbers 1 (Monday) through 7 (Sunday). |
| 3 | Numbers 0 (Monday) through 6 (Saturday). |
| 11 |
Numbers 1 (Monday) through 7 (Sunday). |
| 12 | Numbers 1 (Tuesday) through 7 (Monday). |
| 13 | Numbers 1 (Wednesday) through 7 (Tuesday). |
| 14 | Numbers 1 (Thursday) through 7 (Wednesday). |
| 15 | Numbers 1 (Friday) through 7 (Thursday). |
| 16 | Numbers 1 (Saturday) through 7 (Friday). |
| 17 | Numbers 1 (Sunday) to 7 (Saturday). |
- So, WEEKDAY(A1,2)=6 checks whether the previous day is Saturday.
- IF(WEEKDAY(A1,2)=6,A1+2,A1+1) means if the previous day is Saturday, return A1+2; otherwise return A1+1.
- This ultimately ignores all Sundays.
