Date function, counting years problem

[Chintsapete]

Hi all

I used below function for a couple of year and I thought it was working. Now I noticed that it's not working to my requirements.
The problem is that if an employee starts working in November it's year 0 until January and then it becomes year 1, instead of becoming year 1 in November following year. Is there a way to change the function below that only after 12 months employment it becomes 1 year etc.
Any help would be appreciated.

Public Function fnYearsEmployed(DateOfEngagment _
                                , DateOfTermination)

        
        Dim YearsEmployed As Integer
        Dim EndDate
        If IsNull(DateOfTermination) Then
            EndDate = Date
        Else
            EndDate = DateOfTermination
        End If
        
        YearsEmployed = Nz(Int((DateDiff("yyyy", DateOfEngagment, EndDate))))
        fnYearsEmployed = YearsEmployed
        
        
End Function

 

[plog]

DateDiff simply subtracts year values:

1/1/2017, 12/31/2017 -> 0
12/31/2017, 1/1/2018 -> 1

To achieve what you want I suggest going more grainular and checking the difference in days and seeing if its over 365. Of course, then you fall victim to leap years when counting multiple years:

2/28/2000, 2/23/2020 -> 7300 -> 20 * 365

 

[The_Doc_Man]

Or count months and divide by twelve.

 

[plog]

Or quarters and divide by 4
Or fortnights and divide by 26
Or weeks and divide by 52

Anyone want to "help" out and do the math on hours, minutes and seconds (all of which will not be any more granular than days)?

 

[Chintsapete]

Hi Plog
thanks for the quick response.
On the month would be accurate enough, but if I do something like below do I not get a funny output 0.25? I would need O or 1 or 2 etc
My problem is it's live database and I'm a bit under time pressure to correct it. It's not quite so easy to test.

YearsEmployed = (Nz(Int((DateDiff("mm", DateOfEngagment, EndDate)))))/12

 

[jdraw]

I used below function for a couple of year and I thought it was working. Now I noticed that it's not working to my requirements.

Lesson learned -- Test, Test!

 

[plog]

First, I suggest not doing months:

12/31/2017, 12/1/2018 -> 12

Is that accurate enough? Someone who started the last day of the month will be credited with a year of service on the first day of that month in the next year.

For your specific expression, you really need to understand what you are doing. It's math-like, inner parenthesis to outer parenthesis.

First you get the difference in months (which will always be an integer)
Second you convert that integer to an integer.
Third, you make sure that integer is not null (which it won't ever be)
Finally you divide that integer by 12.

So, you open up yourself to decimals because the last thing you do is divide by 12. Most likely you want the Int() as the last operation.

 

[plog]

It's not quite so easy to test.

Just saw this. Yes, its very easy to test, this isn't 1950, memory doesn't cost $1 a byte and we don't have to wait for our time on the mainframe.


Make a copy of your database and work in there. Then, when things work, move the corrected code/objects back to the live version.

 

[Chintsapete]

I see now why you suggest days, I haven't considered that one, makes sense now.
Thanks for the math refresher, forgot about the integer. I'll give your suggestion a go tomorrow when I'm fresh again.
Thank you so much for your help.

 

[The_Doc_Man]

And for that matter, I believe you could use "" to mean INTEGER DIVIDE and avoid fractions - but then you must beware of truncation.

 

[Cronk]

Think about your age. It is the number of years between the current year and your birth year, minus 1 if the current month/day is less than that of your birth day.

YearsDifference = dateDiff("yyyy",dateS, dateE) - iif (format(dateS,"mmdd") <format(dateE,"mmdd"),0,1)

where dateS/E are the Start/End dates

 

[Galaxiom]

This expression counts the number of full years, clicking over on the anniversary.

DateDiff("yyyy",[DateofEngagement],[DateofTermination])+(Format([DateofTermination],"mmdd")<Format([DateOfEngagement],"mmdd"))

 

[Chintsapete]

Thanks Galaxiom and Cronk and everyone else for the help.
That works sweet, I had to make a slight change to your line of code so it refers to EndDate. It gave me "error 94 invalid use of null" where date of termination was blank.

Working function below for anybody else.

Public Function fnYearsEmployed(DateOfEngagment _
                                , DateOfTermination)

        
        Dim YearsEmployed As Integer
        Dim EndDate
        If IsNull(DateOfTermination) Then
            EndDate = Date
        Else
            EndDate = DateOfTermination
        End If
        
        YearsEmployed = DateDiff("yyyy", [DateOfEngagment], [EndDate]) + (Format([EndDate], "mmdd") < Format([DateOfEngagment], "mmdd"))
        fnYearsEmployed = YearsEmployed
        
        
End Function