Calculating the Number of Workdays between Two Dates

Preview

You may need to calculate the number of workdays between the start time and the end time.

Implementation Method

The number of workdays between two dates is calculated by subtracting the marked number of these two dates.

Download sample data: Sample Data.zip

Processing Date Table

1. The content format of the Date Table is shown in the following figure.

If your holiday arrangement is different from the table, you can modify it.

2. Create an analysis subject named Number of Workdays Between Two Dates, upload the Date Table, and rename it to Holiday Marked Table.

3. Add a formula column to mark holidays as 0 and non-holidays as 1.

4. Add a Summary Column to accumulate the values of the 0/1 Marking field in ascending order by Date.

The result is shown in the following figure. The Mark Number field can be used to calculate the number of workdays between two dates.

For example, to calculate the number of workdays between 2011/03/14 and 2011/03/08, you can subtract the corresponding mark numbers and add 1: (45 - 41) + 1 = 5 days.

Special case: To calculate the number of workdays between 2011/03/09 and 2011/03/05 (the start date is a holiday), you can simply subtract the corresponding mark numbers: 42 - 39 = 3 days.

Calculating the Number of Workdays

1. Upload the Personnel Table with all fields selected by default. 

2. Click Join. Select the fields Date, Holiday or Not, and Mark Number in the Holiday Marked Table and choose Start Time and Date as Merge Basis, as shown in the following figure.

By doing so, you mark the Start Time field in the Personnel Table with numbers and determine whether the Start Time is a holiday.

3. Click Field Settings. Rename the Mark Number field to Mark Number of Start Time and the Holiday or Not field to Whether Start Time is Holiday.

4. Click Join. Select the fields Date and Mark Number in the Holiday Marked Table and choose End Time and Date as Merge Basis, as shown in the following figure.

By doing so, you match the End Time field with the Mark Number field.

5. Click Field Settings and rename the Mark Number field to Mark Number of End Time.

6. Add a Formula Column to calculate the number of workdays between the Start Time and the End Time, as shown in the following figure. /

• If the Start Time is a holiday, enter the formula: IF(Whether Start Time is Holiday="Holiday", Mark Number of End Time-Mark Number of Start Time, Mark Number of End Time-Mark Number of Start Time)

• If the End Time is a holiday, enter the formula: IF(Whether Start Time is Holiday="Holiday", Mark Number of End Time-Mark Number of Start Time, Mark Number of End Time-Mark Number of Start Time+1)

Click Save All and Update.

Demonstration

The result is shown in the following figure. According to this figure, James worked for 43 days from 2016/07/04 to 2016/08/31.