|
|
|
difference in months |
|---|
| message from =?Utf-8?B?bWlrZQ==?= on 10 May 2004 |
can anyone tell me how to calculate difference in months (interval) down to the fraction of a month?
|
| =?Utf-8?B?Q29ycmluYQ==?= replied to =?Utf-8?B?bWlrZQ==?= on 10 May 2004 |
Assuming your fraction of a month would be referring to the second/later month, the approach I would take to this is to calculate the difference in whole months, then add the difference in days for the remainder partial month (if the day number of the second month is less than the day number of the first month then the difference will be negative, so the resulting number will be subtracted) divided by the total days in the second month. Since months vary in the number of days, you would need to include how many days the second month has. Here's an example: DateDiff("m",[date1],[date2])+(Day([date2])-Day([date1]))/Switch(Month([date2])=1,31,Month([date2])=2,28,Month([date2])=3,31,Month([date2])=4,30,Month([date2])=5,31,Month([date2])=6,30,Month([date2])=7,31,Month([date2])=8,31,Month([date2])=9,30,Month([date2])=10,31,Month([date2])=11,30,Month([date2])=12,31). The example doesn't take into account leap years, but you could nest an Iif function for February if you wanted to.
|
| Steve Schapel replied to =?Utf-8?B?bWlrZQ==?= on 11 May 2004 |
Mike,
Because the number of days per month varies, the idea of "fraction of a
([SecondDate]-[FirstDate])/30.437
If this suggestion does not meet your needs, please post back with a
couple of examples showing the result you need.
|
| Graham R Seach replied to =?Utf-8?B?bWlrZQ==?= on 11 May 2004 |
Mike,
http://www.pacificdb.com.au/MVP/Code/Age2.htm
Call this function using the following syntax:
AgeYMD(Date1, Date(), True, True, False)
It will return the difference in the following format:
yy.mm.dd
You can then parse the returned value to extract the years, months and days,
divide a few things, and Bob's your uncle.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
"mike" <anonymous@discussions.microsoft.com> wrote in message
news:A6A8F79D-3CA2-41A9-B6B2-F039D4D2FAD6@microsoft.com...
to the fraction of a month?
|
|
