473,403 Members | 2,323 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,403 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 3328
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Christopher Benson-Manica | last post by:
What would be the best way to convert a month number to its corresponding string? I.e, 3 -> 'March'. Is there a builtin function or must I use a lookup table or something? -- Christopher...
6
by: carl.barrett | last post by:
Hi, I have a continuous form based on a query ( I will also be creating a report based on the same query). There are 2 fields: Date Obtained and Date Of Expiry I want a further 3 columns...
9
by: Robin Tucker | last post by:
Hiya, I need to test "relative dates" in my program, such as "last six months" or "last 3 months" or "in the last week" etc. How can I do this with a DateTime structure? ie. If NodeDate...
29
by: james | last post by:
I have a problem that at first glance seems not that hard to figure out. But, so far, the answer has escaped me. I have an old database file that has the date(s) stored in it as number of days. An...
2
by: bufbec | last post by:
I have worked on this for hours and can't come up with a solution. Hope someone can help me. I have a table called TMBS_HMAUDIT_PARMS. this table contains data to tell me how often a person is...
6
by: Sridhar | last post by:
Hi, I need to display a calendar that shows all the months of an year. In that, I need to show different colors for certain events. I know how to display a calendar for a certain month but I am...
3
by: bootzwiz | last post by:
How can we find months difference between two dates. For Exp : Start Date : 05/03/2007 End Date : 06/10/2007 How to calucate months difference for any dates?.
10
by: karunajo | last post by:
hi, I want to calculate tne months between two dates.Actually i am using Asp.net2.0.Is it possible to calculate using Sql or Asp,net..I want to list the months betweeen two dates.Help me...
5
by: wespw1 | last post by:
I have a form where I have 2 dates: 06/27/2007 01/28/1996 What I need to get is the difference between the 2 dates in months. Or in otherwords I am trying to get the age of the 2 dates in...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

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.