Calendars in System manager are most typically used to calculate workdays and periods. For example, if you want to calculate how many work days or hours a journal has been waiting for approval.

You can set up a calendar in Aico and then use a formula which references that calendar when calculating workdays or hours. The calendar can be marked with holiday days which are then skipped in the calculation as well as setting a working day in hours, such as 09:00 - 17:00 CET.

If you are using a non-calendar period, for example, the 4,4,5 periods, then the Aico calendar is used to set non-calendar start and end dates to each period in order to set correct periods for associated posting dates.

Note that calendars in System manager are completely independent of holiday calendars in Automation manager which are used to calculate due dates and times for tasks.

This article looks at:

How to set up a calendar using value sets

Marking holidays and period start and end dates

Using formulas together with the calendar

How to set up a calendar using value sets.

Set up an Aico calendar using a Value set type "Calendar"

Go to:

1. Value Sets 

2. Create New
 

In the details view, give your calendar a name, set the type as calendar and enter the time zone and office hours (if required) and Save.

Time zones can be found here. Examples include "W. Europe Standard Time", "Central Europe Standard Time", "Eastern Standard Time", "Central Standard Time", "FLE Standard Time" and "GMT Standard Time".

The resulting calendar will look as follows:

Marking holidays and period start and end dates

By default, all weekends are marked as holidays.

Click on a date and a dialogue box will allow you to set that day as a holiday, workday or period start or end date. 

These are then used (together with the working hours) in the formulas below.

Period Start and Period End are only needed if you are using a non-calendar period which can both be for a fiscal year. For example, if a fiscal year starts on 1st April and runs to 31st March in which case April is Period 1 and March is Period 12. You may have periods which do not follow the exact calendar days e.g. period 4 start on Monday 4th April and ends on Sunday 2nd May.

Using formula together with the calendar.

Here, we will cover some common examples in which formulas and calendars are used together.

Calculating the number of workdays a document has been waiting for approval.

=IF(approvedAt=NULL(),CALENDARDIF(createdAt,NOW,"D","Holiday Calendar"),CALENDARDIF(createdAt,approvedAt,"D","Holiday Calendar"))

This formula first checks to see if the approvedAt date header is set, if not then it calculates the difference in days between "Now" and when the document was sent for approval (createdAt) based on the Holiday Calendar. If the approvedAt date is set meaning the date the document was approved then it will set the difference in days between the createdAt and approvedAt headers based on the Holiday Calendar. The formula will exclude any days you have marked as holidays.

Calculating a period from a posting date when using a non-standard calendar

=PERIOD(accountingDate, "Holiday Calendar")

This formula will return the period e.g. 2022-12 from an accounting (posting) date of a document (journal). If you have set the period start in the holiday calendar as 3rd December then a document posted on 2nd December will return a period 2022-11 whereas a document posted on 3rd December will return 2022-12.

Calculating a real date for a workday e.g. what "real" date is workday 5 of the period

=WORKDAY(5, period, "Holiday Calendar")

This formula will return the 5th workday of the period set in the period header based on the Holiday Calendar i.e. if the period is set as 2022-12, it returns 7th December 2022 as the 3rd and 4th are a Saturday and Sunday so are skipped in the formula.

=WORKDAY(5,DATEADD(period,1,"MO"))

This formula returns next month’s 5th workday of the next period set in the period header based on the Holiday Calendar. The DATEADD part is adding 1 month to the current period.