Calculating length of service (1 Viewer)

[JoS1972]

Hi all

I am a complete novice to access and have come across this forum today so am happy to have a community of people who maybe able to help me. Thank you in advance.

Now I'm going to stress again I am a novice, so please don't talk in jargon if you can help it as I'll only be doing this :banghead:.

I have a HR Database that a friend has helped me set up, well she done most of it but there are some bits that she couldn't do and I also know that I will want to tweak the database as it grows into a working database.

My question is: I have a box for Length of Service (LoS) that I want to show how much someones LoS is to date (year and month only). So someone with a start date of 01/03/2017 (1 March 2017) has a LoS of 0 years 5 months.

I honestly have no idea how to go about this. Can anyone help me please.
I am using the latest version of Access on Office 365 if that helps.

Thanks again in advance.

Jo

 

[Ranman256]

for date calculations, you use: DateAdd(...) and DateDiff(...)

you want ateDiff("m",[StartDate], Date)
this will give you months. (you can also set for days,years,etc)

 

[JoS1972]

Thank you Ranman, that worked. So how do I go about getting it to show the years as well as the months?

Jo

 

[jdraw]

You might consider doing the calculation in terms of days rather than months. But it all depends on the level of detail and context. What would .45 months mean for example?? Months don't have same number of days. But length of service is usually discussed in terms of start date and anniversary date.

Length of service is often associated with compensation/entitlement policy or contractual agreement. After 1 year of service, you are entitled to 2 weeks holidays. Or after 10 years service you are entitled to 5 weeks of vacation, and an option to join a pension, or purchase shares......

Perhaps you could give us more info on the context involved.

 

[bob fitz]

Thank you Ranman, that worked. So how do I go about getting it to show the years as well as the months?

Jo

Something like:

Service: DateDiff("yyyy",[StartDate],Date()) & " Years and " & DateDiff("m",[StartDate],Date())-(DateDiff("yyyy",[StartDate],Date())*12) & " Months"

 

[arnelgp]

copy this in a module:

Public Function fnLenOfService(Date1 As Date, Optional Date2 As Date = 0) As String
    Dim Year1 As Integer
    Dim Month_1 As Integer
    Dim Day1 As Integer
    Dim Temp As Date
    If Date2 = 0 Then Date2 = Date
    Temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Year1 = Year(Date2) - Year(Date1) + (Temp > Date2)
    Month_1 = Month(Date2) - Month(Date1) - (12 * (Temp > Date2))
    Day1 = Day(Date2) - Day(Date1)
    If Day1 < 0 Then
        Month_1 = Month_1 - 1
        Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
    End If
    fnLenOfService = Year1 & " years " & Month_1 & " months"
End Function

