Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Red face Unanswered: How to calculate the number of complete months

    I researched the internet and found that by adding the following code, I can get the correct number of complete months BUT I DO NOT KNOW WHY.

    DateDiff("m",[Start Date],[End Date])+(DatePart("d",[Start Date])>DatePart("d",[End Date]))+1

    This is the example:-

    Start Date: 15 January 2012
    End Date: 14 March 2012
    The number of complete months = 1

    Expression: DateDiff("m",[Start Date],[End Date])
    However, the above expression returns the value = 2
    I understand that the first part takes the number of the month (eg. 3 for March, and 1 for January)

    Expression: DateDiff("m",[Start Date],[End Date])+(DatePart("d",[Start Date])>DatePart("d",[End Date]))+1
    I researched the internet and the above expression gives the correct number of complete months BUT I DO NOT KNOW WHY?

    Is it possible that somebody could explain what the DatePart of the expression is doing?

    Thanks.
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Break it down into parts.
    DateDiff gives the difference between two dates in the units specified, in this case months. Your example gives an answer of 2.
    The DatePart comparison looks at the day values. If the day value of the start date is greater than the day value of the end date, this comparison returns True, which numerically is interpreted by VBA -1. If the day values are the same or the start date day value is less than then end date's, this comparison returns False, or 0. This step is added because of something that is counter-intuitive to me, because it works backwards. If you have a greater start date day value than end date day value, your duration includes an extra month. This step of adding one to the logical comparison between the values accounts for this.
    I feel like I'm not explaining this very well, so have a play with the following function:
    Code:
    Function DateDifferences(datStart As Date, datEnd As Date)
        '2012-Nov-11
        'Run through the stages of a compound function
        
        Debug.Print "Workings of DateDiff('m',[Start Date],[End Date])+(DatePart('d',[Start Date])>DatePart('d',[End Date]))+1"
        Debug.Print "Start date: " & Format$(datStart, "yyyy-mm-dd")
        Debug.Print "End date: " & Format$(datEnd, "yyyy-mm-dd")
        Debug.Print "DateDiff('m',[Start Date],[End Date]) = " & DateDiff("m", datStart, datEnd)
        Debug.Print "DatePart('d',[Start Date]) = " & DatePart("d", datStart)
        Debug.Print "DatePart('d',[End Date]) = " & DatePart("d", datEnd)
        Debug.Print "DatePart('d',[Start Date])>DatePart('d',[End Date]) = " & DatePart("d", datStart) > DatePart("d", datEnd)
        Debug.Print "(DatePart('d',[Start Date])>DatePart('d',[End Date])) + 1 = " & (DatePart("d", datStart) > DatePart("d", datEnd)) + 1
        Debug.Print "Result is " & DateDiff("m", datStart, datEnd) + (DatePart("d", datStart) > DatePart("d", datEnd)) + 1
        
    End Function
    It shows its workings.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    weejas,

    thanks for your excellent reply.

    actually i managed to understand it from your description. Especially when i realised that the second part of the statement did NOT require an IF,THEN,ELSE statement. And that if True it would result in a -1 calculation.

    don't you just love it when something in your mind clicks

    thanks again.
    What would you attempt to do if you knew you would not fail?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help!

    I had to think about it for a moment, especially as I've been coding a lot in Lotus Notes formula language, in which True = 1...

    And yes, I rather like the moment when the penny drops!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    I'm not sure to understand why you built some expressions the way you did:
    Code:

    [Contract Months]: DateDiff("m",[Start Date (Original)],[End Date (Original)])
    +(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)]))
    +1

    a) DateDiff("m",[Start Date (Original)],[End Date (Original)]): difference in months between [Start Date (Original)] and [End Date (Original)].

    b) +(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)])) In this expression, > (greater than) is an evaluation operator that can return True (-1) if the day part in [Start Date (Original)] is greater than the day part in [End Date (Original)], False (0) if the day part in [Start Date (Original)] is less than or equal to the day part in [End Date (Original)], or Null if [Start Date (Original)], [End Date (Original)] or both are null.

    This means that in your expression you add -1 (i.e. substract 1), add 0 (i.e. do nothing) or add Null (i.e. render the whole expression Null) to the difference in months between [Start Date (Original)] and [End Date (Original)].

    c) +1 Then you add 1 to the expression to get [Contract Months].

    The same principle is used for [Months Today]. Is there a rational explanation for such expressions?

    It looks as if you were mixing the syntax of the DateDiff() function with the syntax of the IIF() function together.
    My target is to calculate the “Rent Owed”.
    I only have the following fields to work with: Start Date, End Date, Monthly Rent Amount.

    So I am trying to multiply the Monthly Rent Amount by the number of months since Start Date (potential problem is the MAXIMUM amount of months. Eg. if a Contract starts in 2007 and ends in 2008, I don’t want to calculate from 2007 until Today (eg. 2012). But I should only calculate from 2007 to 2008.

    So, in my formulas, I have been trying to calculate the MAXIMUM number of months.
    A) Calculate the number of months from Start Date to End Date [Contract Months].
    B) Calculate the number of months from Start Date until Today [Months Today].
    C) Choose (B) iif (B) is lower than (A). Eg. If the Contract has not yet expired. Or choose (A) if the Contract has already expired.

    I am open to views about how complicated I have made the formula. It makes my brain bubble sometimes!!
    Last edited by reddevil1; 11-14-12 at 12:56.
    What would you attempt to do if you knew you would not fail?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    DateDiff("m", DateAdd("d", -1, d1), DateAdd("d", 1, d2)) - 1
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Thanks for your suggestion, Sinndho.

    I have not fully understood how the calculation works but there are differing results:-

    These are d1 and d2 dates
    01Jan10 to 30Jun10: Formula returns 6, Actual months = 6
    03Feb10 to 03Feb11: Formula returns 11, Actual months = 13
    10Oct12 to 10Nov13: Formula returns 12, Actual months = 14
    29Oct12 to 28Oct13: Formula returns 11, Actual months = 12
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry, by "full" or "entire", I thought you meant "whole" as in "from the first day of a month to the last day of the same month", hence the solution I provided.

    There is a rather easy solution when working with a SQL Server, using a Calendar table, but I dont see any easy way to compute what you want in Access SQL, at least without calling a VBA function in the query.
    Have a nice day!

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    That's fine, Sinndho.

    Although my original formula is quite long-winded and complicated, at least I can understand how it is calculated, and I can adjust it in the future, if necessary.

    Cheers anyway.
    Last edited by reddevil1; 11-17-12 at 12:54.
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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