DateDiff Problems

[unclefink]

I am having some difficulty with a date calculation that i built in a query. The cell is supposed to calculate the difference between two dates and tell me how long an employee has worked for the company in years. The formula I am currently using is

Years in Service: DateDiff("yyyy",[Hire_Date],Date())

The problem I am having is it appears as though when an employee works in multiple years but has yet to reach the anniversary date, the number is counting up.

Example. I have an employee in the DB who was hired on 11/4/2011, the result is currently 1; however it should not be 1 until the anniversary date of 11/4/2012.

What am I doing wrong???? Someone, anyone please help.

Can anyone help.

 

[Mr. B]

Try this:

Years In Service: IIf(DateDiff("d",[Hire_Date]],Date())<365,0,DateDiff("yyyy",[Hire_Date]],Date()))

 

[unclefink]

Its coming back with a syntax error?

 

[Alansidman]

Remove the extra square bracket shown in RED

Years In Service: IIf(DateDiff("d",[Hire_Date]],Date())<365,0,DateDiff("yyyy",[Hire_Date]],Date()))

I suspect it was just a typo.

Alan

 

[Mr. B]

Yes, I managed to get an extra ] in there. Sorry about that.

 

[unclefink]

It seems as though the code works, however; its still calculating up before the actual anniversary hire date. Thanks in advance for the help. I greatly appreciate it.

 

[Mr. B]

The code is designed to first check the number of days difference between the [Hire_Date] and the current date. If the difference is < 356 it returns a 0 for zero years, otherwise it returns the difference in years only when the number of days difference is > 365.

 

[unclefink]

So I assume there is no way to make it count up on the annivery date rather than the first day of the new year. This being the case for all employees who have been employed for <1 year.

For example, im showing an employee who was hired on 12/27/87 as being employed for 25 years where infact this would not be true until 12/27, a little more than 7 months from now.

Thanks again for the help, and the patience, its greatly appreciated.

 

[Mr. B]

Because the employee's employment anniversary date is always going to be in the current year, in addition to the conditions you are already checking, you can create a date variable that would hold the anniversary date in the current year and then check to see if the anniversary date of the current year is > the current date and if so subtract 1 from the results of the DateDiff.

 

[unclefink]

I think thats what id like to do to make it -1 prior to anniversary date. Being new to this process can you tell me how i should start this process, im assuming i add onto what ive currently got but the commands are somewhat intimidating.

 

[RainLover]

This has been tested in 2003 only.

Age: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

 

[unclefink]

2010 throws an error indicating the expression entered contains the wrong number of arguments.

 

[RainLover]

Don't know why 2010 does that.

I have attached a sample Database in 2003.

 

[unclefink]

I copied your syntax and made the appropriate changes and it does exactly what i want it to do. Thank you very much everyone for your help. I greatly appreciate it.

 

[RainLover]

I copied your syntax and made the appropriate changes and it does exactly what i want it to do. Thank you very much everyone for your help. I greatly appreciate it.

Did you change anything to make it work in 2010 or was the previous post just the result of a typo.

 

[unclefink]

It was a typo. I used the entire syntax and changed the appropriate reference points and it did exactly what i was looking for. Thanks again for the help, I appreciate it.

 

[RainLover]

Good to hear.

Now I can say it has been tested in 2010.

I didn't think that there would be a problem but it is best to test to make sure.

 

[unclefink]

Rain,

I just tested it at home on 2007 and it also works as designed. 2003, 2007, 2010 confirmed. Thanks again for the help.