471,073 Members | 1,114 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Number of months between dates

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
3 3207
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
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
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.

Similar topics

2 posts views Thread by Christopher Benson-Manica | last post: by
29 posts views Thread by james | last post: by
2 posts views Thread by bufbec | last post: by
3 posts views Thread by bootzwiz | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.