put this on your Textbox's Control Source(Property->Data->Control Source):
=fnLenOfService([DateFieldOnYourForm)

 

[JoS1972]

Something like:

Service: DateDiff("yyyy",[StartDate],Date()) & " Years and " & DateDiff("m",[StartDate],Date())-(DateDiff("yyyy",[StartDate],Date())*12) & " Months"

Bob, thank you. I am now doing a happy dance. This has given me exactly what I wanted.

Again thank you.

Jo

 

[JoS1972]

You might consider doing the calculation in terms of days rather than months. But it all depends on the level of detail and context. What would .45 months mean for example?? Months don't have same number of days. But length of service is usually discussed in terms of start date and anniversary date.

Length of service is often associated with compensation/entitlement policy or contractual agreement. After 1 year of service, you are entitled to 2 weeks holidays. Or after 10 years service you are entitled to 5 weeks of vacation, and an option to join a pension, or purchase shares......

Perhaps you could give us more info on the context involved.

Hi. At the moment the company that I work for doesn't offer any employee benefits other than the UK statutory minimum. It is something that I will be looking into. So at the moment the only real need for LoS is for any dismissal/disciplinary purposes.

 

[bob fitz]

Bob, thank you. I am now doing a happy dance. This has given me exactly what I wanted.

Again thank you.

Jo

Glad we could help.
Be careful with that dancing. Access has a habit of tripping us all up sometimes and leaving us a little bruised

 

[arnelgp]

hello sir, try these dates on the formula you have chosen:
mar 31, 2017 will return 5 months? today is aug 23, 2017
there are 22 days still before this date becomes 5 month,

while my function returns 4 month.

How about July 31, is it 1 month already?

 

[arnelgp]

again i tested:

dec 31, 2016 (last year)

result: 1 year and 13 months ?

your employees will be happy, while the boss will very mad at you.

 

[Samantha]

copy this in a module:

Public Function fnLenOfService(Date1 As Date, Optional Date2 As Date = 0) As String
    Dim Year1 As Integer
    Dim Month_1 As Integer
    Dim Day1 As Integer
    Dim Temp As Date
    If Date2 = 0 Then Date2 = Date
    Temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Year1 = Year(Date2) - Year(Date1) + (Temp > Date2)
    Month_1 = Month(Date2) - Month(Date1) - (12 * (Temp > Date2))
    Day1 = Day(Date2) - Day(Date1)
    If Day1 < 0 Then
        Month_1 = Month_1 - 1
        Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
    End If
    fnLenOfService = Year1 & " years " & Month_1 & " months"
End Function

put this on your Textbox's Control Source(Property->Data->Control Source):
=fnLenOfService([DateFieldOnYourForm)

Arnel, sorry to revive this old post. I was trying to follow your example for my project and was hoping you may be able to tell me what I am doing wrong. I've got it calculating correctly for anyone with a [StartingDate] and [EndDate] however anyone who has no [EndDate] (so current employees) is giving me #Type! in the cell
If I am interpreting correctly - I thought that this line was controlling that? If Date2 = 0 Then Date2 = Date

thanks in advance!
Samantha

 

[theDBguy]

Arnel, sorry to revive this old post. I was trying to follow your example for my project and was hoping you may be able to tell me what I am doing wrong. I've got it calculating correctly for anyone with a [StartingDate] and [EndDate] however anyone who has no [EndDate] (so current employees) is giving me #Type! in the cell
If I am interpreting correctly - I thought that this line was controlling that? If Date2 = 0 Then Date2 = Date

thanks in advance!
Samantha

Hi. If you're using something like:

LOS: fnLenOfService([StartDate],[EndDate])

try changing it to something like:

LOS: fnLenOfService([StartDate],Nz([EndDate],0))

Hope that helps...

 

[Pat Hartman]

Here's a database with useful date functions. One of the forms shows how to return a date difference formatted as you want to see it.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

 

[Samantha]

Here's a database with useful date functions. One of the forms shows how to return a date difference formatted as you want to see it.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

Thanks Pat I will check that out too.

 

[arnelgp]

here try it again:

Public Function fnLenOfService(ByVal Date1 As Variant, ByVal Date2 As Variant) As String
    Dim Year1 As Integer
    Dim Month_1 As Integer
    Dim Day1 As Integer
    Dim Temp As Date
    If IsNull(Date1) Then Date1 = Date
    If IsNull(Date2) Then Date2 = Date
    Temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Year1 = Year(Date2) - Year(Date1) + (Temp > Date2)
    Month_1 = Month(Date2) - Month(Date1) - (12 * (Temp > Date2))
    Day1 = Day(Date2) - Day(Date1)
    If Day1 < 0 Then
        Month_1 = Month_1 - 1
        Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
    End If
    fnLenOfService = Year1 & " year" & IIf(Year1 > 1, "s ", " ") & Month_1 & " month" & IIf(Month_1 > 1, "s", "")
End Function

 

[Samantha]

Thank you Arnel, much appreciated. I went from #Type! error to a #Name! error which ended up being me naming the module the same name as the function which now I learned you cannot do that. Once I ironed that out works beautifully.

Pat, I will need more time to dive in and understand what parts of your application will apply for my needs. Very impressive though, I am hoping it will help me set up eligibility for the 401K. I need to take the employees start date + 1 year then go to either Jan 1 or July 1 for open enrollment.

Thanks,
Samantha

 

[Pat Hartman]

I'm sure that @arnelgp 's code works but the code in the sample db is more flexible in that it lets you specify the output you want THIS time.

 

[Samantha]

I'm sure that @arnelgp 's code works but the code in the sample db is more flexible in that it lets you specify the output you want THIS time.

It works for the purpose of determining length of service and in a year/month format. Sometimes its hard for a novice like myself to really understand what is going on with a complicated db like yours making it intimidating to use. For me its hard to determine which pieces go together. I am going to try to figure it out though with my 401K parameters because I think the answer is there.

 

[Pat Hartman]

You won't find something that specific there. The database is generic functions but they may give you some ideas.