Calculating number of complete months between two dates

  • Thread starter Thread starter EHPorter
  • Start date Start date
E

EHPorter

I'm trying to figure out an access expression that will calculate the number
of full, complete months between two dates. It is for certain mortgage
based financial calculations, which need to determine how many payments have
been missed.

Example:

Comparing the dates January 15, 2004 and April 16, 2004, the total number of
full months between the two dates is 3

BUT comparing the dates January 15, 2004 and April 14, 2004, the total
number of full months between the two dates is only 2

Any suggestions on this would be appreciated.
 
DateDiff("m", FirstDate, SecondDate) + _
(DatePart("d", FirstDate) > DatePart("d", SecondDate))



For example (rom Debug window):

FirstDate = #01/15/2004#
SecondDate = #04/16/2004#

?DateDiff("m", FirstDate, SecondDate) + _
(DatePart("d", FirstDate) > DatePart("d", SecondDate))
3

FirstDate = #01/16/2004#
SecondDate = #04/15/2004#

?DateDiff("m", FirstDate, SecondDate) + _
(DatePart("d", FirstDate) > DatePart("d", SecondDate))
2
 
I'm trying to figure out an access expression that will calculate the number
of full, complete months between two dates. It is for certain mortgage
based financial calculations, which need to determine how many payments have
been missed.

Example:

Comparing the dates January 15, 2004 and April 16, 2004, the total number of
full months between the two dates is 3

BUT comparing the dates January 15, 2004 and April 14, 2004, the total
number of full months between the two dates is only 2

Any suggestions on this would be appreciated.
Click to expand...

In a query?
Missed:DateDiff("m",[DueDate],#04/14/2004#)-IIf(Format([DueDate],"dd")>Format(#04/14/2004#,"dd"),1,0)

Missed:DateDiff("m",[DueDate],#04/16/2004#)-IIf(Format([DueDate],"dd")>Format(#04/16/2004#,"dd"),1,0)

If you are using the current date to compare to:
Missed:DateDiff("m",[DueDate],Date())-IIf(Format([DueDate],"dd")>Format(Date(),"dd"),1,0)

If you want to be prompted to enter the second date:
Missed:DateDiff("m",[DueDate],[Enter Date])
-IIf(Format([DueDate],"dd")>Format([Enter Date],"dd"),1,0)

You'll be prompted to enter the 04/14/2004 date.
 
Thank you!

I'm trying to convert over some old stuff set up in Borland's Paradox
Database many years ago. Plus, I'm not to experienced at this (I'm an end
user, not a programmer). The stuff I was trying to convert takes about 10
lines of code to do what the DateDiff function does in one expression.

I probably should have migrated over to Access a few years ago. Corel and
Paradox seem to be sinking fast.
 
Back
Top