Difference between two dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Unfortunately I am dealing with leap years so want to make sure the elapsed
time is exact.

Ofer Cohen said:
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


Umair said:
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
Click to expand...
Click to expand...
 
Back
Top