Calculate years & months [Resolved]

[salvelinus]

I'm using DateDiff in a query to calculate the time between two dates. Right now it's giving me the whole number of years. Is there a way to get the years and months, i.e. 2 yrs 3 months, 2.3, or similar? Thanks.

[Ecniv]

A Possible solution :

cdbl(format(cdate("01 jan 2005")-cdate("01 jul 2002"),"yyyy.m"))-1900

[salvelinus]

Thanks, that's pretty close. Two issues:
Some results have multiple zeroes after the decimal point, e.g. 2.20000000,
and the results are in base 10 for the months, whereas there are 12 months in the year. I'm thinking a Mod is needed, but can't figure out where it would go. Here's what I have:

SELECT tblBMXData.fldDepartment, tblBMXData.fldPhone, tblBMXData.fldLName, _
tblBMXData.fldFName, tblBMXData.fldTitle, tblBMXData.fldHireDate, _ 
tblBMXData.fldTermDate, cdbl(format(cdate(fldTermDate) - cdate(fldHireDate), 'yyyy.m')) - 1900 AS fldSvcYrs
FROM tblBMXData
ORDER BY fldTermDate;

[ivobenkovic]

Hi...

here s a snippet how you might calculate the difference as desired

sub test
  Dim dt1 As Date
  Dim dt2 As Date
  dt1 = #1/1/2005#
  dt2 = #12/1/2004#
  Debug.Print Abs(Fix(DateDiff("m", dt1, dt2) / 12)) & "." & Abs(DateDiff("m", dt1, dt2) Mod 12)
end sub

and here within your query supposing you are using a query within ms-jet

SELECT tblBMXData.fldDepartment, tblBMXData.fldPhone, tblBMXData.fldLName, _
tblBMXData.fldFName, tblBMXData.fldTitle, tblBMXData.fldHireDate, _ 
tblBMXData.fldTermDate, 
Abs(Fix(DateDiff("m", fldTermDate, fldHireDate) / 12)) & "." & Abs(DateDiff("m", fldTermDate, fldHireDate) Mod 12) as fldSvcYrs
FROM tblBMXData
ORDER BY fldTermDate;

Hope this helps

Regards

[RobDog888]

Welcome to the Forums.

Your test sub will return a value of 0.1 or 1/10 of a year? Not 0.08333 or 1/12 of a year?
I think its on the right track. Let me see what I can come up with.

[ivobenkovic]

ok... i forgot to tell that the number before the dot displays the amount of years and the number after the dot the amount of months (As specified by salvenius...)

[salvelinus]

That works great. I'd never heard of the Fix function, and always think of Abs as math stuff. Thanks to all of you.