Difference between two dates

[Guest]

Hi There,
I have spent the whole day trying to figure this one out and am not sure if
I am even 1% successful in my attempt. I have a query with two columns. One
contains start dates (dd/mm.yyyy) format and the other the end date in the
same format. I want a third column to calculate the elapsed time between
these two dates (in terms of years).
For example
Start Date: Jan 1, 2005
End Date: June 30, 2006
I want the elapsed time to be : 1.5 years

I tried using the "datediff" formula but it only gives me an answer of 1
year (difference between 2006 and 2005).

In MS Excel, there is a formula called "Yearfrac", which does exactly what I
want but it is absent in MS Access. IS there any other way for me to
calculate this field.

Thanks for all the help you could provide on this.

Best Regards,
Umair

 

[Guest]

You can get the different in days and then devide it by 365

Round(DateDiff("d",[StartDate],[EndDate])/365,1)

In the example it round it with one number after the dot

 

[Guest]

Unfortunately I am dealing with leap years so want to make sure the elapsed
time is exact.

You can get the different in days and then devide it by 365

Round(DateDiff("d",[StartDate],[EndDate])/365,1)

In the example it round it with one number after the dot

--
Good Luck
BS"D

Hi There,
I have spent the whole day trying to figure this one out and am not sure if
I am even 1% successful in my attempt. I have a query with two columns. One
contains start dates (dd/mm.yyyy) format and the other the end date in the
same format. I want a third column to calculate the elapsed time between
these two dates (in terms of years).
For example
Start Date: Jan 1, 2005
End Date: June 30, 2006
I want the elapsed time to be : 1.5 years

I tried using the "datediff" formula but it only gives me an answer of 1
year (difference between 2006 and 2005).

In MS Excel, there is a formula called "Yearfrac", which does exactly what I
want but it is absent in MS Access. IS there any other way for me to
calculate this field.

Thanks for all the help you could provide on this.

Best Regards,
Umair