Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Answered

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

Posted on by 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 62,018 Super User on at
    No of months in a given year based on a from and to date

Helpful resources

Quick Links

Can you answer this forum question?

You could make someone's day!

Community Newsletter - May 2024

Kudos to our community stars!

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,632 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 224,528 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans