How to Count Monday Through Saturday and Add Parental Leave in Excel
Question details
The user needs to configure an attendance tracker to count Monday through Saturday as working days, treat only Sunday as a weekend, and integrate a new "Parental Leave" category with unique colors and summary calculations.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Modifying an employee attendance or leave tracking workbook to support a 6-day workweek and track specific employee absence types.
- Observed behavior
- Standard network days formulas default to Saturday and Sunday weekends, and the calendar formatting does not natively support tracking or highlighting the new Parental Leave category.
Create a duplicate copy of your existing attendance tracker before adjusting complex formulas and conditional formatting rules to ensure you can revert if the calendar layout breaks.
Use NETWORKDAYS.INTL and Update Conditional Formatting
Modify the core workday calculation to use a custom weekend parameter, adjust the calendar formatting to grey out only Sundays, and add the new leave type to the summary table.
The NETWORKDAYS.INTL function is designed to handle non-standard workweeks by allowing you to define exactly which days are considered weekends. By passing the weekend parameter "11", Excel will treat only Sunday as a non-working day.
Select the cell calculating total workdays. Replace the existing formula with `=NETWORKDAYS.INTL(start_date, end_date, 11, holidays)`. For example: `=NETWORKDAYS.INTL(DATE(Calendar_Year,1,1), EDATE(DATE(Calendar_Year,1,1),12)-1, 11)`.
Go to Home > Conditional Formatting > Manage Rules. Locate the rule that greys out weekends and edit it to target only Sundays and holidays. Use a formula like `=OR(LEFT(C$5,2)="SU",COUNTIF(lstHolidays,C6)>0)`.
Navigate to your Leave Types reference table and type in 'Parental Leave'. Copy the formula structure from existing leave-summary columns and update the criteria to reference the new Parental Leave cell.
Choose a specific fill color for Parental Leave. Go to Home > Conditional Formatting > New Rule. Create a formula-based rule using `COUNTIFS` to match dates marked as Parental Leave and apply your chosen fill color.
Track 6-Day Workweeks and Leaves in WPS Spreadsheet
WPS Spreadsheet fully supports the NETWORKDAYS.INTL function and advanced conditional formatting, making it effortless to build, manage, and customize attendance trackers.
- 1. Open Your Tracker: Launch WPS Spreadsheet and open your existing attendance or leave tracking workbook.
- 2. Calculate Custom Workdays: Click the summary cell and enter `=NETWORKDAYS.INTL(start_date, end_date, 11)` to calculate Monday to Saturday workdays.
- 3. Customize Rules: Navigate to the Home tab and click Conditional Formatting to set up highlighting rules for Sundays and Parental Leave.
- 4. Save Seamlessly: Save your updated file in .xlsx format to ensure it remains completely compatible with other spreadsheet software.
Frequently Asked Questions
Can I use the standard NETWORKDAYS function instead?
No, the standard `NETWORKDAYS` function strictly defines weekends as both Saturday and Sunday. To calculate a 6-day workweek, you must use `NETWORKDAYS.INTL` with the weekend argument set to 11.
Why is my conditional formatting rule not coloring Parental Leave correctly?
Ensure your `COUNTIFS` formula correctly locks the cell references using the dollar sign ($), such as `$C6`. Also, check the Conditional Formatting Rules Manager to ensure no other conflicting rule is placed higher in the hierarchy.
How do I format both Sunday and holidays in the calendar?
You can combine conditions using the `OR` function in your conditional formatting rule. For example, `=OR(LEFT(C$5,2)="SU",COUNTIF(lstHolidays,C6)>0)` will return TRUE if the day is Sunday or if the date exists in your named range 'lstHolidays'.
