Number of months between dates

[srbooth]

Hi

Can anyone help me with this?

I want to be able to calculate the number of months between certain dates held in a table. ie StartDate and CurrentDate.

Thanks

 

[Rabbie]

Have you looked at the DateDiff function?

 

[boblarson]

What is CurrentDate? Is it a field in your table, or are you just referring to the actual "current date" as if it were today?

And I second Rabbie's question about the DateDiff function.

 

[srbooth]

Thanks managed to work it out the DateDiff did the trick

 

[raskew]

Hi -

Potential problem here, depending on what you're looking for. The DateDiff() function merely subtracts the month numbers, without regard to days. This has been a perpetual problem in computing ages in years.

Here's an example:

? datediff("m", #1/15/09#, #4/1/09#)
3

...when actually 3 full months won't occur until 4/15/09 (or 4/14/09, depending how you look at it).

If this could be a problem, here's a way around it:

DateStart = #1/15/09#
DateEnd = #4/1/09#

? datediff("m", datestart, dateend) + (day(datestart)>=day(dateend))
2

The + (day(datestart)>=day(dateend)) is a Boolean statement which returns -1 if true, 0 if false.

HTH - Bob