Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    NJ, USA
    Thanked 0 Times in 0 Posts

    convert number of days to year, months, days (Access 2K)

    If I subtract Resignation date from Sworn in date I get a number of days between those two points. How do I convert that to years, months, and days. Thanks Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: convert number of days to year, months, days (Access 2K)

    The most elegant way to do this is to create VBA functions that hide the nasty details. But you can also create expressions to do this, for use in queries and in the control source of text boxes. Here are the expressions, for fields named Date1 and Date2; replace these by Sworn in date and Resignation date.

    Years: Year([Date2])-Year([Date1])+(Month([Date2])<Month([Date1]) Or Month([Date2])=Month([Date1]) And Day([Date2])<Day([Date1]))

    Months: (Month([Date2])-Month([Date1])+(Day([Date2])<Day([Date1]))+12) Mod 12

    Days: DateDiff("d",DateSerial(Year([Date2]),Month([Date2])+(Day([Date2])<Day([Date1])),Day([Date1])),[Date2])


  3. #3
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 470 Times in 387 Posts

    Re: convert number of days to year, months, days (Access 2K)

    Hi Fay,

    You can't reliably convert a number of days to an equivalent number of calendar years, months and days, because the answer depends on both the StartDate and EndDate (Sworn and Resignation in your example). You can do an approximation, though, based on a count of the number of days difference between the StartDate and the EndDate (ie EndDate-StartDate=Period), as in the following generic formulae:
    Years: =INT((Period+1)/365.25)
    Months: =INT(MOD(Period+1,365.25)*12/365.25)
    Days: =INT(Period-INT(Years*365.25+Months*365.25/12))
    Note: In this approach, the decision on whether to include or exclude the StartDate is taken when the Period is defined. The Period should be reduced by one day if the StartDate is to be excluded. The formula syntax may also be different in Access.

    If you want the real number of calendar years, months and days, an approach like the one outlined by Hans is needed, but you'd need to add one day if you want to count both the Sworn and Resignation dates (eg use Sworn date minus 1 day), or subtract one day if you don't want to count either of the Sworn and Resignation dates (eg use Resignation date minus 1 day).


    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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