Adding n number of working days to a date

misadministrator · 2006-05-19

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

amunozsu · 2006-05-19

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 --

kine · 2006-05-19

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...

misadministrator · 2006-05-19

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

kine · 2006-05-19

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...

misadministrator · 2006-05-19

great... thanks... I'll go away and have a try!

- R

Belias · 2010-02-18

Hi, is there some documentation about parameters of CalcDateBOC somewhere?the code inside is a bit crappy to follow...thanks in advance

JohnCon · 2010-02-18

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;

DenSter · 2010-02-18

Take some time to figure out the calendar, you'll be surprised at how useful that can be. :thumbsup:

Belias · 2010-02-19

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

Belias · 2010-02-19

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

Belias · 2010-02-19

Sorry for triple posting, but i am adding things, i cannot just edit (

)
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...

vasja · 2011-04-28

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,

Belias · 2011-04-29

it's not just about sat&sundays. You can check my blog to see how I solved the issue on my blog

Thanks, anyway!

Adding n number of working days to a date

Comments

Categories