Hi how can i derive the difference of 2 dates in terms of years months n days eg : d1 ;= 010105 d2 := 010502 the result of d1- d2 should be n years n months n days thanx in advance
Do you have any spare tables, or temp tables? You could write code to populate a table with the dates and a number. Have the system subtract one from the other and convert it into yrs Mths and days. You’d probably have to average the months to be approx 30 days long (or something like that) Hope it works?
Hi John no i do not use any temp tables …it should be calculated on the fly on the form basing on the date values from the table. rgds MV
Some rough ideas, with some important assumptions: 1. Date1 > Date2 2. there is a function MonthLen(Date) that returns the length of the month in Date. This could be done using a 12-element array, and some code to check if it is a leap year. DateDiff(Date1, Date2: Date); D1 := DATE2DMY(Date1, 1); D2 := DATE2DMY(Date2, 1); M1 := DATE2DMY(Date1, 2); M2 := DATE2DMY(Date1, 2); Y1 := DATE2DMY(Date1, 3); Y2 := DATE2DMY(Date1, 3); IF D1 >= D2 THEN Days := D1 - D2 ELSE BEGIN Days := D1 + (MonthLen(Date2) - D2); M2 := M2 + 1; IF M2 = 13 THEN BEGIN M2 := 1; Y2 := Y2 + 1; END; END; IF M1 >= M2 THEN Months := M1 - M2 ELSE BEGIN Months := M1 + (12 - M2); Y2 := Y2 + 1; END; Years := Y1 - Y2;
You could uste temp table 2000000007 Date. SETRANGE on “Period Start” Then COUNT the different “Period Type”'s
Here’s another rough idea: D1 := 290204D; D2 := 310304D; IF (D1 > D2) THEN BEGIN // Either swap dates or issue errormessage END; DTemp := D1; WHILE (DTemp <= D2) DO BEGIN DTemp := CALCDATE('1M', DTemp); MM := MM + 1; END; MM := MM - 1; YY := MM DIV 12; MM := MM MOD 12; DTemp := CALCDATE('-1M', DTemp); DD := D2 - DTemp; MESSAGE('%1 - %2 = %3 years %4 months %5 days', D1, D2, YY, MM, DD);You’ve got to get your definitions straight, though. The above example will give the result 1 month 2 days - one might argue that the correct result is 1 month.
quote:
Originally posted by MVGS
Hi how can i derive the difference of 2 dates in terms of years months n days eg : d1 ;= 010105 d2 := 010502 the result of d1- d2 should be n years n months n days thanx in advance
Try this it’s good, and say me what you think about d1 := 010105D; d2 := 010502D; dd2 := CALCDATE(‘<+CM>’,d2); nbr_year := (DATE2DMY(d1,3)-DATE2DMY(d2,3))-1; nbr_month := 12 + DATE2DMY(d1,2)-DATE2DMY(d2,2); IF nbr_month >= 12 THEN BEGIN nbr_month -= 12; nbr_year += 1; END; nbr_day := DATE2DMY(dd2,1) - DATE2DMY(d2,1) + DATE2DMY(d1,1); buy
Or you could possibly use some logic based on the Date Table. I use it to get Number of Work Days, Calender Days, Months, etc… for date calculations.
How about this? DateTbl.setrange(“Period Type”,DateTbl.“Period Type”::month); DateTbl.setrange(“Period Start”,begindate,enddate); NoofMonths := datetbl.count; and so forth for year and day by changing the period type filter where DateTbl is the Date table where begindate and enddate are your date ranges
This is not an answer to your specific question but if you are recording Date Differences Might be usefull to Others looking a call duration logging etc: Minutes:=((“End Date”-“Start Date”)*1440)+ROUND((“End Time”-“Start Time”)/60000,1); Days := Minutes DIV 1440; Hours := (Minutes MOD 1440) DIV 60; Minutes := (Minutes MOD 60);
OK I dont think that the dates will be 1st day of the months so we have more work to do eg : d1 ;= 010105 d2 := 010502 try d1 := 17/05/02; d2 := 10/02/05; Or d1 := 10/05/02; d2 := 17/02/05; could not leave it like this so 7:00am Saturday morning! create code unit You will need to add code to make sure blank and error dates are not sent This code is meant as a guide not a full solution and was only tested with these examples you will need to adjust the code for all senarios as required, then post any changes here to share with others. Create a Codeunit and a Function called Code add the Globals Variables Day, Month, Year are Integer Calendar is Record Date OnRun() Code(170502D,100205D); Code(100502D,170205D); Code(FromDate : Date;ToDate : Date) // Number of Years Calendar.RESET; Calendar.SETRANGE(“Period Type”,Calendar.“Period Type”::Year); Calendar.SETRANGE(“Period Start”,FromDate,ToDate); Years := Calendar.COUNTAPPROX; // We have the Whole years // if we have not reached same month then adjust IF (Years <> 0)AND(DATE2DMY(ToDate, 2) < DATE2DMY(FromDate, 2))THEN Years := Years - 1; // Number of Months Calendar.SETRANGE(“Period Type”,Calendar.“Period Type”::Month); Months := Calendar.COUNTAPPROX; Months := Months MOD(12); // We have the Whole Months // if we have not reached same Day then adjust IF (Months <> 0)AND(DATE2DMY(ToDate, 1) < DATE2DMY(FromDate, 1))THEN Months := Months - 1; // Now Days if the to Day is greater then difference else calculate IF (DATE2DMY(ToDate, 1) <> DATE2DMY(FromDate, 1))THEN IF (DATE2DMY(ToDate, 1) > DATE2DMY(FromDate, 1))THEN Days := DATE2DMY(ToDate, 1) - DATE2DMY(FromDate, 1) ELSE Days := (CALCDATE(‘CM’,FromDate)-FromDate) + DATE2DMY(ToDate, 1); MESSAGE(’%1 Days %2 Months %3 Years’,Days,Months,Years);