Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / No of months in a give...
Small and medium business | Business Central, N...
Answered

No of months in a given year based on a from and to date

(1) ShareShare
ReportReport
Posted on by DarrenJackson 14
Hi At first I thought this was simple but I just cant get my head around it.
 
What I want to do is work out how many months between 2 dates BUT and this is what is frying my noodle I want it for a given year. 
 
So if 
 
From Date = 01/10/2023
To Date = 01/05/2024
for the financial year of 01/09/2023 to 31/08/2024 how many months in the example above the answer is 7 but how do I code this so that I can use it on a page in Business Central BC23 on premise.
 
Of course the From and to dates change all the time and I want to expand on this so that I actually have a 5 year forcast so I would have a from and to date and the dates may span over several years but I would want 5 columns /Year1,Year2,Year3,Year4,Year5/ each showing how many months in each based on the from and to dates
 
Any help would be greatly appreciated
 
 
  • Verified answer
    DarrenJackson Profile Picture
    DarrenJackson 14 on at
    No of months in a given year based on a from and to date
    Hi Eventually I figured out a way to to do this. It may not be an elegant solution but its working.
     
    I created a procedure for each year then created what I am calling rules for each scenario
     
    local procedure CalculateMonthstoFYEnd1(): Integer
        var
            frommonth: Date;
            tomonth: Date;
            Diff: Integer;
            FYSTART1: Date;
            FYEND1: Date;
            StartDate: Date;
            EndDate: Date;
        begin
            FYSTART1 := CalculateFYStart1();
            FYEND1 := CalculateFYEND1();
     
            if Rec."Expected Starting Date" = 0D then
                StartDate := DMY2DATE(01, 01, 1973)
            else
                StartDate := rec."Expected Starting Date";
            if Rec."Expected End Date" = 0D then
                EndDate := DMY2DATE(01, 01, 1973)
            else
                EndDate := rec."Expected End Date";
            //Rule#1
            If (StartDate <= FYSTART1) and (EndDate >= FYSTART1) and (EndDate <= FYEND1) then begin
                fromMonth := FYSTART1;
                toMonth := EndDate;
                Diff := 1 + DATE2DMY(toMonth, 2) - DATE2DMY(fromMonth, 2) + 12 * (DATE2DMY(toMonth, 3) - DATE2DMY(fromMonth, 3));
            end;
            //Rule#2
            If (StartDate <= FYSTART1) and (EndDate >= FYEND1) then begin
                fromMonth := FYSTART1;
                toMonth := FYEND1;
                Diff := 1 + DATE2DMY(toMonth, 2) - DATE2DMY(fromMonth, 2) + 12 * (DATE2DMY(toMonth, 3) - DATE2DMY(fromMonth, 3));
            end;
            //Rule#3
            If (StartDate >= FYSTART1) and (StartDate <= FYEND1) and (EndDate <= FYSTART1) and (EndDate <= FYEND1) then begin
                fromMonth := StartDate;
                toMonth := EndDate;
                Diff := 1 + DATE2DMY(toMonth, 2) - DATE2DMY(fromMonth, 2) + 12 * (DATE2DMY(toMonth, 3) - DATE2DMY(fromMonth, 3));
            end;
            //Rule#4
            If (StartDate >= FYSTART1) and (EndDate >= FYEND1) then begin
                fromMonth := StartDate;
                toMonth := FYEND1;
                Diff := 1 + DATE2DMY(toMonth, 2) - DATE2DMY(fromMonth, 2) + 12 * (DATE2DMY(toMonth, 3) - DATE2DMY(fromMonth, 3));
            end;
            //Rule#5
            If (StartDate >= FYSTART1) and (StartDate >= FYEND1) then
                Diff := 0;
            exit(Diff);
        end;
     
  • DarrenJackson Profile Picture
    DarrenJackson 14 on at
    No of months in a given year based on a from and to date
    Thank you for taking the time to post but no sorry, I can get the difference between a single start/end date but when im wanting to break that down into a 5 year span its proving difficult.
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 78,978 Super User 2025 Season 1 on at
    No of months in a given year based on a from and to date

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,025 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,837 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans