# Thread: [RESOLVED] DateDiff calculation issue

1. ## [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. ## Re: DateDiff calculation issue

Originally Posted by chris223b
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. ## Re: DateDiff calculation issue

Originally Posted by chris223b
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. ## 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
•