Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Unanswered: Subtracting dates and then formatting into years and months

    I would have thought this would have been simple but can't figure it out.
    I have a series of dates (DatePurchased) on a report, I have a box next to these dates that calculates today less the date purchased (Date()-DatePurchased). This gives me result as integer number of days. I want the result formatted in numbers of years and months.
    eg if the DatePurchased is 15/09/02 I want the box to show 1yr 1mnth (Date()-DatePurchased)
    Anybody help?
    thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Subtracting dates and then formatting into years and months

    Originally posted by BigMrC
    I would have thought this would have been simple but can't figure it out.
    I have a series of dates (DatePurchased) on a report, I have a box next to these dates that calculates today less the date purchased (Date()-DatePurchased). This gives me result as integer number of days. I want the result formatted in numbers of years and months.
    eg if the DatePurchased is 15/09/02 I want the box to show 1yr 1mnth (Date()-DatePurchased)
    Anybody help?
    thanks
    Take a look at Access-help and search for "DateDiff".
    This might help.

  3. #3
    Join Date
    Feb 2003
    Posts
    15
    I searched for DateDiff on Access help and no results?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access 2000 help has to be one of the worst pieces of garbage it's ever been my misfortune to encounter, and if i ever meet the persons who designed that mess, i shall take great delight in punching them right in the nose

    try this reference: Date functions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by BigMrC
    I searched for DateDiff on Access help and no results?
    I wanted to copy/paste my helppages, but I use a Dutch Office-version, so it might be of no help.
    Maybe someone else can do this for you.

  6. #6
    Join Date
    Feb 2003
    Posts
    15
    thanks for that, the reference to the Date Functions - I can certainly get the total number of months between the two dates no problem using DateDiff, but how do I get years and months, eg returns 1yr 2months as the answer to Date()-DatePurchased if DatePurchased was August02

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure if there's an easier way, but what i'd do involves the YEAR, MONTH, and DAY functions

    YEAR(date()) - YEAR(DatePurchased) give the number of years difference

    aside: but so does DATEDIFF("y"...)

    anyhow, you string a great bunch of these together in multiply-nested IIFs

    the logic would be horrendous

    i'd have to be pushed pretty hard to write a solution like that

    not only is it hard to code, but i think giving a date interval as "X years, Y months, Z days" is ugly

    what i'd probably do is take the difference in days and divide by 365.25 and then give the answer as "approximately 1.2 years"

  8. #8
    Join Date
    Feb 2003
    Posts
    15
    that's kind of what I did, just wondered if there was something inbuilt, thanks for your help anyway..

  9. #9
    Join Date
    Feb 2003
    Posts
    15
    actually, I'll try one more thing - how do you get the remainder from a calculation?
    eg if I had something that gave the result 1.8 how do I extract the .8 bit to use in another calculation?

  10. #10
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by BigMrC
    actually, I'll try one more thing - how do you get the remainder from a calculation?
    eg if I had something that gave the result 1.8 how do I extract the .8 bit to use in another calculation?
    use 9\5 instead of 9/5

  11. #11
    Join Date
    Feb 2003
    Posts
    15
    cracked it, have one box for the number of years elapsed which is easy enough, then another box for the number of months with the following:

    Fix(12*(((Date()-[DatePurchased])/31)/12-(((Date()-[DatePurchased])\31)\12)))

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, despite the fact that i think it's ugly, nevertheless it was a challenge, so i tried it, tested it, and it works quite nicely --
    Code:
    SELECT somedate
    , '' 
    & DATEDIFf("yyyy",somedate,date()) & ' year'
    & IIF ( DATEDIFf("yyyy",somedate,date())=1, ', ', 's, ' )
    & DATEDIFf("m",somedate,date()) MOD 12 & ' month'
    & IIF ( DATEDIFf("m",somedate,date()) MOD 12 =1, ', ', 's, ' )
    & IIF ( DAY(date()) >= DAY(somedate)
       , DAY(date()) - DAY(somedate)
       , DAY(date()) + DAY( DateSerial( YEAR(somedate), Month(somedate)+1, 0 ) )
                     - DAY(somedate)
          ) & ' day'
    & IIF (     
      IIF ( DAY(date()) >= DAY(somedate)
       , DAY(date()) - DAY(somedate)
       , DAY(date()) + DAY( DateSerial( YEAR(somedate), Month(somedate)+1, 0 ) )
                     - DAY(somedate)
          ) = 1, '', 's' ) as elapsed
    FROM somedates;
    sample results:
    Code:
    somedate     elapsed
    2003-09-09   0 years, 1 month, 29 days
    2003-02-28   0 years, 8 months, 8 days
    2000-02-29   3 years, 8 months, 8 days
    1999-12-31   4 years, 10 months, 8 days
    1999-10-08   4 years, 0 months, 0 days
    2000-10-08   3 years, 0 months, 0 days
    2004-10-08   -1 years, 0 months, 0 days
    2003-09-08   0 years, 1 month, 0 days
    2003-09-10   0 years, 1 month, 28 days
    2003-09-11   0 years, 1 month, 27 days
    2003-12-08   0 years, -2 months, 0 days
    2002-08-06   1 year, 2 months, 2 days
    2003-10-07   0 years, 0 months, 1 day
    2000-01-07   3 years, 9 months, 1 day
    rudy
    http://r937.com/

Posting Permissions

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