How to Calculate Half-Day Holidays with NETWORKDAYS in Excel
Question details
Calculate the number of working days in a period while correctly deducting both full-day and half-day holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user needs to find the remaining weekdays in a given month but has to account for partial holidays (like a half day on Christmas Eve).
- Observed behavior
- The standard NETWORKDAYS function only deducts full days for holidays, meaning half-day holidays cannot be natively processed without a supplemental formula.
Create a reference table with two columns: one for the holiday dates (e.g., column I) and another for the exclusion amount (e.g., column J), where you enter 1 for a full day and 0.5 for a half day.
Use the LET and SUMIFS Functions (Excel 2021 & Microsoft 365)
Utilize the LET function to streamline calculating remaining workdays for the current month while dynamically subtracting the correct holiday amounts.
The LET function allows you to assign names to calculation results. By defining variables like 'Today', 'EndOfMonth', and 'StartOfMonth', the formula becomes easier to read, updates dynamically, and processes faster.
Enter your holiday dates in cells I2:I6 and their corresponding exclusion values (1 or 0.5) in cells J2:J6.
Click on the empty cell where you want to display the final working days count.
Input the formula: =LET(Today,TODAY(),EndOfMonth,EOMONTH(Today,0),StartOfMonth,EOMONTH(Today,-1)+1,NETWORKDAYS(Today,EndOfMonth)-SUMIFS(J2:J6,I2:I6,">="&StartOfMonth,I2:I6,"<="&EndOfMonth))
Press Enter to calculate the remaining working days for the current month, correctly deducting both full and half days.
Use Standard NETWORKDAYS and SUMIFS Functions
For older versions of Excel that do not support the LET function, you can achieve the exact same result by combining NETWORKDAYS and SUMIFS directly.
Calculate Working Days Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like NETWORKDAYS, SUMIFS, and LET. You can seamlessly manage complex work schedules, project timelines, and half-day holiday calculations just as you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
- 2. Create a Holiday Table: List your holiday dates in one column and their values (1 for a full day, 0.5 for a half day) in an adjacent column.
- 3. Enter the Formula: Select an empty cell and input your NETWORKDAYS and SUMIFS combination formula.
- 4. Get Accurate Results: Press Enter. WPS Spreadsheet will instantly calculate the correct number of working days based on your inputs.
Frequently Asked Questions
Can the NETWORKDAYS function handle half-day holidays natively?
No, the built-in NETWORKDAYS and NETWORKDAYS.INTL functions only subtract whole days for holidays. You must use a workaround, such as subtracting a SUMIFS calculation, to handle fractional and half days.
What should I input for the holiday exclusion amount in the helper column?
You should enter 1 for a fully excluded day (like a standard public holiday) and 0.5 for a half-day holiday (like Christmas Eve). This enables the SUMIFS function to deduct the exact amount from your total working days.
How do I calculate working days for a custom date range instead of the current month?
To calculate a custom date range, replace the TODAY() and EOMONTH() functions in the formulas with direct absolute cell references that contain your desired start date and end date.
