dcsimg
Results 1 to 4 of 4

Thread: [RESOLVED] DateDiff calculation issue

  1. #1

    Thread Starter
    Member
    Join Date
    May 2018
    Posts
    37

    Resolved [RESOLVED] DateDiff calculation issue

    I'm trying to write a program to calculate monthly interest on a loan, adding interest on once a month based on the anniversary of the loan date. The first step to that is to calculate how many complete months have elapsed. Note that I need strict calendar months added so that, for a loan issued on 21 April, the interest will accrue on the 21st of every month allowing for different length months, I can't just use a fixed interval like 30 days unfortunately.

    I'm trying to use DateDiff like this:

    Code:
    ElapsedMonths = DateDiff("m", StartDate, Now())
    What I expected to happen with StartDate being set to 21/04/2019 is ElapsedMonths to return 0 until 20/05/2019 and then increment to 1 on 21/05/2019, 2 on 21/06/2019 etc.

    However with that code it is incrementing on the 1st of the month so I'm getting 0 until 30/04/2019, then 1 on 01/05/2019 and 2 on 01/06/2019.

    It seems to just be using the month property of StartDate rather than the full date for the calculation. I'm sure I'm missing something simple but I've been playing with this for ages and got nowhere!

  2. #2
    Lively Member
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    126

    Re: DateDiff calculation issue

    Quote Originally Posted by chris223b View Post
    It seems to just be using the month property of StartDate rather than the full date for the calculation. I'm sure I'm missing something simple but I've been playing with this for ages and got nowhere!
    I stumbled upon the very same (mis)behaviour recently. But then again, this is actually documented in the help file, albeit not in a very obvious way and right at the end of the description: "When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.".

    You're better off using DateAdd and iterating through the months, that works reliably.

    Wolfgang

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,376

    Re: DateDiff calculation issue

    Quote Originally Posted by chris223b View Post
    Code:
    ElapsedMonths = DateDiff("m", StartDate, Now())
    with that code it is incrementing on the 1st of the month so I'm getting 0 until 30/04/2019, then 1 on 01/05/2019 and 2 on 01/06/2019.
    Would the following help?
    Code:
    Function ElapsedMonths(ByVal StartDate As Date, ByVal CurDate As Date)
      ElapsedMonths = DateDiff("m", StartDate, CurDate) + CBool(Day(CurDate) < Day(StartDate))
    End Function
    Olaf

  4. #4

    Thread Starter
    Member
    Join Date
    May 2018
    Posts
    37

    Re: DateDiff calculation issue

    That works perfectly Olaf! Thanks very much!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width