Calulation for Years of Service

  • Thread starter Thread starter Building Buildings
  • Start date Start date
B

Building Buildings

Can someone help me with a formula? I'm trying to arrive at the number of
years of service that reads 2.3 (two years 3 months). My formula now only
gives me the years. I would like the months included. I would appreciate
any help someone could give me.

Tina
 
Can someone help me with a formula? I'm trying to arrive at the number of
years of service that reads 2.3 (two years 3 months). My formula now only
gives me the years. I would like the months included. I would appreciate
any help someone could give me.

Tina
Click to expand...

Well, 2.3 years is actually 2 years 4 months :-(

How about a function that will return a literal "2 years 3 months"?

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

Note: this can be run from a query, or an unbound control on a form or
report.... NOT in a table.
 
Fred,
I'm kinda new at this so the link you gave me is a little greek. This is
the formula that I have written: YOS: DateDiff("yyyyy","[hire
date],now())+Int(format(now(),"mmdd")<format([hire date],"mmdd"))

Can you tell me how it should read to include the months? PLEASE - I've
been working on this way too long and can't figure it out.
 
Fred,
I'm kinda new at this so the link you gave me is a little greek. This is
the formula that I have written: YOS: DateDiff("yyyyy","[hire
date],now())+Int(format(now(),"mmdd")<format([hire date],"mmdd"))

Can you tell me how it should read to include the months? PLEASE - I've
been working on this way too long and can't figure it out.

fredg said:
Well, 2.3 years is actually 2 years 4 months :-(

How about a function that will return a literal "2 years 3 months"?

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

Note: this can be run from a query, or an unbound control on a form or
report.... NOT in a table.
Click to expand...
Click to expand...

The authors of that Diff2Dates function I suggested to you didn't
spend all of that time writing it if something less 'greeky' would
have worked.

I would suggest you re-read that function at the Web site and do as it
says.
Copy the function (by selecting it from the Web site example) and
Paste it into an Access Module.
Then call the function, from a query for example, using:
YOS:Diff2Dates("ym",[HireDate],Date(),True)
 
Fred,
This is a little greek to me. Can you help with the correct formula? my
formula reads: YOS: DateDiff("yyyy","[Hire
Date],Now())+Int(format(now(),"mmdd"),format[Hire Date],"mmdd"))
 
Fred,

I'm sorry if I've upset you but the dates were throwing me off as to what I
need to put there. I've had to teach myself this program and it's a little
difficult. Approach was much easier to learn. But thank you for the help
you offered.

Tina
 
If you are new to Access you may be unclear about things such as
instructions telling you to paste the code into a module. If so, be
specific about what you do not understand. People here are generally quite
willing to help people who make an effort to help themselves.

Being self-taught, there may be some things that got past you in the
learning process. You may find some of the articles at this page to be
useful:
http://allenbrowne.com/tips.html

Also, there are links to a lot more information for users on all levels
here:
http://allenbrowne.com/links.html
 
Thank you Bruce for the info. I did try to search for the answers before
stumbling onto this site. Again, I'm sorry if I've appeared to be stupid and
incompetent but this software is a little hard to understand.

But I did try to help myself. That's what makes it fun learning.

Again thank you for the help.....
 
Tina,
I think this works. It calculates the number of months in total (3 times
over!) then works out the years and remaining months, joined with a dot.

YOS: Int(DateDiff("m",[HireDate],Now())/12) & "." &
DateDiff("m",[HireDate],Now())-Int(DateDiff("m",[HireDate],Now())/12)*12


But it has problems. The DateDiff works in Calendar units. So if someone
joins on, say, 29 Jan the function ignores the days and just sees that as
January. When the month changes to February they are immediately seen as
having worked one month. If you want the sophistication of actual
anniversary days then it would be more complex.

Good Luck
Richard
 
I may have been unclear. It seemed to me you had made an effort to
understand and learn, and you had made an effort to solve the problem. By
the way, when code is giving you problems it is usually best to post the
code).
Access is indeed rather hard to understand, and the learning curve is steep.
As a relative beginner the information and experience you can bring to bear
are limited, but that is not a personal failing or a character flaw, so
there is no need to put yourself down. The GettingStarted newsgroup may be
a better choice for your questions, but the suggestion you have received
will address your problem in detail, and may be simpler to implement than
you realize. Other suggestions are simpler, and may be close enough for
your needs.
 
Thank you so much Richard. I DO appreciate your help. Thanks for taking
the time to help me. I really was stuck. Have a great day.

RGUBTON said:
Tina,
I think this works. It calculates the number of months in total (3 times
over!) then works out the years and remaining months, joined with a dot.

YOS: Int(DateDiff("m",[HireDate],Now())/12) & "." &
DateDiff("m",[HireDate],Now())-Int(DateDiff("m",[HireDate],Now())/12)*12


But it has problems. The DateDiff works in Calendar units. So if someone
joins on, say, 29 Jan the function ignores the days and just sees that as
January. When the month changes to February they are immediately seen as
having worked one month. If you want the sophistication of actual
anniversary days then it would be more complex.

Good Luck
Richard
Building Buildings said:
Can someone help me with a formula? I'm trying to arrive at the number of
years of service that reads 2.3 (two years 3 months). My formula now only
gives me the years. I would like the months included. I would appreciate
any help someone could give me.

Tina
Click to expand...
Click to expand...
 
Back
Top