Date Differences in Years
in [Forms]
|
From: allenjc11 on 27 May 2010 15:53 Hello, I have the following formula in Access 2007 to determine how many years someone has been with the company. ----- Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) ----- This formula is partially accurate, but not completely because, for example, if someone was hired on 8/24/09 and today's date is 5/27/10, it says that person has worked here for a year (the value result = 1). But because it technically hasn't been a full year, the number isn't accurate. I would like for it to return a value = 0 (zero) if it hasn't been a full year between the two dates. Is there something I can add to this formula to get an accurate year difference? Regards, allenjc11
From: Douglas J. Steele on 27 May 2010 16:01 It's the same principle as calculating a person's age. Check http://www.mvps.org/access/datetime/date0001.htm at "The Access Web". -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "allenjc11" <allenjc11(a)discussions.microsoft.com> wrote in message news:B61935E5-B922-4552-B334-7A0222507041(a)microsoft.com... > Hello, > > I have the following formula in Access 2007 to determine how many years > someone has been with the company. > > ----- > > Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) > > ----- > > This formula is partially accurate, but not completely because, for > example, > if someone was hired on 8/24/09 and today's date is 5/27/10, it says that > person has worked here for a year (the value result = 1). But because it > technically hasn't been a full year, the number isn't accurate. I would > like > for it to return a value = 0 (zero) if it hasn't been a full year between > the > two dates. > > Is there something I can add to this formula to get an accurate year > difference? > > Regards, > allenjc11
From: Dorian on 27 May 2010 16:49 Look in Access HELP for full details on DateDiff statement. How about figuring the difference in days and then looking for 365. Of couse that will not take into account leap years. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "allenjc11" wrote: > Hello, > > I have the following formula in Access 2007 to determine how many years > someone has been with the company. > > ----- > > Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) > > ----- > > This formula is partially accurate, but not completely because, for example, > if someone was hired on 8/24/09 and today's date is 5/27/10, it says that > person has worked here for a year (the value result = 1). But because it > technically hasn't been a full year, the number isn't accurate. I would like > for it to return a value = 0 (zero) if it hasn't been a full year between the > two dates. > > Is there something I can add to this formula to get an accurate year > difference? > > Regards, > allenjc11
From: John W. Vinson on 27 May 2010 19:30 On Thu, 27 May 2010 12:53:29 -0700, allenjc11 <allenjc11(a)discussions.microsoft.com> wrote: >Hello, > >I have the following formula in Access 2007 to determine how many years >someone has been with the company. > >----- > >Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) > >----- > >This formula is partially accurate, but not completely because, for example, >if someone was hired on 8/24/09 and today's date is 5/27/10, it says that >person has worked here for a year (the value result = 1). But because it >technically hasn't been a full year, the number isn't accurate. I would like >for it to return a value = 0 (zero) if it hasn't been a full year between the >two dates. > >Is there something I can add to this formula to get an accurate year >difference? One way: Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) - IIF(Format([Hire Date], "mmdd") > Format([TodaysDate], "mmdd"), 1, 0) You can of course use the builtin Date() function in place of [TodaysDate] if you really want the current date. -- John W. Vinson [MVP]
|
Pages: 1 Prev: How can I scroll page up/down with arrow keys Next: label formating |