 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2008Calulation for Years of Service
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 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.  SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail 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. > > 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 [quoted text clipped - 12 lines] > 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 [quoted text clipped - 20 lines] >> Note: this can be run from a query, or an unbound control on a form or >> report.... NOT in a table. 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) SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail > 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. The Ten Commandments of Access http://www.mvps.org/access/tencommandments.htm #8: Thou shalt not copy and paste other people's code without at least attempting to understand what it does. Could this commandment be wrong <g>?! Jamie. -- 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")) > 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 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 > 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 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 > Fred, > [quoted text clipped - 16 lines] >> >> Tina 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..... > 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 [quoted text clipped - 30 lines] > >> > >> Tina 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 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 [quoted text clipped - 43 lines] >> >> >> >> Tina 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 > 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 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. > Tina, > I think this works. It calculates the number of months in total (3 times [quoted text clipped - 18 lines] > > > > Tina |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.