Skip to main content

Celonis Product Documentation

WORKDAYS_BETWEEN
Description

The WORKDAYS_BETWEEN function determines the amount of workdays between two given dates. When the start date is before the the end date, the end date is not counted. When the start date is after the end date, the start date is not counted and a negative value is returned.

This function only considers the dates of the given timestamps. The time of day does not influence the result.

The WORKDAYS_BETWEEN function retrieves its information regarding valid workdays from the passed Calendar configuration. For the weekday and factory calendars any day with a (non-empty) shift is considered a workday.

When either the start or the end date are outside of the scope of the calendar, NULL is returned. For the factory calendar the scope contains the complete year containing the first defined shift (so also all days before the first shift in that year), the complete year containing the last defined shift and all years in between.

WORKDAYS_BETWEEN follows the same logic as the WORKDAYS_BETWEEN function in SAP HANA.

Syntax

The recommended way of using the WORKDAYS_BETWEEN function is the following:

  WORKDAYS_BETWEEN ( start_date, end_date, calendar_specification [, calendar_id_column])
 
  • start_date: Start date

  • end_date: End date

  • calendar_specification: One of WEEKDAY_CALENDAR, FACTORY_CALENDAR, WORKDAY_CALENDAR, or INTERSECT

  • calendar_id_column: Column to create a mapping between the respective activities and their used calendar specification. This is mandatory when using multiple calendar specifications. For more details, please take a look at the respective documentation of the DateTime Calendar.

Deprecated Behavior

With a configured calendar table the WORKDAYS_BETWEEN function can also be called in a different way. [ DEPRECATED | Use the newest version of WORKDAYS_BETWEEN instead.]

The calendar table can be configured in the Data Model Editor and applies to all calls of WORKDAYS_BETWEEN in the Data Model.

The calendar table has to have the following layout:

  • Calendar ID: Identifier of the working calendar

  • Year: Year of the calendar

  • Month 1: Working days of January bit encoded. 1 is a working day. 0 is a day off. Example of January 2019 with the weekends off: 1111001111100111110011111001111

  • Month 2: Working days of February.

  • ...

  • Month 12: Working days of December

  WORKDAYS_BETWEEN ( calendar_id, start_date, end_date [, INVALID_TO_NULL] )
 
  • calendar_id: The ID defines which calendar out of the calendar table is used.

  • start_date: Start date

  • end_date: End date

  • INVALID_TO_NULL: This flag prevents the fallback to DAYS_BETWEEN.

If the calendar ID does not exist in the calendar table or if one of the input dates lies outside of the range of the calendar, WORKDAYS_BETWEEN behaves like the DAYS_BETWEEN function and returns the number of days between the specified dates. In this case, a warning is issued.

When working with the deprecated version of this function, using the INVALID_TO_NULL flag will prevent the fallback to DAYS_BETWEEN, and NULL will be returned instead. This is the recommended behavior, since the fallback may lead to unexpected/unintuitive results.

Note

Please note that for the same two dates and the same WORKDAY_CALENDAR specification, there is a difference in using WORKDAYS_BETWEEN and subtracting two REMAP_TIMESTAMPS calls for these dates. WORKDAYS_BETWEEN always ignores the time of the day (e.g., 1970-01-01T23:59:59:999 is treated as 1970-01-01T00:00:00:000) while REMAP_TIMESTAMPS does not.

NULL handling

If any parameter is NULL, the result is NULL as well.

Exceptions

The new version of WORKDAYS_BETWEEN will not work if there are any gaps in the years covered by the respective WORKDAY_CALENDAR. An error will be returned if gaps are found in the workday calendar configuration.

Example

[1]

In this scenario, the workday difference between two dates is determined using a workday calendar. In both examples the weekend on the sixth and seventh is not counted. Note that in the second example, even though the time of day of the start and end date are very different, the absolute value of the result stays the same.

Query

Column1

         WORKDAYS_BETWEEN ( "TimestampTable"."Begin" , "TimestampTable"."End" , WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) )
        

Input

Output

TFACS

IDENT : string

JAHR : int

MON01 : string

MON02 : string

MON03 : string

MON04 : string

MON05 : string

MON06 : string

MON07 : string

MON08 : string

MON09 : string

MON10 : string

MON11 : string

MON12 : string

'BAY'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

TimestampTable

Begin : date

End : date

Tue Jan 02 2018 01:00:00.000

Mon Jan 08 2018 01:00:00.000

Mon Jan 08 2018 00:00:00.000

Tue Jan 02 2018 23:59:59.599

Result

Column1 : int

4

-4

[2]

The following example shows the behavior when either only the start or the end date is not a workday. As mentioned above, the input date at the later point in time is not counted. So when the start date is before the end date, the end date is counted, and when the end date is before the start date, the start date is not counted. This implies that: WORKDAYS_BETWEEN(start, end) = -WORKDAYS_BETWEEN(end, start) always holds.

Query

Column1

         WORKDAYS_BETWEEN ( "TimestampTable"."Begin" , "TimestampTable"."End" , WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) )
        

