D365 Business Central : Calculate Dates Based On Working Days

One of the recent requirements that I got was to calculate dates based on working days, excluding weekends and holidays. This can be accomplished by leveraging the Base Calendar feature and utilising the CalcDateBOC procedure in codeunit 7600 “Calendar Management.” In this blog post, let’s explore the steps to achieve this functionality.

Base Calendar Setup

Let’s begin by creating a new base calendar in by navigating to the Base Calendar page.

For illustration purposes, we’ll name this calendar “MONWED” to represent working days from Monday to Wednesday.

Understanding the CalcDateBOC Procedure

procedure CalcDateBOC(OrgDateExpression: Text[30]; 
       OrgDate: Date; 
       CustomCalendarChange: Array[2] of Record "Customized Calendar Change"; 
       CheckBothCalendars: Boolean): Date

The CalcDateBOC procedure in codeunit 7600 “Calendar Management” is important for calculating dates based on the custom calendar we set up. This procedure takes four parameters:
– OrgDateExpression: A date formula used to calculate the date.
– OrgDate: The date from which we want to calculate.
– CustomCalendarChange: The base calendar(s) we want to use (up to two base calendars).
– CheckBothCalendars: An option to use either one or both calendars during the calculation.

Case 1: Calculating Dates Using Company Information Calendar

In our first scenario, let’s explore how to calculate dates using the base calendar set up in the Company Information. First assign our newly created calendar to Company Information.

Before calling the CalcDateBOC procedure, we need to specify the calendar source using the SetSource procedure. In this example, we set the source as “Company” to refer to the Company Information. Since we have already provided the source as Company, there’s no need to supply the Base Calendar Code separately.

procedure GetNewDateBasedOnWorkingDaysFromCompanyInfo(CurrDate: Date; CurrDateFormula: DateFormula): Date
var
    CustomizedCalendarChange: array[2] of Record "Customized Calendar Change";
    CalendarManagement: Codeunit "Calendar Management";
begin
    CustomizedCalendarChange[1].SetSource(Enum::"Calendar Source Type"::Company, '', '', ''); //Setup source as Company Information
    exit(CalendarManagement.CalcDateBOC(Format(CurrDateFormula), CurrDate, CustomizedCalendarChange, false));
end;

Let’s try calling the procedure.

var
    NewDate: Date;
    CurrDateFormula: DateFormula;
    CurrDate: Date;
begin
    Evaluate(CurrDateFormula, '3D'); //Add 3 days
    CurrDate := 20230725D; // 25 July 2023 (Tuesday)
    NewDate := GetNewDateBasedOnWorkingDaysFromCompanyInfo(CurrDate, CurrDateFormula);
    Message('New Date: %1', NewDate); //Result 1 August 2023 (Tuesday) 
end;

Result is three working days after 25 July 2023 (Wed 26th, Mon 31st, Tue 1st) based on our Monday to Wednesday Calendar.

Case 2: Calculating Dates Using Calendar from Our Own Table

Now if we want to use calendar on our own table, instead of the one in company information, we can do this by extending the Calendar Source Type enum. We will use the new enum as the source and specify the base calendar code accordingly.

enumextension 60111 "Calendar Source Type_TNG" extends "Calendar Source Type"
{
    value(60111; "My Calendar_TNG")
    {
        Caption = 'My Calendar';
    }
}

procedure GetNewDateBasedOnWorkingDaysFromMonWedCalendar(CurrDate: Date; CurrDateFormula: DateFormula): Date
var
    CustomizedCalendarChange: array[2] of Record "Customized Calendar Change";
    CalendarManagement: Codeunit "Calendar Management";
begin
    CustomizedCalendarChange[1].SetSource(Enum::"Calendar Source Type"::"My Calendar_TNG", '', '', 'MONWED'); // Using MONWED base calendar
    exit(CalendarManagement.CalcDateBOC(Format(CurrDateFormula), CurrDate, CustomizedCalendarChange, false));
end;

Case 3: Calculating Dates Using Two Calendars

In some scenarios, you may need to use two calendars for calculation. Let’s explore this option as well. Create another new calendar named “WEDFRI” to represent working days from Wednesday to Friday.

Let’s try using this procedure.

procedure GetNewDateBasedOnWorkingDaysFromTwoCalendars(CurrDate: Date; CurrDateFormula: DateFormula): Date
var
    CustomizedCalendarChange: array[2] of Record "Customized Calendar Change";
    CalendarManagement: Codeunit "Calendar Management";
begin
    CustomizedCalendarChange[1].SetSource(Enum::"Calendar Source Type"::"My Calendar_TNG", '', '', 'MONWED'); // Using MONWED base calendar
    CustomizedCalendarChange[2].SetSource(Enum::"Calendar Source Type"::"My Calendar_TNG", '', '', 'WEDFRI'); // Using WEDFRI base calendar
    exit(CalendarManagement.CalcDateBOC(Format(CurrDateFormula), CurrDate, CustomizedCalendarChange, true));
end;

var
    NewDate: Date;
    CurrDateFormula: DateFormula;
    CurrDate: Date;
begin
    Evaluate(CurrDateFormula, '3D'); //Add 3 days
    CurrDate := 20230725D; // 25 July 2023 (Tuesday)
    NewDate := GetNewDateBasedOnWorkingDaysFromTwoCalendars(CurrDate, CurrDateFormula);
    Message('New Date: %1', NewDate); //Result 2 August 2023 (Tuesday) 
end;

Result is 2 August 2023.

Why do we get 2 August 2023?

After applying the first calendar (“MONWED”), the result is 1 August 2023, which falls on a Tuesday. However, since Tuesday is a non-working day in the second calendar (“WEDFRI”), the system proceeds to the next available working day for both calendars, which is 2 August 2023 (Wednesday). In this procedure, the second calendar is only used after applying the first calendar to find the earliest possible working day.

Why do we want to use this?

Consider the following scenario: you send goods to your customer on Tuesday, 25 July 2023. It takes 3 working days for your courier to deliver the goods, so the earliest arrival date at your customer’s place is on 1 August 2023. However, your customer does not operate on Tuesdays, so your courier will attempt redelivery on the next available working day. As a result, the goods will arrive on 2 August 2023, which is the next available working day for both your courier and customer. By using two calendars, you ensure that delivery is scheduled considering the working days for both parties involved.

That’s it. Hopefully this post will help you understand more on how to use this procedure to calculate working days using calendar.