Date Calculations
 

I have a worksheet with a beginning and ending date range. I need the number
of months for the range but also need them rounded up to the next month.

For example:

13-Jul-11 to 31-Jul-12 should calculate as 13 months and not the 12 provided
by the DATEDIF function.

In short, I need to round up to the next month for my calculations.

05-Mar-11 to 31-Dec-11 should return 10 full months and not 9.

Is there an easy way to do this?

Date Calculations
 

If you are happy to assume there are 30 days in a month, then this
approximation should work for you:

=ROUNDUP((B2-A2)/30,0)

assuming end date is in B2 and start date is in A2. If you want it
slightly more accurate you can make it 30.4.

Hope this helps.

Pete

On Jul 23, 11:11*pm, Tommy wrote:
I have a worksheet with a beginning and ending date range. I need the number
of months for the range but also need them rounded up to the next month.

For example:

13-Jul-11 to 31-Jul-12 should calculate as 13 months and not the 12 provided
by the DATEDIF function.

In short, I need to round up to the next month for my calculations.

05-Mar-11 to 31-Dec-11 should return 10 full months and not 9.

Is there an easy way to do this?

Date Calculations
 

On Wed, 23 Jul 2008 15:11:01 -0700, Tommy
wrote:

I have a worksheet with a beginning and ending date range. I need the number
of months for the range but also need them rounded up to the next month.

For example:

13-Jul-11 to 31-Jul-12 should calculate as 13 months and not the 12 provided
by the DATEDIF function.

In short, I need to round up to the next month for my calculations.

05-Mar-11 to 31-Dec-11 should return 10 full months and not 9.

Is there an easy way to do this?

If you can formulate some unambiguous rules, then you could do it.

But, since months can have 28, 29, 30 or 31 days, you need to be more specific.

What about:

15-Feb-11 to 14-Dec-11

and how did you arrive at that result?
--ron

Date Calculations
 

Add 1 maybe? There is a difference between subtraction and counting...
DATEDIF works by subtraction. Think of it this way.. the difference between
1 and 6 (January and June) is 5, but the number of months involved is 6... 1
greater than the difference.

Rick


"Tommy" wrote in message
...
I have a worksheet with a beginning and ending date range. I need the
number
of months for the range but also need them rounded up to the next month.

For example:

13-Jul-11 to 31-Jul-12 should calculate as 13 months and not the 12
provided
by the DATEDIF function.

In short, I need to round up to the next month for my calculations.

05-Mar-11 to 31-Dec-11 should return 10 full months and not 9.

Is there an easy way to do this?

Date Calculations
 

Since you are rounding up, the only time you would not add 1 is if the day of
the month is the same, so this formula should work for you:

=DATEDIF(startdate,enddate,"m")+IF(DAY(startdate)= DAY(enddate),0,1)

--
John C


"Tommy" wrote:

I have a worksheet with a beginning and ending date range. I need the number
of months for the range but also need them rounded up to the next month.

For example:

13-Jul-11 to 31-Jul-12 should calculate as 13 months and not the 12 provided
by the DATEDIF function.

In short, I need to round up to the next month for my calculations.

05-Mar-11 to 31-Dec-11 should return 10 full months and not 9.

Is there an easy way to do this?