Results 1 to 6 of 6
  1. #1
    alabamax is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4

    Number of months in a specific time range

    Hello everyone,



    I'm trying to calculate the number of months in a specific time range. For instance, I want to know the number of months between two dates but only in the first semester of a specific year. It means the module has to exclude, or reference 0, two dates that are not in that time range.

    For example : the number of months in the first semester of 2018
    1/1/2018 - 30/6/2018 = 6 months
    1/3/2018 - 31/5/2018 = 3 months (march, april, may)
    1/7/2018 - 01/10/2018 = 0 month

    I'm still new to vba modules and I'm not sure I have to use them because it seems like a simple request, and yet I have been struggling for quite some time to find the solution.

    Thank you for your help

    Maxime

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    investigate using the datediff function. Your example is a bit vague - you talk about a specific time range, but provide full months in your example - so is it full months you will always use, or would another example start say 15th March and end 12th June? In which case what do you consider to be the number of months - complete months (2) or rounded to 3 months (to 15th May) or 4 months?

    You also haven't explained when your semester starts and ends? Implication from your example is it ends on 1st July

  3. #3
    alabamax is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    Sorry indeed, I need to clarify this.
    In my case, my semesters start from january to june and july to december. As far the months are concerned, the first date starts with the first day of the month and the last date ends with the last day of the month. To be more specific, it deals with lease contracts. The leases always start on the first day of the month and always end on the last day of the month (in-between in my case)

    So to correct my example : it would be a function that could help me identify in this case the number of months I have during the first semester (January-june) of 2018.
    1/1/2018 - 30/6/2018 = 6 months
    1/3/2018 - 31/5/2018 = 3 months (march, april, may)
    1/7/2018 - 30/10/2018 = 0 month


  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in a query your sql might look something like this

    Code:
    SELECT lease, sDate, EDate, Datediff("m",sDate,EDate)+1 as months
    FROM tblLeases
    WHERE sDate>=[Enter Start Date] AND eDate<=[Enter End Date]

  5. #5
    alabamax is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    thank you very much !

  6. #6
    alabamax is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    thank you very much !!

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

Similar Threads

  1. Limit Data to 12 months - Date range -HELP
    By fluffyvampirekitten in forum Access
    Replies: 5
    Last Post: 01-27-2016, 03:20 AM
  2. Replies: 6
    Last Post: 11-25-2013, 10:53 AM
  3. Selecting from specific Date and Time range
    By LindaRuble in forum Programming
    Replies: 1
    Last Post: 05-15-2013, 07:37 AM
  4. Replies: 3
    Last Post: 05-12-2012, 04:52 AM
  5. Dividing time by a specific number
    By harrig04 in forum Queries
    Replies: 1
    Last Post: 07-05-2011, 09:09 AM

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