Date Calculations

  • Thread starter Thread starter Tommy
  • Start date Start date
T

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?
 
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
 
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?
Click to expand...

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
 
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
 
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)
 
Back
Top