Working days between 2 dates

Purpose of this recipe

You have a table storing some kind of business task that has a start date and an end date. You want to calculate the duration of this task as working days, that is, excluding weekends. This can be achieved using calculated fields. We’ll create a duration field in AppGini, with its data type set to Integer and set it as a calculated field, with the query below.

Files to edit

In AppGini, add the following SQL code to the query box in the calculated field tab for the duration field:

1
2
3
4
5
6
7

SELECT FLOOR(
    ABS(DATEDIFF(end_date, start_date)) + 1
    - ABS(DATEDIFF(ADDDATE(end_date, INTERVAL 1 - DAYOFWEEK(end_date) DAY),
                   ADDDATE(start_date, INTERVAL 1 - DAYOFWEEK(start_date) DAY))) / 7 * 2
    - (DAYOFWEEK(IF(start_date < end_date, start_date, end_date)) = 1)
    - (DAYOFWEEK(IF(start_date > end_date, start_date, end_date)) = 7)
) FROM `tablename` WHERE `id`='%ID%'

In the above code, replace start_date and end_date with the actual name of the start and end date fields, respectively. Also, replace tablename with the actual table name, and id with the actual name of the primary key field.

How this query works

The query above calculates the number of working days between two dates. Here’s a breakdown of how it works:

1. Line 2 (ABS(DATEDIFF(end_date, start_date)) + 1): This calculates the total number of days between the two dates.
2. Then in lines 3 and 4, we subtract the number of weekends between the two dates. This is done by calculating the number of Saturdays and Sundays between the two dates and subtracting them from the total number of days.
3. Finally, in lines 5 and 6, we subtract the number of weekends that fall on the start and end dates. This is done by checking if the start date is a Sunday and if the end date is a Saturday, and subtracting 1 from the total number of days if either of these conditions is true.
4. The result is the number of working days between the two dates.
5. The FLOOR function is used to round down the result to the nearest integer.
6. The WHERE clause at the end of the query ensures that the calculation is done for the current record only.
7. The %ID% placeholder is replaced by the actual primary key value of the current record by AppGini when the query is executed.
8. The result is stored in the duration field for each record.

Now, when you view a record in your AppGini app, you’ll see the number of working days between the start and end dates in the duration field.