Difficulty in Date Difference

Business Central/NAV Developers Forum
1

Hi
I am trying to calculate the difference between the resumption Date and the rundate to calculate the no. of months. This no. of months should be multiply by the no. of leave an employee is entiled. Each employee is has a category were he/she belongs to and each of these categories has their corresponding leave entitle. For example category 2 employees has a corresponding leave entitlement of 20 days, 3-> 20days etc. These has a table called Categories where you have categories and entitlements.

When an employee has taken leave say have of the entitlement and then resume, he has a remaining leave entitlement. He spend some months and whats to know his entitlement, we run the below report to add the remaining leave to the leave entitlement for the months spent after resumption. The way the next leave is calculate is we divide the entitlement base on the category by 12 and the multiple this by the number of months spent after resumption to work. This answer is then added to the remaining leave and this gives the person’s leave entitlement. The absence table is used to subtract the leaves taken
Can some check this report and help me make this work leave i mhave explained.

I am getting problems on the difference in months. I use the resumption date to calculate the no of months spent after resumption which is subtracted from the run date to get the months spent after resumption.
Can anyone help please.

Code:

OBJECT Report 50026 Leave Computation
{
OBJECT-PROPERTIES
{
Date=27/03/08;
Time=16:34:01;
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table5200;
OnAfterGetRecord=BEGIN
Cats.GET(Catagory);
EmpName :=“First Name” + Space + “Middle Name” + Space + “Last Name”;
// Testing Below
//Cats.GET(Employee.Catagory);
//“Employee Absence”.SETRANGE(“Employee No.”,Employee.“No.”);
//Cats.SETRANGE(Catagories, Employee.Catagory);
RemLeave := Cats.“Leave entitle”;
AnnLeave := 0;

{Emp.RESET;
Emp.SETCURRENTKEY(“No.”,“Resumption Date”);
Emp.SETRANGE(“No.”,“No.”);
Emp.SETFILTER(“Resumption Date”,’<>%1’,0D);}

//IF Emp.FIND(’+’) THEN
Resumedate :=“Resumption Date”;
//LeaveRunDate := VarDate;
//Nday := LeaveRunDate - Resumedate;


{IF “Resumption Date” <> 0D THEN BEGIN
nOfDays := VarDate - “Resumption Date”;
END;}

IF NOT EVALUATE(VarDate,VarDateText) THEN;
{IF (VarDate <> 0D) AND (“Resumption Date” <> 0D) THEN
NoMonth := 12*((VarDate - “Resumption Date”)/365.25);
NofMonths := ROUND(NoMonth,1,’<’);}



VarDate := TODAY;
//ExpectedResultDate := CALCDATE(’-Resumedate’, VarDate);
MESSAGE(’%1’, ExpectedResultDate);

IF “Resumption Date” > VarDate THEN BEGIN
//MESSAGE(textDateError);
//EXIT(‘00-00-00’);
END;

g1 := DATE2DMY(“Resumption Date”, 3);
m1 := DATE2DMY(“Resumption Date”, 2);
d1 := DATE2DMY(“Resumption Date”, 1);
//m1days := LastDayOfMonth(g1, m1); // Simple function to find last day of month using Date table

g2 := DATE2DMY(VarDate, 3);
m2 := DATE2DMY(VarDate, 2);
d2 := DATE2DMY(VarDate, 1);

IF d2 < d1 THEN BEGIN
d2 += m1days;
m2 -= 1;
END;
d := d2 - d1;

IF m2 < m1 THEN BEGIN
m2 += 12;
g2 -= 1;
END;
m := m2 - m1;

g := g2 - g1;

RemLeave := Cats.“Leave entitle” + ROUND(m *(Cats.“Leave entitle”)/12,1,’>’);

//EXIT(CONVERTSTR(FORMAT(g, 2) + ‘-’ + FORMAT(m, 2) + ‘-’ + FORMAT(d, 2), ’ ‘, ‘0’));









//IF “Employee Absence”.FIND(’-’)
//THEN BEGIN
//AnnLeave := “Employee Absence”.Quantity;
//IF “Employee Absence”.NEXT <> 0
//THEN REPEAT
//AnnLeave := AnnLeave + “Employee Absence”.Quantity;
//UNTIL “Employee Absence”.NEXT = 0;
//RemLeave := Cats.“Leave entitle” - “Employee Absence”.Quantity;
//END
//ELSE BEGIN
//RemLeave := Cats.“Leave entitle”;
//AnnLeave := 0;
//END;
END;

ReqFilterFields=No.,Date Filter;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
SectionWidth=17250;
SectionHeight=2115;
}
CONTROLS
{
{ 1000000011;Label ;1500 ;1269 ;9150 ;423 ;FontSize=12;
FontBold=Yes;
CaptionML=ENU=STAFF LEAVE COMPUTATION REPORT }
{ 1000000016;TextBox;12150;0 ;3750 ;423 ;HorzAlign=Right;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1000000018;TextBox;13050;846 ;2850 ;423 ;HorzAlign=Right;
SourceExpr=USERID }
{ 1000000015;TextBox;0 ;0 ;1800 ;423 ;SourceExpr=COMPANYNAME }
{ 1000000017;TextBox;14400;423 ;1500 ;423 ;SourceExpr=CurrReport.PAGENO }
{ 1000000019;Label ;12900;423 ;1500 ;423 ;HorzAlign=Right;
CaptionML=ENU=Page }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=17250;
SectionHeight=846;
}
CONTROLS
{
{ 1000000014;Label ;0 ;0 ;2250 ;423 ;FontSize=9;
FontBold=Yes;
CaptionML=ENU=Employee No.: }
{ 1000000000;TextBox;2250 ;0 ;1350 ;423 ;FontSize=8;
SourceExpr=Employee.“No.” }
{ 1000000005;Label ;3900 ;0 ;1200 ;423 ;FontSize=9;
FontBold=Yes;
CaptionML=ENU=Name: }
{ 1000000002;TextBox;5250 ;0 ;6900 ;423 ;FontSize=8;
SourceExpr=EmpName }
{ 1000000006;Label ;0 ;423 ;1650 ;423 ;FontSize=9;
FontBold=Yes;
CaptionML=ENU=Category: }
{ 1000000001;TextBox;1800 ;423 ;900 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=Employee.Catagory }
{ 1000000003;Label ;2850 ;423 ;2550 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=Leave Entitle To }
{ 1000000007;TextBox;5550 ;423 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=RemLeave }
{ 1000000030;TextBox;7650 ;423 ;1500 ;423 ;SourceExpr=Resumedate }
{ 1000000032;TextBox;9750 ;423 ;1500 ;423 ;SourceExpr=d }
{ 1000000033;TextBox;11550;423 ;1500 ;423 ;SourceExpr=m }
{ 1000000034;TextBox;13350;423 ;1500 ;423 ;SourceExpr=Nday }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table5207;
OnAfterGetRecord=BEGIN
//Cats.GET(Employee.Catagory);

empAbse.SETRANGE(“Employee No.”,Employee.“No.”);
empAbse.SETRANGE(Deductible,TRUE);
Cats.SETRANGE(Catagories, Employee.Catagory);

//IF Emp.FIND(’+’) THEN
//Resumedate := Emp.“Resumption Date”;


IF empAbse.FIND(’-’)
THEN BEGIN
AnnLeave := empAbse.Quantity;
IF empAbse.NEXT <> 0
THEN REPEAT
AnnLeave := AnnLeave + empAbse.Quantity;
UNTIL empAbse.NEXT = 0;
RemLeave := Cats.“Leave entitle” - AnnLeave;
END
ELSE BEGIN
RemLeave := Cats.“Leave entitle”;
AnnLeave := 0;
END;
END;

DataItemLink=Employee No.=FIELD(No.);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
SectionWidth=17250;
SectionHeight=423;
}
CONTROLS
{
{ 1000000020;Label ;0 ;0 ;1350 ;423 ;HorzAlign=Left;
FontBold=Yes;
CaptionML=ENU=From Date: }
{ 1000000022;Label ;1350 ;0 ;1050 ;423 ;HorzAlign=Left;
FontBold=Yes;
CaptionML=ENU=To Date: }
{ 1000000024;Label ;2850 ;0 ;2100 ;423 ;HorzAlign=Left;
FontBold=Yes;
CaptionML=ENU=Cause of Absence: }
{ 1000000026;Label ;5250 ;0 ;4200 ;423 ;HorzAlign=Left;
FontBold=Yes;
CaptionML=ENU=Description: }
{ 1000000028;Label ;13350;0 ;1800 ;423 ;HorzAlign=Left;
FontBold=Yes;
CaptionML=ENU=No. of days: }
{ 1000000031;Label ;15150;0 ;1500 ;423 ;HorzAlign=Left;
FontBold=Yes;
CaptionML=ENU=Deductible }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=17250;
SectionHeight=423;
}
CONTROLS
{
{ 1000000004;TextBox;0 ;0 ;1200 ;423 ;HorzAlign=Left;
SourceExpr=“From Date” }
{ 1000000021;TextBox;1200 ;0 ;1200 ;423 ;HorzAlign=Left;
SourceExpr=“To Date” }
{ 1000000023;TextBox;2850 ;0 ;2700 ;423 ;HorzAlign=Left;
SourceExpr=“Cause of Absence Code” }
{ 1000000025;TextBox;6150 ;0 ;3450 ;423 ;HorzAlign=Left;
SourceExpr=Description }
{ 1000000027;TextBox;13350;0 ;1800 ;423 ;HorzAlign=Left;
SourceExpr=Quantity }
{ 1000000029;TextBox;15150;0 ;1500 ;423 ;HorzAlign=Left;
SourceExpr=Deductible }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=17250;
SectionHeight=1269;
}
CONTROLS
{
{ 1000000009;Label ;0 ;0 ;3150 ;423 ;FontSize=9;
FontBold=Yes;
CaptionML=ENU=Annual Leave Taken: }
{ 1000000010;TextBox;3150 ;0 ;1050 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=AnnLeave }
{ 1000000012;Label ;4200 ;0 ;3900 ;423 ;HorzAlign=Left;
FontSize=9;
FontBold=Yes;
CaptionML=ENU=Annual Leave Remaining: }
{ 1000000013;TextBox;8100 ;0 ;1050 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=RemLeave }
{ 1000000008;TextBox;0 ;423 ;17250;423 ;HorzAlign=Left;
2

I’m afraid that nobody will help you if you putting this “bible” newcommer, you display comments, controls… too many things :frowning:

3

Hi,

Can you then check this part of the code for me. Thats where i am having problems.

RemLeave := Cats.“Leave entitle”;
AnnLeave := 0;
Resumedate :=“Resumption Date”;
VarDate := TODAY;
IF “Resumption Date” > VarDate THEN BEGIN

END;

g1 := DATE2DMY(“Resumption Date”, 3);
m1 := DATE2DMY(“Resumption Date”, 2);
d1 := DATE2DMY(“Resumption Date”, 1);
//m1days := LastDayOfMonth(g1, m1); // Simple function to find last day of month using Date table
g2 := DATE2DMY(VarDate, 3);
m2 := DATE2DMY(VarDate, 2);
d2 := DATE2DMY(VarDate, 1);

IF d2 < d1 THEN BEGIN
d2 += m1days;
m2 -= 1;
END;
d := d2 - d1;
IF m2 < m1 THEN BEGIN
m2 += 12;
g2 -= 1;
END;
m := m2 - m1;
g := g2 - g1;
RemLeave := Cats.“Leave entitle” + ROUND(m *(Cats.“Leave entitle”)/12,1,’>’);

4

Ok, I will try to help you, but first I will note a pair of things:

  • IF “Resumption Date” > VarDate THEN BEGIN

END; Why do you use this if there’s no code inside, maybe you want the rest of code lines to be executed in the ELSE case ¿?

  • Cats is a record var of table ¿?

  • What do you want this code to do exactly?, maybe just to get the days difference between “Resumption Date” and VarDate or something similar?

5
  • IF “Resumption Date” > VarDate THEN BEGIN

END

Am not actually using the above lines of code. I forgot to delete it. I was actually making sure that VarDate should be greater than the Resumption Date othewise it should show an error.

Cats is a records var of Category table.

I want the code to give me the date difference of VarDate( Which is the date the report will be run) and Resumption Date( Which is the date the person resumes from leave).

When i get this difference, let say its M. The code should divide the leave entitlement base on the category which is obtained from the Category table by 12 and if the result is N, then we have MN. Now RemingLeave:= RemingLeave + MN;

6

I have to recognize I’m quite lost with your problem. I work with NAV 5 and there’s no table Category, and no table that contains this word has a “Leave entitle” field, is this a custom table?

Another thing is that you really complicate the calculation between dates, do you know that (TODAY - “Resumption Date”) returns the number of days between this dates?.

7

Catgory is a customized table with categries and leaves entitlment and am working with Navision 4.0.

TODAY - “Resumption Date” - I need this to be converted to no. of months.

8

The following is an aproximated, but usually acceptable calculation:

Months := (TODAY - “Resumption Date”) DIV 30;

IF ((TODAY - “Resumption Date”) MOD 30) > 15 THEN
Months := Months + 1;

This is expecially useful when the difference between dates is more than a year.

9

Thanks Anna. Its working. Are F or M?

10

these work better:

Months := (TODAY - “Resumption Date”) DIV 30;

IF ((TODAY - “Resumption Date”) MOD 30) > 15 THEN
Months := Months + 1;

Months:=Months-ROUND(Months/65,1);