DATEDIFF Help

[Caddie]

Hi All -

I've created a contract management database, which is working great. Now I'm in the process of creating reports and need help with something.

I need to calculate the remaining term of contracts (in months). Assuming each contract is a 3 year term, and there are only 2 renewal options, month to month or 1 year, here is what I've got so far:

IIf([Service Details]![End Date]>Date(),DateDiff("m",Date(),[Service Details]![End Date]),IIf([Contract Details]![Renewal Term]="Month to Month",1,0))

If the original contract term has not expired then End Date - Today's Date = XX (in months)

If the original contract term has expired and the renewal term is month to month then = 1 (1 month)

Where I'm having trouble is if the renewal term is 1 year. How would I go about calculating the months remaining, because the contract could have expired 2 years ago, so the end date would be January 15, 2007 and today's date is March 18, 2010, so the new expiry date would be January 15, 2011 and I would do the datediff with those to dates, but I don't know who to write the formula for it.

Hope this makes sense. Pleas let me know.

Thanks!

 

[gemma-the-husky]

i would do it in several bits - rather than having complicated nested comparisons

have one column for expired, and just set true or false as the criteria to include exclude etc

expired: enddate>date

do the same with the other bits

============
much easier than trying to evaluate everything in a single statement

 

[Caddie]

Thanks, that's a good idea, that's what I'm going to do.

One other thing I noticed is that when using DATEDIFF "m" it rounds to the nearest month, it doesn't account for partial months using decimals. How would I do this?

Jan 1 to Feb 1 = 1 Month
Jan 1 to Feb 15 = 1.5 months

that type of thing.