Overview
In attendance and leave management, weekends and holidays usually need to be excluded when calculating the actual number of leave days.
This article shows how to automatically exclude holidays and weekends from a leave period and, when applicable, include additional workdays that normally fall on weekends. This helps calculate the Actual Leave Days accurately for attendance tracking, payroll calculation, and other scenarios.
Solution Design
The solution works as follows:
In the Holiday Calendar, enter the holidays applicable to your organization for the year in advance. Use a formula to determine whether each holiday falls on a weekend and add a marker.
In the Additional Workday Calendar, enter any normally non-working weekend dates that your organization designates as workdays, and add a marker to each date.
In the Leave Request form, use the
NETWORKDAYSfunction to calculate the number of complete working days between the leave start date and leave end date. Then subtract holidays that fall on weekdays and add any weekend dates designated as additional workdays to calculate the Actual Leave Days.
Demonstration
Take Independence Day in 2026 as an example. Here July 3 is entered in the Holiday Calendar as the observed holiday. If an employee requests leave from June 29, 2026 to July 10, 2026, the Actual Leave Days are 9.
How to Set Up
Step 1 Designing the Forms
1. Create a form named Leave Request and add the following fields:
Field Name | Field Type |
Leave Start Date | Date&Time |
Leave End Date | Date&Time |
Actual Leave Days | Number |
Subform Row Count | Number |
Date Details | SubForm |
No. | Subfield - Single Line |
Date | Subfield - Date&Time |
Holiday Check | Subfield - Number |
Additional Workday Check | Subfield - Number |
2. Create a form named Holiday Calendar and add the following fields:
Field Name | Field Type |
Holiday Date | Date&Time |
Holiday Marker | Number |
3. Create a form named Additional Workday Calendar and add the following fields:
Field Name | Field Type |
Additional Workday Date | Date&Time |
Additional Workday Marker | Number |
4. Create an auxiliary form named Subform Base Table and add the following fields:
Field Name | Field Type |
Total Rows | Number |
Row Data | SubForm |
No. | Subfield - Single Line |
Number | Subfield - Number |
Step 2 Setting the Auxiliary Forms
Holiday Calendar
1. Select the Holiday Marker field. Go to Field Properties > Initial Value, select Formula, and click Edit.
2. In the formula editor, set the formula for Holiday Marker as follows: IF(OR(TEXT(DATE(Holiday Date),'E')==0,TEXT(DATE(Holiday Date),'E')==6),0,1)
If the holiday falls on Sunday or Saturday, Holiday Marker is set to 0; otherwise, it is set to 1. Click OK to save the formula.
3. Go to Manage Data and enter the holidays applicable to your organization for the year.
Additional Workday Calendar
1. Select the Additional Workday Marker field. Go to Field Properties > Initial Value, select Custom, and set the initial value to 1：
2. Go to Manage Data and enter any additional workdays applicable to your organization.
Subform Base Table
1. Go to Subform Base Table > Manage Data > Import and import the sample data from the attachment: 200-row Subform Helper Table.xlsx
2. When importing the data, make sure the sample data is mapped to the corresponding form fields as shown below:
Step 3 Setting Data Linkage
1. Open the Leave Request form and select the entire Date Details SubForm. Go to Field Properties > Initial Value, select Data Linkage, and click Data Linkage Settings.
2. On the Data Linkage Settings page, configure the following:
Linked Form: Subform Base Table
Condition:
Total Rowsequals toSubform Row CountTriggered Linkage:
Date Detailsdisplays the value ofSubformAdd the
No.subfield and display the value of theNo.subfield.
Click OK to save the settings.
3. Set Data Linkage for Date Details.Holiday Check as follows:
Linked Form: Holiday Calendar
Condition:
Holiday DateequalsDate Details.DateTriggered Linkage:
Date Details.Holiday Checkdisplays the value ofHoliday Marker
Click OK to save the settings.
4. Set Data Linkage for Date Details.Additional Workday Check as follows:
Linked Form: Additional Workday Calendar
Condition:
Additional Workday Dateequals toDate Details.DateTriggered Linkage:
Date Details.Additional Workday Checkdisplays the value ofAdditional Workday Marker
Click OK to save the settings.
Step 4 Setting Formulas
1. Open the Leave Request form and select the Subform Row Count field. Go to Field Properties > Initial Value, select Formula, and click Edit.
2. In the formula editor, set the formula for Subform Row Count as follows:
DAYS(Leave End Date,Leave Start Date)+1
This formula calculates the total number of calendar days from the leave start date through the leave end date and uses the result as the number of rows in Date Details. Click OK to save the formula.
3. Set the formula for other fields as follows:
Date Details.Date:
IF(Date Details.No.=='1',Leave Start Date,IF(VALUE(Date Details.No.)<=Subform Row Count+1,DATE(YEAR(Leave Start Date),MONTH(Leave Start Date),DAY(Leave Start Date)+VALUE(Date Details.No.)-1),''))When the row number in Date Details is1, the date in the first row is set to Leave Start Date. For subsequent rows, one day is added based on the row number until all required rows are used.Actual Leave Days:
NETWORKDAYS(Leave Start Date,Leave End Date) -SUM(Date Details.Holiday Check) +SUM(Date Details.Additional Workday Check)This formula first calculates the number of working days in the leave period, excluding Saturdays and Sundays. It then subtracts holidays that fall on weekdays and adds any normally non-working weekend dates that the organization designates as additional workdays. The final result is the Actual Leave Days.
Step 5 Setting Field Visibility
1. Subform Row Count and Date Details are helper fields and do not need to be displayed when employees submit leave requests. Select each field, go to Field Properties > Field Permissions, and set the field to invisible.
2. After hiding the helper fields, go to Form Properties > Hidden Field Default Value and select Always Recalculate.
