Results 1 to 4 of 4
  1. #1
    Raluca1 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    2

    Calculate DateDiff in months, only for current year, from a Start date and End date from 2013to2018

    Hi !
    Can you help me calculate the length of a contract in months, only for this year, where for eg, one can be valid from 11/1/2014 to 10/31/2016 and another from 8/31/2016 to 12/31/2018 ?
    I'm a beginner


    Tnx !

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not clear on what you want. Do you want the number of months between 11/1/2014 to 10/31/2016? or just between 1/1/2016 and 10/31/2016?
    The general function to get the difference in months is:
    DateDiff(interval, date1, date2)

    If you mean between #1/1/2016# and #10/31/2016#, you will need to add 1. I manually calculated the number of months to be 10.
    Try
    Code:
    DateDiff("m", #1/1/2016#, #10/31/2016#)+1
    You might have to use VBA code to be able to calculate the number of months.

  3. #3
    Raluca1 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    2

    Calculate DateDiff in months, only for current year, from a Start date and End date from 2013to2018

    Quote Originally Posted by ssanfu View Post
    Not clear on what you want. Do you want the number of months between 11/1/2014 to 10/31/2016? or just between 1/1/2016 and 10/31/2016?
    The general function to get the difference in months is:
    DateDiff(interval, date1, date2)

    If you mean between #1/1/2016# and #10/31/2016#, you will need to add 1. I manually calculated the number of months to be 10.
    Try
    Code:
    DateDiff("m", #1/1/2016#, #10/31/2016#)+1
    You might have to use VBA code to be able to calculate the number of months.
    Hi!
    Thank you for you answer. But i need the number of months, only for 2016, for:

    Valid from Valid to len (Years)
    9/1/2013 9/1/2016 3
    1/1/2015 12/31/2017 2
    8/1/2014 12/31/2016 2
    1/1/2016 12/31/2017 1
    1/1/2016 12/31/2017 1
    1/1/2015 3/31/2016 1
    4/2/2014 3/27/2016 2
    4/2/2014 3/27/2016 2
    3/1/2015 2/28/2017 2
    6/1/2013 6/1/2016 3
    8/1/2013 1/1/2016 3
    10/1/2013 10/1/2016 3
    10/1/2013 10/1/2016 3
    10/1/2016 12/31/2016 0
    1/1/2015 12/31/2016 1
    1/1/2015 12/31/2016 1
    1/1/2014 12/31/2016 2
    10/20/2016 12/31/2017 1

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Still not sure what you want. Is this in code or in a query?
    I'm going to make several assumptions...
    The examples you provided are in a table.
    The calculations for number of months will be done in a query.
    ALL "Valid_from" dates will begin on the first of a month.
    ALL "Valid_to" dates will end on the last day of the month.

    Right off the bat, you have problems. In the first row of dates, the "Valid_to" date is the first of a month. not the last day of the month.
    In the last row of dates, the "Valid_from" is 10/20/2016. So do you count Oct as a whole month?

    Create a new query.
    Add your table that has "Valid_from" and "Valid_to" fields. Note that there are no spaces in the field names.
    In a empty column, paste in the following:
    Code:
    LenInMths: DateDiff("m",IIf([Valid_from]<#1/1/2016#,#1/1/2016#,[valid_from]),IIf([Valid_to]>#12/31/2016#,#12/31/2016#,[valid_to]))+1
    The dates for Jan 1, 2016 and 12/31/2016 are hard coded. I would rather have them reference text boxes on a form, but you can make that change later.
    Code:
    Valid_from Valid_to lenYears LenInMths
    9/1/2013 9/1/2016 3 9
    1/1/2015 12/31/2017 2 12
    8/1/2014 12/31/2016 2 12
    1/1/2016 12/31/2017 1 12
    1/1/2016 12/31/2017 1 12
    1/1/2015 3/31/2016 1 3
    4/2/2014 3/27/2016 2 3
    4/2/2014 3/27/2016 2 3
    3/1/2015 2/28/2017 2 12
    6/1/2013 6/1/2016 3 6
    8/1/2013 1/1/2016 3 1
    10/1/2013 10/1/2016 3 10
    10/1/2013 10/1/2016 3 10
    10/1/2016 12/31/2016 0 3
    1/1/2015 12/31/2016 1 12
    1/1/2015 12/31/2016 1 12
    1/1/2014 12/31/2016 2 12
    10/20/2016 12/31/2017 1 3
    Any of the Length in months that are RED, I would consider a problem. The dates in RED are the cause.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-23-2014, 02:06 PM
  2. Replies: 3
    Last Post: 09-14-2013, 10:36 AM
  3. DateDiff to include start date
    By AussieGal in forum Access
    Replies: 1
    Last Post: 05-07-2013, 02:50 PM
  4. Replies: 11
    Last Post: 08-04-2010, 04:26 PM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

Tags for this Thread

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