Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV forum / No of months in a give...
Microsoft Dynamics NAV forum
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
 
 
Categories:
Financial Management
Development / Customization / SDK
  • 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 64,563 Super User on at
    No of months in a given year based on a from and to date

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,584 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,864 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans