Rounding up partial months

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using DateDiff in a query to give the number of months between 2 dates -
DateDiff("m",[FirstDate],[SecondDate]). If the number of days between each
date is less than 15, it rounds down to zero. I need it to round up any
fractions of months. Can anyone help?
I tried -Int(-DateDiff("m",[FirstDate],[SecondDate])]) but it didn't have
any effect on the result.
 
Lesley,

The DateDiff() function doesn't work as you indicated. If you use it to
find the difference in months, it considers the months and takes no
account of the number of days. So 29-Nov to 2-Dec is 3 days but
DateDiff will return 1 month, whereas 2-Nov to 30-Nov is 28 days, but
DateDiff will return 0 months. There will probably be a way to get the
result you want. It depends on how accurate you want to be, and how you
want to handle the differing number of days from month to month. Here
is a very rough approach, which will round to the nearest 30 day period...
CInt(([SecondDate]-[FirstDate])/30)
 
Thanks for the expanation. Now I at least know how DateDiff works and it is
obviously the wrong function for what I need.
Unfortunately I need more accuracy than your suggestion. I am doing tax
returns and if a "return mailed" date is, for example, after the "due date"
of October 15, it is considered 1 month late until Nov 15. Then on Nov 16 it
is 2 months late. If I use an average month of 30 days it will be wrong for
some days in both Feb and all 31 day months. I thought of trying to do
something with DateSerial and an embedded IIf statement so that if the day is
greater than 15 it returns the 15th of the following month--but I can't get
the syntax right. Can you help?
Thanks a lot
Lesley

Steve Schapel said:
Lesley,

The DateDiff() function doesn't work as you indicated. If you use it to
find the difference in months, it considers the months and takes no
account of the number of days. So 29-Nov to 2-Dec is 3 days but
DateDiff will return 1 month, whereas 2-Nov to 30-Nov is 28 days, but
DateDiff will return 0 months. There will probably be a way to get the
result you want. It depends on how accurate you want to be, and how you
want to handle the differing number of days from month to month. Here
is a very rough approach, which will round to the nearest 30 day period...
CInt(([SecondDate]-[FirstDate])/30)

--
Steve Schapel, Microsoft Access MVP

I am using DateDiff in a query to give the number of months between 2 dates -
DateDiff("m",[FirstDate],[SecondDate]). If the number of days between each
date is less than 15, it rounds down to zero. I need it to round up any
fractions of months. Can anyone help?
I tried -Int(-DateDiff("m",[FirstDate],[SecondDate])]) but it didn't have
any effect on the result.
Click to expand...
Click to expand...
 
Lesley,

You'd be amazed to know how much difference it makes if I know what you
are trying to achieve :-)

Try it like this...
DateDiff("m",[Due Date],[Return Mailed])-(Day([Return Mailed])>15)
 
Back
Top