Using Datediff in a query to return years and months

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

Guest

I am running a query to decern the years and months elapsed from one service
to the next.

I can run it to display the year but not month. Here is my funciton: YRS:
DateDiff("yyyy",[BIRTH_DATE],[CLOSING_DATE]). How do I incorporate the month?

Thank you,
 
Here is some code I use in a form to show years and months of service. You
can modify it for your needs...


=DateDiff("m",[HireDate],Date())\12 & " yrs. " &
DateDiff("m",[HireDate],Date()) Mod 12 & " mts."
 
The decimal returned was not in complete months. I need a return of 12.5 for
twelve years and five months not twelve and a half, or six months.

Rick B said:
Here is some code I use in a form to show years and months of service. You
can modify it for your needs...


=DateDiff("m",[HireDate],Date())\12 & " yrs. " &
DateDiff("m",[HireDate],Date()) Mod 12 & " mts."

--
Rick B



Analyst 1 said:
I am running a query to decern the years and months elapsed from one service
to the next.

I can run it to display the year but not month. Here is my funciton: YRS:
DateDiff("yyyy",[BIRTH_DATE],[CLOSING_DATE]). How do I incorporate the month?

Thank you,
Click to expand...
Click to expand...
 
Then I think you'd simply do...

=DateDiff("m",[HireDate],Date())/12



--
Rick B



Analyst 1 said:
The decimal returned was not in complete months. I need a return of 12.5 for
twelve years and five months not twelve and a half, or six months.

Rick B said:
Here is some code I use in a form to show years and months of service. You
can modify it for your needs...


=DateDiff("m",[HireDate],Date())\12 & " yrs. " &
DateDiff("m",[HireDate],Date()) Mod 12 & " mts."

--
Rick B



Analyst 1 said:
I am running a query to decern the years and months elapsed from one service
to the next.

I can run it to display the year but not month. Here is my funciton: YRS:
DateDiff("yyyy",[BIRTH_DATE],[CLOSING_DATE]). How do I incorporate
Click to expand...
the
month?
Thank you,
Click to expand...
Click to expand...
Click to expand...
 
..5 is not 5 months. .5 means 1/2 (6 months).

The first formula I sent you would have returned...

12 years 5 months.

You are welcome to change it so it prints "12", a period, and then "5".

Simply replace " yrs. " with "."
and leave off the & " mts." part

But again, 12.5 is 12 and a half years which is twenve years and 6 months.

--
Rick B



Analyst 1 said:
The decimal returned was not in complete months. I need a return of 12.5 for
twelve years and five months not twelve and a half, or six months.

Rick B said:
Here is some code I use in a form to show years and months of service. You
can modify it for your needs...


=DateDiff("m",[HireDate],Date())\12 & " yrs. " &
DateDiff("m",[HireDate],Date()) Mod 12 & " mts."

--
Rick B



Analyst 1 said:
I am running a query to decern the years and months elapsed from one service
to the next.

I can run it to display the year but not month. Here is my funciton: YRS:
DateDiff("yyyy",[BIRTH_DATE],[CLOSING_DATE]). How do I incorporate
Click to expand...
the
month?
Thank you,
Click to expand...
Click to expand...
Click to expand...
 
Rick B said:
..5 is not 5 months. .5 means 1/2 (6 months).

The first formula I sent you would have returned...

12 years 5 months.

You are welcome to change it so it prints "12", a period, and then "5".

Simply replace " yrs. " with "."
and leave off the & " mts." part

But again, 12.5 is 12 and a half years which is twenve years and 6 months.

--
Rick B



Analyst 1 said:
The decimal returned was not in complete months. I need a return of 12.5 for
twelve years and five months not twelve and a half, or six months.

Rick B said:
Here is some code I use in a form to show years and months of service. You
can modify it for your needs...


=DateDiff("m",[HireDate],Date())\12 & " yrs. " &
DateDiff("m",[HireDate],Date()) Mod 12 & " mts."

--
Rick B



I am running a query to decern the years and months elapsed from one
service
to the next.

I can run it to display the year but not month. Here is my funciton: YRS:
DateDiff("yyyy",[BIRTH_DATE],[CLOSING_DATE]). How do I incorporate the
month?

Thank you,
Click to expand...
Click to expand...


Thank you
Click to expand...
 
I am running a query to decern the years and months elapsed from one service
to the next.

I can run it to display the year but not month. Here is my funciton: YRS:
DateDiff("yyyy",[BIRTH_DATE],[CLOSING_DATE]). How do I incorporate the month?

Thank you,
Click to expand...

Your current expression will not give you the correct number of years,
as it will return 1 if the Birthdate is 12/31/2000 and the ClosingDate
is 1/1/2001.

An accurate method to determine the difference in elapsed full years
would be:
In a query:
YRS: DateDiff("yyyy", [Birth_Date], [Closing_Date]) - IIF
(Format([Birth_Date], "mmdd") >Format([Closing_Date], "mmdd"), 1, 0)

Which doesn't quite do what you want, as it does not break out the
months also.

I would suggest you take a look at:

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html

which will return Years and Months (and days if you wish).
i.e. 2 Years 5 Months.
 
Back
Top