Date Calculations

[Tommy]

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?

 

[Pete_UK]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[John C]

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)