Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 

Description

This function calculates working days between two dates based on a TFACS style calendar table.

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. A 1 is a working day. A 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, end )
  • calendar_id: the ID defines which calendar out of the calendar table is used.

If the calendar id does not exist in the calendar table, WORKDAYS_BETWEEN assumes that only the weekends are days off.

Example

Here WORKDAYS_BETWEEN is used without an existing working calendar. Therefore it assumes that all days, except weekends are working days.
Query
Column1
WORKDAYS_BETWEEN ( "Table1"."Calendar_ID" , "Table1"."Column1" , "Table1"."Column2" )
Input
Table1
Column1 : DATEColumn2 : DATECalendar_ID : STRING
Sat Apr 15 2017 00:00:00.000
Sun Apr 16 2017 00:00:00.000
'Does not exist Calendar ID'
Sun Apr 16 2017 00:00:00.000
Mon Apr 17 2017 00:00:00.000
'Does not exist Calendar ID'
Mon Apr 17 2017 00:00:00.000
Tue Apr 18 2017 00:00:00.000
'Does not exist Calendar ID'
Output
Result
Column1 : INT
1
1
1
  • No labels