Number of working days between two dates

1.1. Expected effect

A group of interns came to the company, and their wages were settled according to the number of days they worked. We hope to calculate how many working days they have worked through "start time" and "end time".

1.2 Implementation ideas

By marking a date and subtracting the number of marks of two dates, you can obtain the number of working days between two dates.

2. Operation mode

Download sample data for this article:130.sample data.rar

2.1 Processing date table

1) The content format of the date table is shown in the following table:

If the user's own holiday time is different from the table, it can be changed.

2) Upload the date table to finebi. Add the self-service dataset "holiday marking table" and check all data in the "DateChart". As shown in the figure below:

3) Add a new column to mark the date with 0 and 1. 0 for holidays and 1 for non holidays. As shown in the figure below:

4) Add a new column called "Accumulation mark". As shown in the figure below:

The table after accumulation is shown in the figure below. You can directly calculate the working days between two dates through the "Accumulation mark" field.

For example, the number of working days between March 14, 2011 and March 8, 2011 only needs to be subtracted by the corresponding "Accumulation mark" value: (45-41) + 1 = 5 days.

2.2 Calculation of working days

1) Upload the "NameChart", create a new self-service dataset, and check all fields of the "NameChart". As shown in the figure below:

2) Add left and right merge. Select the "TIME" and "Cumulative mark" field of the holiday mark table, and select "start time" and "TIME" according to the combination. As shown in the figure below:

This step corresponds each start time to their marks.

3) Change the field "Accumulation mark" to "start time mark". As shown in the figure below:

4) Similarly, make another left and right merge. Select the "TIME" field and "Cumulative mark" field in the holiday mark table, and select the "end time" and "TIME" according to the combination, as shown in the following figure:

This step corresponds the end time to their marks.

5) Change the "holiday marking table - accumulation mark" to "end time mark".

6) Add a new column to calculate the number of working days between the start time and the end time, as shown in the following figure:

Subtract the two marks to get the number of working days.

7) Save the self-service dataset.

2.3 View the effect

The calculated working days are shown in the figure below. For example, James interned for 43 working days from August 31, 2016 to July 4, 2016.