Automated Creation of Shift Calendar Data

New release, new luck! In INOSIM 2025.1, it is possible to dynamically change Shift Calendars. In this tip and trick, we will show you how to set regular working hours, holidays, and exceptions using VBA. A sample model is linked. After a general introduction to the new feature, we will present three methods for automatically reading holidays from publicly accessible data sources or from your own lists in INOSIM, or directly calculating them using the Gaussian Easter formula.

General information on the changeability of the Shift Calendar

Changes to the Shift Calendar are only possible as long as it has not yet been called up by the simulation, i.e., during simulation initialization. The ShiftCalendar object has the following properties:

EnableValue property. Specifies whether a Shift Calendar contains values or availabilities (True or False)
DefaultValue property. Sets the default value of a Shift Calendar . (0: not available; >0 : Available / Value when EnableValue=True)
WorkingDays property. Returns the time ranges of weekdays in a Shift Calendar.
Holidays property. Returns the date ranges of holidays in a Shift Calendar.
Exceptions property. Returns the date ranges of exceptions in a Shift Calendar.

Entering regular working times into the Shift Calendar

Regular working times can be added to the Shift Calendar via the WorkingDays property. First, DayPeriod objects are generated, which determine the value of the shift calendar for a time period of a weekday. To do this, the properties Weekday, StartTime, and EndTime must be set.

The Weekday property sets the weekday of a time range in a shift calendar and is set using Integer values (1-7, corresponding to Sunday – Saturday) or via the Basic constants the vbMonday, vbTuesday.
The StartTime property sets the start time of the time range and is set using Integer values (seconds since midnight). If the property is Nothing, this corresponds to the beginning of the day.
TheEndTime property sets the end time of the time range and is set using Integer values (seconds since midnight). If the property is Nothing, this corresponds to the end of the day.

A DayPeriod object can be added to the WorkingDays property of the Shift Calendar using the Add method (in addition, a Remove and a Clear method are available).

The following subroutine sets DayPeriods for working hours from Monday to Friday between 8:00 and 17:00 and adds them to the Shift Calendar.

Sub AddWorkingDays
    Dim Cal As ShiftCalendar
    Dim i As Integer

    Cal = ShiftCalendars("Schichtkalender")
    'Default: no working time
    Cal.DefaultValue = 0

    For i = vbMonday To vbFriday
        Dim pWeek As New DayPeriod
        pWeek.Weekday = i
        'Start work at 08:00 am
        pWeek.StartTime = 8 * 3600
        'Stop work at 05:00 pm
        pWeek.EndTime = 17 * 3600
        'Set working hours as working time
        pWeek.Value = 1
        'Add working hours to shift calendar
        Cal.WorkingDays.Add pWeek
    Next
End Sub

Entering holidays to the Shift Calendar

Holidays can be added to the Shift Calendar using the Holidays property. For this, DatePeriod objects are generated that contain the date ranges of the holidays. Here, the StartDate and EndDate properties have to be set.

The StartDate property sets the start date of the time period and can be set as a simulation date. If the property is Nothing, this corresponds to the start time of the simulated time.
The EndDate property sets the end time of the time period and can be set as a simulation date. If the property is Nothing, this corresponds to the end time of the simulated time.

A DatePeriod object can be added to the Shift Calendar’s Holidays property using the Add method (in addition, a Remove and a Clear method exists).

The subroutine shown here adds holidays from a (previously defined) holidays table Holiday_Table to the Shift Calendar.

Sub AddHolidays
    Dim Cal As ShiftCalendar
    Dim Holiday_Table As New Table
    Dim i As Integer

    Cal = ShiftCalendars("Schichtkalender")
    'calcHolidays(Holiday_Table)
    'readICS(Holiday_Table, "C:\Users\MaxMustermann\Holidays.ics")  
    'readHolidayTable(Holiday_Table, "NW")

    For i = 1 To Holiday_Table.RowCount
        Dim pHoliday As New DatePeriod
        'Add holidays from holidays table to holiday period
        pHoliday.StartDate = Holiday_Table(i, 1)
        'Check if holidays table has 2nd column (holiday end dates; for ICS import)
        If Holiday_Table.ColumnCount = 2 Then
            pHoliday.EndDate = Holiday_Table(i, 2)
        Else
            'If no 2nd column, set end date in 24 hours
            pHoliday.EndDate = pHoliday.StartDate + 24 * 3600
        End If
        'Set holiday period as no working time
        pHoliday.Value = 0
        'Add holiday period to shift calendar
        Cal.Holidays.Add pHoliday
    Next
End Sub

The holidays table is filled in this example using one of the three outcommented subroutines:

calcHolidays
readHolidayTable
readICS

You can find the options behind this in the section Subroutines for setting holidays.

Using a table is optional; data can also be passed directly., e. g., via

pHoliday.StartDate = DateToSimDate(New Date(yyyy, mm, dd))
pHoliday.EndDate = DateToSimDate(New Date(yyyy, mm, dd))

Entering exceptions into the Shift Calendar

