hi everybody,
Once again I am here to take your help. Please let me know how I will be able to get accurate days, months and years. I have two fields
1. Date of Joining [12-03-1990]dd/mm/yyyy
2. Date of retirement [03-07-2008]dd/mm/yyyy
in a query I want total days months and year the employee devoted in his services. [Date_of_joining]-[Date_of_retirement]. It should be 22 years 03 month and 18 days.

Age: DateDiff("yyyy",[Date_of_joining],[Date_of_retirement])but it gives only year.

thanks with regards

harish mankoti

Not quite... using DateDiff with the "yyyy" might give you a misleading answer because if you use it to find the difference between 1 January 2008 and 31 December 2007, you'll find that it says ONE YEAR when only ONE DAY has passed!

Fast and dirty with inaccuracies:

My advice is to use it to calculate total number of days ONLY (i.e. use "d", not "yyyy"). For convenience, I'll use all integers. Then you have:

intDays = DateDiff("d", [Date_of_Joining], [Date_of_Retirement])
intYears = CInt(intDays / 365.25)
intMonths = CInt(intDays / 30) - (intYears * 12)
intDays = intDays - CInt(intYears * 365.25) - (intMonths * 30)

This is approximate because there are either 365 or 366 days in the year with leap years. Also months are 28, 29, 30, or 31 days long.

More Accurate:

Loop from hire to retirement days day by day. Have three counters: intDays, intMonths, intYears. The counter starts with all = 0. As the counter goes, intDays adds 1. When it hits one month = DateSerial(Year([Date_of_Joining]), Month([Date_of_Joining]) + (intMonths + 1), Day([Date_of_Joining])) then add 1 to intMonths and reset intDays to zero. When it hits the anniversary date DateSerial(Year([Date_of_Joining]) + intYears + 1, Month([Date_of_Joining]), Day([Date_of_Joining])), then add 1 to intYears and reset intDays and intMonths to zero. Etc.

I'm sure there are other techniques out there.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division