Options

Adding n number of working days to a date

misadministratormisadministrator Member Posts: 11
Hi

I'm wanting to populate a variable on a report with the completion date of an installation. I have the fields "Installation Date" and "Engineers Time On Site", where "Engineers Time On Site" is of type Decimal and shows the number of days a job is schedued to run (0.5, 1, etc...)

At first, I simply coded:

roundup:= ROUND("Engineers Time On Site", 1, '>');
completionDate:= "Installation Date" + roundup - 1;

But realised, that this doesn't take any account of weekends and is therefore incorrect!

How do I write a routine to recognise weekdays/weekends?

Many Thanks

- R

Comments

  • Options
    amunozsuamunozsu Member Posts: 30
    Hi,

    You can try something like that, against the virtual table date:

    SetRange("Period Type", 0);
    SetRange("Period Start", startDate, endDate);
    SetFilter("Period No.", '%1|%2', 6,7);
    weekEndDays := Count;

    -- Alejandro --
  • Options
    kinekine Member Posts: 12,562
    You can calc the date based on standard Calendar functionality in Navision. See CU 7600 function CalcDateBOC. It is like CALCDATE but with use of company, vendors, customers etc. calendars (as you select). Example of usage:
        "Planned Delivery Date" :=
          CalendarMgmt.CalcDateBOC(
            FORMAT("Shipping Time"),
            "Planned Shipment Date",
            CalChange."Source Type"::Customer,
            "Sell-to Customer No.",
            '',
            CalChange."Source Type"::"Shipping Agent",
            "Shipping Agent Code",
            "Shipping Agent Service Code",
            TRUE);
    

    As you can see, it can use two calendars to calculate the date...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    misadministratormisadministrator Member Posts: 11
    amunozsu wrote:
    Hi,

    You can try something like that, against the virtual table date:

    SetRange("Period Type", 0);
    SetRange("Period Start", startDate, endDate);
    SetFilter("Period No.", '%1|%2', 6,7);
    weekEndDays := Count;

    -- Alejandro --

    Thanks Alejandro, but there's a problem. I don't have an endDate until after I've worked out weekEndDays. Could I do something like:

    SetRange("Period Type", 0);
    SetFilter("Period No.", '%1|%2|%3|%4|%5', 1,2,3,4,5);

    Then find the record in Date that matches "Installation Date", and do a 'Next' loop to add ("Engineers Time On Site" - 1) to it.

    Think this would work, but I don't know how to code it. Any suggestions?

    - R
  • Options
    kinekine Member Posts: 12,562
    If you can, use standard calendar functionality in Navision... as you can see in my example, it is easy, just call the function with correct parameters and it will calc all for you. And you can change the calendar, add holidays etc. and it will be still working correctly...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    misadministratormisadministrator Member Posts: 11
    great... thanks... I'll go away and have a try!

    - R
  • Options
    BeliasBelias Member Posts: 2,998
    Hi, is there some documentation about parameters of CalcDateBOC somewhere?the code inside is a bit crappy to follow...thanks in advance
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    JohnConJohnCon Member Posts: 55
    You may try adding a function that steps through saturday and sunday, something like this:


    CalcEndDate(SDate : Date;NumberofDays : Integer;VAR EDate : Date;VAR CalendarDays : Integer;VAR TxtDayWeek : Option)
    IF SDate =0D THEN
    EXIT;
    IF NumberofDays <0 THEN
    EXIT;

    WorkingDate :=SDate;
    FOR Count :=1 TO NumberofDays
    DO BEGIN
    WorkingDate :=CALCDATE('1D',WorkingDate);
    DayWeek :=DATE2DWY(WorkingDate,1);
    IF DayWeek =6 THEN WorkingDate := CALCDATE('2D',WorkingDate);
    IF DayWeek =7 THEN WorkingDate := CALCDATE('1D',WorkingDate);
    END;
    TxtDayWeek :=DATE2DWY(WorkingDate,1);
    EDate :=WorkingDate;
    CalendarDays :=WorkingDate - SDate;
  • Options
    DenSterDenSter Member Posts: 8,304
    Take some time to figure out the calendar, you'll be surprised at how useful that can be. :thumbsup:
  • Options
    BeliasBelias Member Posts: 2,998
    just fount how it works...what sounds strange to me is how the CM is treated: i've done a:
    01/01/10 plus CM + 15D and the result was 08/03/10 (ggmmaa format)...nonworking days are only sat&sunday + only one monday
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    BeliasBelias Member Posts: 2,998
    just figured it out... ](*,)
    a bit crappy...especially if i have to fulfill the following conditions about payment terms!

    Pay at the fifteenth working day of the next month :shock: :-k
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    BeliasBelias Member Posts: 2,998
    Sorry for triple posting, but i am adding things, i cannot just edit ( :wink: )
    in the italian module we have a table, payment lines, that stores multiple due dates for the payment, based on some dateformulas...i think i have to split the "JustDoACalcdate" part from the "CalcdateWithWorkingDaysOnly" part, and then calculate the due date by apply the CALCDATE first and the CalcDateBOC on the first "CALCDATED" due date...hope to be clear...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    vasjavasja Member Posts: 19
    Actualy if you just want to go over weekends there is simpler way:

    // Input parameters are StartDate and WorkDays which is number of workdays which you have to add to StartDate

    wd := DATE2DWY(StartDate,1); // Get day of week, 1 for monday, 2 for tuesday, etc...
    TotalDays := WorkDays+((WorkDays+wd-1) DIV 5) * 2; // add all intermediate saturdays and sundays
    IF(wd > 5) THEN // in case start date is saturday or sunday we have to go back a little
    TotalDays -= (wd - 5);
    EndDate := StartDate + TotalDays; // add totaldays to startdate

    Regards,
  • Options
    BeliasBelias Member Posts: 2,998
    it's not just about sat&sundays. You can check my blog to see how I solved the issue on my blog ;)
    Thanks, anyway!
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
Sign In or Register to comment.