Exceptions can be added to the shift calendar via the Exceptions property. To do this, DatePeriod objects are generated, analogous to the treatment of holidays, which contain the date periods of the exceptions.

The following subroutine sets a shift break for Christmas Eve and New Year’s Eve from 1:00 PM until the start of the following day.

Sub AddExceptions
    Dim Cal As ShiftCalendar
    Dim pExceptions_hl_abend As New DatePeriod
    Dim pExceptions_silvester As New DatePeriod

    Cal = ShiftCalendars("Schichtkalender")

    'define start date (and time) of exceptions
    pExceptions_hl_abend.StartDate = DateToSimDate(New Date(2025, 12, 24, 13, 00, 00))
    pExceptions_silvester.StartDate = DateToSimDate(New Date(2025, 12, 31, 13, 00, 00))
    'define end date (and time) of exceptions
    pExceptions_hl_abend.EndDate = DateToSimDate(New Date(2025, 12, 25))
    pExceptions_silvester.EndDate = DateToSimDate(New Date(2026, 01, 01))
    'define exception periods as no working time
    pExceptions_hl_abend.Value = 0
    pExceptions_silvester.Value = 0
    'Add exception periods to shift calendar
    Cal.Exceptions.Add pExceptions_hl_abend
    Cal.Exceptions.Add pExceptions_silvester
End Sub

Subroutines for setting holidays

At this point, three options are presented with which the formerly laborious entrering of holidays in the ShiftCcalendar can be largely automated or realized by importing them from an Excel table or even from an ICS file.

The examples shown here refer to public holidays in Germany, but can of course be easily adapted to other countries and extended to include, for example, revision dates or company vacations.

Automatic setting of holidays

Many public holidays are linked to fixed dates and are therefore easy to define. However, a few public holidays are quite variable. In Germany, these are the public holidays that are based on Easter. If Easter Sunday, for example, is known, the dates of all other public holidays can be easily calculated. Fortunately, Easter Sunday can also be calculated thanks to the Gaussian Easter formula.

In this code example, the data for all public holidays in a year is written to a holidays table, whereby the public holidays that are based on Easter are calculated using the Gaussian Easter formula. The implementation of the formula was adopted here from the German federal agency Physikalisch-Technische Bundesanstalt.

The public holidays specific to your federal state can simply be “commented in”.

These input parameters of the subroutine are passed:

The holidays table Holiday_Table

Sub calcHolidays(ByRef Holiday_Table As Table)
    '#############################################################################
    'Calculates variable holidays referring to Easter via Gauss's Easter Algorithm,
    'other fixed holidays are also defined.
    'State specific (Germany) holidays may be commented in.

    'Holiday_Table: Return table containing the read holidays
    '############################################################################
    Dim  i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, n As Integer, o As Integer, p As Integer, q As Integer
    Dim cur_year As Integer

    cur_year = Year(SimDateToDate(Simulation.SimDate))

    'Holidays not referring to Easter
 '##########################################################################################

    'Neujahr
    Holiday_Table(1, 1) = DateToSimDate(DateSerial(cur_year, 01, 01))

    'Hl. drei Könige; Baden-Württemberg, Bayern und Sachsen-Anhalt
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 01, 06))

    'Int Frauentag; Berlin und Mecklenburg-Vorpommern
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, 08))

    'Tag der Arbeit 
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 05, 01))

    'Jahrestag der Befreiung vom Nationalsozialismus und Ende des Zweiten Weltkriegs; Berlin
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 05, 08))

    'Augsburger Friedensfest; Bayern
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 08, 08))

    'Mariä Himmelfahrt; Bayern und Saarland
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 08, 15))

    'Weltkindertag; Thüringen
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 09, 20))

    'Tag der deutschen Einheit
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 10, 03))

    'Reformationstag; Brandenburg, Bremen, Hamburg, Mecklenburg-Vorpommern, Niedersachsen, Sachsen, Sachsen-Anhalt, Schleswig-Holstein und Thüringen
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 10, 31))

    'Allerheiligen; Baden-Württemberg, Bayern, Nordrhein-Westfalen, Rheinland-Pfalz und Saarland
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 11, 01))

    'Buß- und Bettag; Sachsen
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 11, 19))

    '1. Weihnachtsfeiertag
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 12, 25))

    '2. Weihnachtsfeiertag
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 12, 26))

    'Calculation of Easter Sunday based on Gauss's Easter Algorithm, implementation taken from Physikalisch-Technische Bundesanstalt
    'https://www.ptb.de/cms/ptb/fachabteilungen/abt4/fb-44/ag-441/darstellung-der-gesetzlichen-zeit/wann-ist-ostern.html
    '###############################################################################################################################

  i = Int(cur_year / 100)
  j = 15 + Int((3 * i + 3) / 4) - Int((8 * i + 13) / 25)
  k = 2 - Int((3 * i + 3) / 4)
  l = cur_year Mod 19
  m = (19 * l + j) Mod 30
  n = Int(m / 29) + (Int(m / 28) - Int(m / 29)) * Int(l / 11)
  o = 21 + m - n
  p = 7 - (cur_year + Int(cur_year / 4) + k) Mod 7
  q = o + 7 - (o - p) Mod 7

    'Holidays referring to Easter
 '##########################################################################################

    'Karfreitag
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, q - 2) )

    'Ostersonntag; Brandenburg
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, q))

    'Ostermontag
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, q + 1))

    'Christi Himmelfahrt
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, q + 39))

    'Pfingstsonntag; Brandenburg
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, q + 49))

    'Pfingstmontag
    Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, q + 50))

    'Fronleichnam; Baden-Württemberg, Bayern, Hessen, Nordrhein-Westfalen, Rheinland-Pfalz und Saarland
    'Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(cur_year, 03, q + 60)) 
