By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Number of months between dates

P: n/a
I am trying to compute storage charges by getting the number of months
between dates and multiplying it by a rate. I cannot get a correct
number of months if the date is greater than a year ago. Here is the
formula I am using now to get the number of months: #ofMonths:
Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHeader].[createdate])-1.
It has worked for me until now because it never was greater than a
year before. Please help. I am a novice at this.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
MMFBprez wrote:
I am trying to compute storage charges by getting the number of months
between dates and multiplying it by a rate. I cannot get a correct
number of months if the date is greater than a year ago. Here is the
formula I am using now to get the number of months: #ofMonths:
Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHeader].[createdate])-1.
It has worked for me until now because it never was greater than a
year before. Please help. I am a novice at this.


In the debug window I entered
? datediff("m",Date(),Date()-396)

and it echoed back
-12

? datediff("m",Date(),Date()-396)
-13

? datediff("m",Date()-396,Date())
13
Nov 13 '05 #2

P: n/a
Try the DataDiff function
NumOfMonths: DateDiff("m",[CreateDate],[CloseDate])
You'll find information about the DateDiff function in the VBA help file.
Pres Alt + F11 to open the VBE window and then click on help.

Jeff
"MMFBprez" <tm******@mmforward.com> wrote in message
news:26**************************@posting.google.c om...
I am trying to compute storage charges by getting the number of months
between dates and multiplying it by a rate. I cannot get a correct
number of months if the date is greater than a year ago. Here is the
formula I am using now to get the number of months: #ofMonths:
Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHea
der].[createdate])-1. It has worked for me until now because it never was greater than a
year before. Please help. I am a novice at this.

Nov 13 '05 #3

P: n/a
If I read your formula correctly, it looks like you're 1st subtracting one
date from another (which yields days) and then using the month function on
the result, and then subtracting 1. This is a meaningless calculation. As
others have suggested, use the DateDiff() function. Or you could roll your
own with something like (air code):

Month(date1) - Month(date2) + (Year(date1) - Year(date2)) * 12

Fred
"MMFBprez" <tm******@mmforward.com> wrote in message
news:26**************************@posting.google.c om...
I am trying to compute storage charges by getting the number of months
between dates and multiplying it by a rate. I cannot get a correct
number of months if the date is greater than a year ago. Here is the
formula I am using now to get the number of months: #ofMonths:
Month([dbo_tblOutBoundTrackingHeader].[closedate]-[dbo_tblInboundTrackingHea
der].[createdate])-1. It has worked for me until now because it never was greater than a
year before. Please help. I am a novice at this.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.