Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Convert # days to years and months ((97 and 2k))

    Is there a feature in Access that will take the difference between two dates and present it in years and
    months?

    For example, if user wants to calculate the amount of time an employee has been with the company using today's date and the hire date. Before creating additional code to calculate years (/365) and months (divide remainder by 30) I wanted to see if Access had a feature built-in.

    TIA
    Kwvh

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Convert # days to years and months ((97 and 2k))

    I believe you should be able to use the DateDiff() function to calculate the difference. It may require a bit of recursive logic to get the Years separate from the months, and it would give you days as well, so you may want to do some rounding, but check out the function and see if it will do the trick. If you get stuck, post back - lots of loungers know tricks for these kind of things.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Convert # days to years and months ((97 and 2k))

    Thanks Wendell,

    The DateDiff() does give me the difference but does not give me years and months, at least not that I can find.

    I am seeking the value to include in a report. The report contains several individuals with some demographic information. Included in the needed information is the the length of time in years and months that the employee has worked for the company. I found some nice snippets of code in the thread 187447, but I don't know if that will do what I need, nor how to incorporate the function into fields on a report.

    Sorry to be so ignorant, but I am trying to learn.

    TIA

    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert # days to years and months ((97 and 2k))

    Take a look at <post#=159291>post 159291</post#>. I attached a zipped Access 97 database that demonstrates how to calculate the age of a person in months and years. This query can be used as record source of a report.

    The "formula" to calculate age in years is

    AgeYearsateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))

    The "formula" to calculate age in months is

    AgeMonthsMonth(Date())-Month([DOB])+12+(Day(Date())<Day([DOB]))) Mod 12

    In this example, DOB is the date-of-birth, but it might as well be the date employment started. If you don't want to calculate the period up to todat, use an end-of-employment field instead of Date() in the above expressions.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Convert # days to years and months ((97 and 2k))

    Hans has given you a couple of good examples - let me see if I can fill in the blanks a bit:

    To calculate the difference in Years between two dates, you use the DateDiff with the option to return Years.

    You then add that many years to the original date (using DateAdd()) and see if it is in the future - If so you want to subtract one from the DateDiff result because the DateDiff for years assumes that the older year started on 1/1/YYYY rather than the date you gave it.

    One you have the correct age in years, you add that to the older date to get the most recent anniversary of the date.

    Having that you compare the anniversary date with the current date to calculate the age in months, again using DateDiff. Hope this helps it make some sense.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •