Calculating a Month

  • Thread starter Thread starter grep
  • Start date Start date
G

grep

I'm trying to calculate a month's time for billing purposes. I'm having
a hard time with it, and it should be easy. I know I'm missing something.

I want to know how many months it's been since CommenceDate. DateDiff
doesn't work for me (at least not directly) because 11/29/2005 and
12/02/2005 are in different months, but they're not a month apart, but
DateDiff shows the difference as 1 month.

I'm not sure this is so much a query question, but since I'm doing this
calculation in a query, I figured I'd ask it here.

grep
 
grep said:
I'm trying to calculate a month's time for billing purposes. I'm having
a hard time with it, and it should be easy. I know I'm missing something.

I want to know how many months it's been since CommenceDate. DateDiff
doesn't work for me (at least not directly) because 11/29/2005 and
12/02/2005 are in different months, but they're not a month apart, but
DateDiff shows the difference as 1 month.

I'm not sure this is so much a query question, but since I'm doing this
calculation in a query, I figured I'd ask it here.

grep
Click to expand...

grep,

Calculate the number of days using DateDiff, and then divide by the
number of days in each billing cycle. That will be the number of
"billing months". If that isn't good enough, write a VBA function
that does the calculation exactly as you need.


Sincerely,

Chris O.
 
That's the way DateDiff works (and it's even implied in the Help file: see,
for example,
http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctdatediff.asp,
where it says "When comparing December 31 to January 1 of the immediately
succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day
has elapsed.")

To know whether it's truly been more than one month, you need to also look
at the day, not just the month:

DateDiff("m", [CommenceDate], Date()) - _
IIf(Day(Date()) < Day([CommenceDate]), 1, 0)
 
This is ALMOST the solution I'm looking for, and was dancing around all
last night. There are two problems:

1. If I'm looking at the record prior to the CommenceDate,
it shows -1. It should show 0. This is easily remedied,
however, by putting the whole thing into another IIf, that
checks for Date() < [CommenceDate]. If true, 0, otherwise,
the rest of the statement you wrote out.

2. The more serious problem is that if CommenceDate is Dec. 5,
that means that, as of Dec. 5, a specified amount is charged.
But because of the calculation, a query on Dec. 31 produces
the value 0. I think you did help me solve it, though. The
answer is essentially the opposite of your suggestion. It's
IIf(Date()<[CommenceDate],0,DateDiff("m",[CommenceDate],Date())+ _
IIf(Day(Date())<Day([CommenceDate]),0,1))

I've just tested this a bit, and it appears to produce exactly what I'm
looking for. Thanks so much for the help. I really wrestled with this
for 3 hours last night before posting.

grep
 
Back
Top