How to Use Excel WORKDAY Formula with a Fixed Holiday Range
Question details
The user needs to copy a WORKDAY formula down a column while keeping the reference to the holiday list fixed, allowing only the start date to change dynamically per row.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating delivery dates or project deadlines across multiple rows using the WORKDAY function without manually adjusting the holiday range for each cell.
- Observed behavior
- When the formula is dragged down using relative references, the holiday range shifts downward, leading to incorrect workday calculations and missed holiday exclusions.
Ensure your holiday dates are listed in a continuous range and properly formatted as dates, rather than text. If they are on a different worksheet, take note of the sheet name before writing your formula.
Use Absolute References ($) for the Holiday Range
Lock the cells containing your holiday list by converting them into absolute references. This prevents the range from shifting when you drag the formula down to other rows.
In spreadsheet formulas, relative references (like B1:B10) change automatically when you copy them to another cell. To stop this behavior for your holiday list, you must use absolute references by adding dollar signs (like $B$1:$B$10).
Select the cell where you want to output your first date calculation. Type `=WORKDAY(` and click on your start date cell (e.g., C1), followed by a comma and the number of days (e.g., 15).
Type another comma, then use your mouse to highlight the range containing your holiday dates (e.g., Holiday!B1:B10).
With the holiday range highlighted in the formula bar, press the `F4` key on your keyboard. This will automatically add dollar signs to the range, changing it to `Holiday!$B$1:$B$10`. Close the parenthesis and press Enter.
Click the small square at the bottom-right corner of your calculated cell (the fill handle) and drag it down to apply the formula to the remaining rows.
Use a Named Range for the Holiday List
Instead of manually applying dollar signs, you can assign a specific name to your holiday list. Named ranges act as absolute references by default.
Simplify Date Calculations with WPS Spreadsheet
WPS Spreadsheet fully supports the WORKDAY function, absolute referencing, and Named Ranges. You can seamlessly calculate business days, project deadlines, and exclude holidays with complete Microsoft Excel compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your start dates and holiday list.
- 2. Start the formula: Select the target cell, type `=WORKDAY(`, and click on the cell containing your starting request date.
- 3. Add days and holiday range: Enter the number of working days to add, type a comma, and highlight your list of holiday dates.
- 4. Lock the holiday range: Press the F4 key to lock the holiday range with absolute references (e.g., `$B$1:$B$10`), then press Enter to calculate the result.
- 5. Drag to fill: Click and drag the fill handle down to apply the fixed-holiday calculation to all other rows.
Frequently Asked Questions
Why does my WORKDAY formula return a #VALUE! error?
This error typically occurs if either your start date, the number of days, or the dates in your holiday range are formatted as text instead of valid dates. Check your holiday list and ensure all cells are properly recognized as 'Date' format.
How do I exclude weekends other than Saturday and Sunday?
If your work week does not follow a standard Saturday/Sunday weekend schedule, you should use the WORKDAY.INTL function. It allows you to specify exactly which days of the week are considered weekends, in addition to using your fixed holiday range.
Can I use an entire column as my holiday range?
Yes, you can reference an entire column (e.g., =WORKDAY(C1, 15, Holiday!$B:$B)). This is an absolute reference that will not shift, and it allows you to add new holidays to the bottom of the column later without updating the formula. However, referencing entire columns can slightly impact calculation speed in very large files.