End Sub

Reading out holidays from Excel tables

Another option is to store public holidays, vacations, etc. in an Excel table.

In the example shown here, all possible public holidays in Germany in 2025 are listed in an Excel table and the federal state in which the day is actually a public holiday has been stored. This makes it easy to read out these public holidays and integrate them into the Shift Calendar. To do this, you need the Read_Table function, which you can find in another Tip & Trick (Applying table objects).

The following input parameters are passed to the subroutine here:

The holidays table Holiday_Table
The federal state token BL

Sub readHolidayTable(ByRef Holiday_Table As Table, ByVal BL As String)
    '##########################################################################################
    'Reads a table from an Excel sheet containing the information on the date of a holiday and
    'checks whether the holiday is actually a holiday in the specified state of Germany.
    'Holiday_Table: Return table containing the holidays selected by state
    'BL: State, possible input: BW  BY  BE  BB  HB  HH  HE  MV  NI  NW  RP  SL  SN  ST  SH  TH
'##########################################################################################

    Dim HolidayBLTable As New Table
    Dim i As Integer
    'read Excel sheet
    HolidayBLTable = Read_Table("Holidays", 1, 1, 0, 0, 1, 1)
    'check if the holiday is holiday in the selected state and write into return table
    For i = 1 To HolidayBLTable.RowCount
        If HolidayBLTable(i, BL) Then Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(HolidayBLTable(i, "Datum"))
    Next
End Sub

Reading out holidays from ICS files

Do you have a public holiday calendar on your PC or smartphone? Then why not export it to an ICS file (also known as an iCalendar file) and import the file into your INOSIM Shift Calendar using the last example.

In the subroutine presented here, you pass

the holidays table Holiday_Table
the path to your ICS file Path (e. g. C:\Users\Max Mustermann\Kalender.ics)

Sub readICS(Holiday_Table As Table, Path As String)
    '#################################################################
    'Reads an ICS calendar file and extracts all specified dates. Make sure the 
    'file only contains dates that should be imported (e.g. state specific holidays / exclude dates of other states).
    '**IMPORTANT** For dates marked as frequently (e.g., by:"RRULE:FREQ=YEARLY") only the first occurrence is imported.
    'Holiday_Table: Return table containing the read holidays
    'Path: Path to the specified ICS file
    '#################################################################

    Dim ICS_Date As String
    Dim Holidays() As String
    Dim i, row As Integer
    
    FileOpen(1, Path, OpenMode.Input)
    While Not EOF(1)
        ICS_Date = LineInput(1)
        If InStr(ICS_Date, "DTSTART;VALUE=DATE:") <> 0 Then
            'Read start date of holiday(s), convert and save to return table
            Holidays() = Split(ICS_Date, "DTSTART;VALUE=DATE:")
            For i = 1 To Holidays.Length - 1
                Holiday_Table(Holiday_Table.RowCount + 1, 1) = DateToSimDate(DateSerial(CShort(Mid(Holidays(i), _
1, 4)), CShort(Mid(Holidays(i), 5, 2)), CShort(Mid(Holidays(i), 7, 2))))
            Next
        End If

        If InStr(ICS_Date, "DTEND;VALUE=DATE:") <> 0 Then
            'If an end date is specified in ICS file, read end date of holiday(s), convert and save to 2nd column of return table
            Holidays() = Split(ICS_Date, "DTEND;VALUE=DATE:")
            For i = 1 To Holidays.Length - 1
                Holiday_Table(row + 1, 2) = DateToSimDate(DateSerial(CShort(Mid(Holidays(i), 1, 4)),_
 CShort(Mid(Holidays(i), 5, 2)), CShort(Mid(Holidays(i), 7, 2))))
                row += 1
            Next
        End If
    End While
    FileClose(1)
End Sub

But be careful: The subroutine does not differentiate according to the type of dates in the ICS file. All calendar entries are imported, regardless of whether they are public holidays, children’s birthdays or dentist appointments. So make sure you only export the data you want.

In addition, only the first entry can be taken into account for dates that have been saved with the setting of a specific recurrence frequency.

Download

An example model with all the subroutines shown here, including the function required for reading tables, is available in the Download area.

Downloads

More Tips & Tricks

9. May 2023