Calculating difference between dates

[Guest_imported]

I have two fields: StartDate and EndDate.

I would like to have a field that will show the number of months difference (EndDate - StartDate = X months) rounded off to the nearest month (guess that would become a problem depending on the month but ...)

I'm not familiar enough with the Date commands to accomplish this easily.

Any ideas?

 

[jfhewitt]

Go into Help/Index. Type or select "DateDiff". Follow instructions.

 

[Guest_imported]

Thanks. It was what I was looking for.

Now I have another question though.

The DateDiff finds the difference of the dates Dec 1/03 - Nov 30/00 = 59 months . This is obviously incorrect as it is 60 months.

However it correctly calculates that Dec 1/03 - Dec 1/00 = 60 months.

What gives?

 

[MichaelRed]

Go back to the help. It does not EXPLICITLY state it, but if you read carefully the implication is that it returns a WHOLE number, without any rounding. So it is - in your example - not looking at anything but the month an year portion of the dates. It is NOT attempting to interpert the fractional (# of days) part of the date.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[TheInsider]

Hi,
This might help. This is the function that I use to return the client's age (in years), you can modify it to return months though:

Public Function Age(DOB, Optional vDate)
If Not IsDate(vDate) Then vDate = date

If IsDate(DOB) Then
Age = DateDiff("yyyy", DOB, vDate) + (DateSerial(Year(vDate), Month(DOB), Day(DOB)) > vDate)
Else
Age = Null
End If
End Function Rob Marriott
robert_a_marriott@yahoo.com

Hire me! Full-time, contract, whatever...shhh don't let my current employer know I said that.

 

[Guest_imported]

Thamks for the input
I decided to avoid using any lengthy calculations and used a calculation that seems to work fine as long as the dates aren't spread apart by several decades!

=DateDiff("d",[StartDate],[EndDate])/30.5

I just set my field so it doesn't have any decimal places so it rounds it to the nearest number. Since the date range I'm working with is usually less than 6 years, this method will work just fine.

 

[Guest_imported]

Thanks for the input
I decided to avoid using any lengthy calculations and used a calculation that seems to work fine as long as the dates aren't spread apart by several decades!

=DateDiff("d",[StartDate],[EndDate])/30.5

I just set my field so it doesn't have any decimal places so it rounds it to the nearest number. Since the date range I'm working with is usually less than 6 years, this method will work just fine.

 

[Guest_imported]

Sorry about the double post. Just had a little case of deja vu!