A fixed 30-day shift is not a month shift
Month lengths and leap years differ. Use EOMONTH instead of A2+30.
Choose a working cutoff at month end
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill C2:C5 with the final eligible working date on or before the last calendar date of the month reached by shifting the date in column A by the whole-month offset in column B. Use the text weekly mask G2 and office-closure list G4:G7.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: C2, C3, C4, C5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Shift to a target month, locate its calendar end, then move backward to the final eligible date.
THE QUICK ANSWER01
EOMONTH finds the shifted month end. Starting one calendar day later and moving back one working day includes the month end itself when it is eligible.
ƒx
=WORKDAY.INTL(EOMONTH(A2,B2)+1,-1,$G$2,$G$4:$G$7)For the example data
C2 2026-01-29C3 2026-02-26C4 2026-10-30C5 2027-01-29
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Why adding one day matters
WORKDAY.INTL excludes its starting date for a nonzero offset. Moving back directly from a working month end would skip the correct date. Start from the following date to make the month end eligible.
A declared office calendar
This scheduling exercise uses invented office closures. For an operational schedule, supply the applicable organization calendar.
Input boundary
Source dates are whole numeric Excel serials between 2000-01-01 and 2099-12-31. Month shifts are whole numbers from -24 through 24. The text weekend mask must leave at least one working weekday; closure dates may be blank or whole numeric dates.
Read the full step-by-step walkthrough
- Choose the target month
Column A can contain any whole date in the starting month. Column B shifts by calendar months, including zero and negative offsets.
- Let the calendar choose its last day
EOMONTH handles 28, 29, 30 and 31-day months and changes of year. Do not add a fixed number of days to guess a month end.
- Start just beyond the boundary
Add one calendar day to the month end so moving backward can return that end date when it is already eligible.
- Move backward with the working calendar
The offset -1 finds the preceding eligible date. The shared mask and closure list can push it back several days. The result is the last eligible date on or before month end, even if unusually long closures would move it into a preceding month.
- Fill and test different month shapes
Keep both calendar references fixed while the date and month-offset row move. Test leap February, a year change and consecutive closures.
Things to watch
A working month end is skipped
Starting at EOMONTH(A2,B2) and moving back one workday excludes that end date. Add one calendar day before moving backward.
Only ordinary weekends are excluded
The capstone must also use the supplied weekly pattern and closure list.
About this example
Formula entry, filling, all authored datasets, walkthrough controls and original/current/frozen XLSX downloads were checked in Chrome on October 17, 2026. Dates remain numeric Excel 1900-system serials with readable formatting. Closure dates are invented office-calendar examples. Native Excel and Google Sheets recalculation remain unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.