Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 02 Jun 2011 at 8:45am
can you explain what you are trying to get at with the formula?
I am confused by it as the datediff() is looking at years but you are using NOW which is time
also the INT and FORMAT are not making sense to me...
Joined: 02 May 2011
Location: United States
Online Status: Offline
Posts: 28
Posted: 02 Jun 2011 at 10:06am
I'm trying to figure Tenure using the person's date of hire. It works in Access.
I'm getting an error stating: The ) is missing. The first occurrence of the word Format is highlighted so I think it means I need a ) somewhere around that word. However, no matter where I put one, I get the same message.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 02 Jun 2011 at 10:17am
so you want the number as years hired including this year if the hire date was before or on today but not this year if it was after today?
I think this would give you that...
DateDiff('yyyy',{EMPLOYEE.DATE_HIRED},currentdate) - if datepart('y',currentdate)>datepart('y',{EMPLOYEE.DATE_HIRED}) then 1
Joined: 02 May 2011
Location: United States
Online Status: Offline
Posts: 28
Posted: 06 Jun 2011 at 10:28am
Thank you! That works. However, I need it to use the month and day as well as the year to figure the tenure. So if they started on 10/1/2009 and today is 6/6/2011 it should show 1 year because they won't actually have been there 2 years until October. When I type in mmddyyyy in each of the places that have either yyyy or just a y, I get an error that says it's out of range. Can you tell me why? Thanks so much!
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
