Results 1 to 7 of 7
  1. #1
    JimO is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    126

    Format display of Date Diff

    Using



    Code:
    =DateDiff("m",[Shot],Now())/12
    gives me the total difference in months, I would like to show the result as years and months rathes than total months.

    Thanks Jim O

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Issue is How many days in a month? Since they vary, it isn't a simple arithmetic thing.

    You could work in days and divide by some factor, but again different number of days per month.
    But, if you divide by 365 or 365.25 you could get an approximation of years and part years.
    Guess it really depends on your requirement.

    Now() gives Date and Time--is that required?
    If you use Date(), you get date only.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe datediff("yyyy",[Shot],date) & " - " & int(datediff("m",[Shot],date)/12) ?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    JimO is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    126
    Code:
    =DateDiff("m",[BP],Now())/12
    Displays total months but I would like to see full years and months rounded to the nearest month. 20.6 would become 20 yr 7 mo.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by JimO View Post
    Code:
    =DateDiff("m",[BP],Now())/12
    Displays total months but I would like to see full years and months rounded to the nearest month. 20.6 would become 20 yr 7 mo.
    That is addressed to whom? If the requirement has changed to include part months greater than .5 then swap the Int for Round and see if that works for you.

    datediff("yyyy",[Shot],date) & " - " & Round(datediff("m",[Shot],date)/12)

    Rounding can produce unexpected results but should be satisfactory for this.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I concocted this to work with Dates less than today (eg historic)

    Code:
    Int(DateDiff("m", shot, Now()) / 12) & " years  and " & _
       Int(((DateDiff("m", shot, Now) / 12) - (Int(DateDiff("m", shot, Now()) / 12))) * 365) \ 30 & "  month(s)"
    which produced this with some sample dates

    Period
    from 21-Aug-97 till 16-Sep-19 Is 22 years and 1 month(s)
    from 16-Sep-17 till 16-Sep-19 Is 2 years and 0 month(s)
    from 06-Apr-09 till 16-Sep-19 Is 10 years and 5 month(s)
    from 21-Jan-19 till 16-Sep-19 Is 0 years and 8 month(s)

  7. #7
    JimO is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    126
    Thank you very much, that works great.

    Jim O

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-30-2016, 10:27 AM
  2. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  3. Replies: 0
    Last Post: 11-19-2014, 05:47 AM
  4. Date Format Display
    By drunkenneo in forum Access
    Replies: 5
    Last Post: 06-20-2013, 07:06 AM
  5. Date Entry in one format display in another
    By mikethebass in forum Forms
    Replies: 1
    Last Post: 01-22-2012, 05:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums