Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return Number of Months Between 2 Dates? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    Using Post: Re: How to find whole months only (Access 2k Win 2k)
    <post#=393727>post 393727</post#> re: 393722 from dazednconfused

    I have:

    dtmVaccinExpire=1/5/7
    dtmExpireDate=1/31/8

    DateDiff("m", dtmVaccinExpire, dtmExpireDate) + (Day(dtmVaccinExpire) > Day(dtmExpireDate))

    The above code returns 12

    What do I have to change so it returns 12.5?

    Thanks, John

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

    Re: Return Number of Months Between 2 Dates? (a2k (9.0.6926) SP-3 Jet 4.0

    There are 12 months and 26 days between the two dates. I don't see how 26 days translates to .5. Can you explain?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number of Months Between 2 Dates? (a2k (9.0.6926) SP-3 Jet 4.0

    Was a typo

    dtmVaccinExpire=1/15/7
    dtmExpireDate=1/31/8
    looking for 12.5

    Other exampales:

    dtmVaccinExpire=1/23/7
    dtmExpireDate=1/31/8
    looking for 12.3

    dtmVaccinExpire=10/24/6
    dtmExpireDate=10/31/7
    looking for 12.2

    dtmVaccinExpire=1/30/7
    dtmExpireDate=1/31/8
    looking for 12.0

    John

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

    Re: Return Number of Months Between 2 Dates? (a2k (9.0.6926) SP-3 Jet 4.0

    A relatively simple way would be
    <code>
    ([dtmExpireDate]-[dtmVaccineExpire])*12/365.25
    </code>
    Format the result as Fixed with 1 decimal. Or if you prefer to round the result itself, use
    <code>
    Round(([dtmExpireDate]-[dtmVaccineExpire])*12/365.25,1)
    </code>
    Note: this is not 100% accurate, but it should be good enough for most purposes.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return Number of Months Between 2 Dates? (a2k

    Howzabout

    12*(DateDiff("w",[dtmVaccinExpire],[dtmExpireDate])+(Day([dtmVaccinExpire])>Day([dtmExpireDate])))/52

    Converting it to the number of weeks multiplying by 12 and dividing by 52 it gives 12.69 if created to have properties of Fixed and 2 decimal points
    Jerry

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return Number of Months Between 2 Dates? (a2k (9.0.6926) SP-3 Jet 4.0

    Thank you Hans & Jezza for the solutions

    John

Posting Permissions

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