Input

Output

TFACS

IDENT : string

JAHR : int

MON01 : string

MON02 : string

MON03 : string

MON04 : string

MON05 : string

MON06 : string

MON07 : string

MON08 : string

MON09 : string

MON10 : string

MON11 : string

MON12 : string

'BAY'

2018

'0111100111110011111001111100111'

'1100111110011111001111100111'

'1100111110011111001111100111100'

'001111001111100111110011111001'

'0111001110100111110001111001110'

'100111110011111001111100111110'

'0111110011111001111100111110011'

'1110011111001101100111110011111'

'001111100111110011111001111100'

'1101100111110011111001111100111'

'010011111001111100111110011111'

'0011111001111100111110010011001'

TimestampTable

Begin : date

End : date

Mon Jan 01 2018 03:00:00.000

Tue Jan 02 2018 03:00:00.000

Tue Jan 02 2018 04:00:00.000

Mon Jan 01 2018 04:00:00.000

Fri Jan 05 2018 04:00:00.000

Sat Jan 06 2018 04:00:00.000

Sat Jan 06 2018 04:00:00.000

Fri Jan 05 2018 04:00:00.000

Result

Column1 : int

0

0

1

-1

[3]

The function also works with a weekday calendar. In the first example the third (Wednesday) is not counted. In the second example both the seventh (Sunday) and the third are not counted.

Query

Column1

         WORKDAYS_BETWEEN ( "TimestampTable"."Begin" , "TimestampTable"."End" , WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 ) )
        

Input

Output

TimestampTable

Begin : date

End : date

Tue Jan 02 2018 01:00:00.000

Sat Jan 06 2018 01:00:00.000

Mon Jan 08 2018 02:00:00.000

Mon Jan 01 2018 02:00:00.000

Result

Column1 : int

3

-5

[4]

Additionally, a factory calendar or an arbitrary intersection of these calendars can be used. In the following scenario, the first example does not count the third (Wednesday), because it does not appear in the weekday calendar and the fifth (Friday), because it does not appear in the factory calendar. The second example does not count the seventh (Sunday) because even though the day appears in both calendars, the shift on the day do not overlap and so it is not contained in the intersection.

Query

Column1

         WORKDAYS_BETWEEN ( "TimestampTable"."Begin" , "TimestampTable"."End" , INTERSECT ( WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 SUNDAY 13:00 - 15:00 ) , FACTORY_CALENDAR ( "FactoryTable"."START" , "FactoryTable"."END" ) ) )
        

Input

Output

FactoryTable

START : date

END : date

Tue Jan 02 2018 08:00:00.000

Tue Jan 02 2018 17:00:00.000

Wed Jan 03 2018 08:00:00.000

Wed Jan 03 2018 17:00:00.000

Thu Jan 04 2018 08:00:00.000

Thu Jan 04 2018 17:00:00.000

Sat Jan 06 2018 08:00:00.000

Sat Jan 06 2018 17:00:00.000

Sun Jan 07 2018 08:00:00.000

Sun Jan 07 2018 12:00:00.000

Mon Jan 08 2018 08:00:00.000

Mon Jan 08 2018 17:00:00.000

Tue Jan 09 2018 08:00:00.000

Tue Jan 09 2018 17:00:00.000

TimestampTable

Begin : date

End : date

Tue Jan 02 2018 00:00:00.000

Sat Jan 06 2018 00:00:00.000

Tue Jan 09 2018 00:00:00.000

Sat Jan 06 2018 00:00:00.000

Result

Column1 : int

2

-2

[5]

It is also possible to use different calendars for different rows by specifying an additional calendar id column.

Query

Column1

         WORKDAYS_BETWEEN ( "TimestampTable"."Begin" , "TimestampTable"."End" , FACTORY_CALENDAR ( "FactoryTable" ) , "TimestampTable"."Ident" )
        

Input

Output

FactoryTable

IDENT : string

START : date

END : date

'DE'

Tue Jan 02 2018 08:00:00.000

Tue Jan 02 2018 17:00:00.000

'DE'

Thu Jan 04 2018 08:00:00.000

Thu Jan 04 2018 13:00:00.000

'DE'

Thu Jan 04 2018 16:00:00.000

Thu Jan 04 2018 17:00:00.000

'DE'

Sat Jan 06 2018 08:00:00.000

Sat Jan 06 2018 17:00:00.000

'US'

Fri Jan 05 2018 08:00:00.000

Fri Jan 05 2018 17:00:00.000

'US'

Sun Jan 07 2018 08:00:00.000

Sun Jan 07 2018 12:00:00.000

'US'

Mon Jan 08 2018 08:00:00.000

Mon Jan 08 2018 17:00:00.000

TimestampTable

Ident : string

Begin : date

End : date

'DE'

Tue Jan 02 2018 00:00:00.000

Sat Jan 06 2018 00:00:00.000

'US'

Mon Jan 08 2018 00:00:00.000

Fri Jan 05 2018 00:00:00.000

Result

Column1 : int

2

-2