# Excel / open/libre office formulae: number of full months left until date

I need a formula which equates to an integer value for the number months left until a set date (2011-10-16) based on the current date.

e.g.

``````on 2011-03-13 the answer would equate to 8
on 2011-03-17 the answer would equate to 7
``````

-
I'm a bit confused about the date format. Is it YYYY-MM-DD or YYYY-DD-MM (I mean: I don't know the 13th nor the 17th nor the 16th month). –  bmk Mar 13 '11 at 12:07
YYYY-DD-MM is a weird format. –  Dennis Williamson Mar 13 '11 at 13:37
you mention set date in YYYY-MM-DD manner, yet your example is in YYYY-DD-MM manner :-s –  Sathya Mar 14 '11 at 6:47
sorry. I meant to do it in international format so as not to confuse anyone YYYY-MM-DD. but messed up. - normally I do it the british way DD/MM/YY - but that will probably confuse many americans out there. I shall correct. –  Mark W Mar 14 '11 at 11:06
Could you please define "month"? Since you want it to work by days, a month 30 days, or something else? –  Lance Roberts Jun 22 '11 at 19:09

I think the use case is clear. It has to do with the number of genuine whole calendar months between two dates, where the end date sets where the month periods end. So there is no division by 30 or any other month-duration approximation.

You can use the MONTH( ), DAY( ), and YEAR( ) functions to take the two dates apart. You can then compute the number of months between the month of the first date and the month of the ending date, inclusive. If the day in the first month is later than the day of the ending month, decrease the result by 1.

(Probably make sure that the early date is actually not later than the ending date, too.)

-
There are edge cases when one of the months is February, but I assume that the day-of-month is always less than 29 for the ending date or else there is a fudge that has to be made for an ending in February. –  orcmid Aug 24 '11 at 21:18

Assuming `B50` contains the set date & `B51` contains the current date,

`=CEILING((B50-B51)/365*12,1)`

should give you what you want.

`(B50-B51)/365*12` returns the number of months till from set date to current date, `=CEILING()` rounds off the month.

-
That works nicely on month, but does not take into account days. In this example on 2011-03-13 the answer would equate to 8 on 2011-03-17 the answer would equate to 8 still, but should be 7 –  Mark W Mar 14 '11 at 11:11

I found a solution but don't now whether solves your problem or not. Because don't know what is your requirements clearly. Lets talk the solution:

Assuming P1 cell contains the set date; 2011-10-16 and P3 is 2011-03-17 and P4 is 2011-03-13.

I tried to consider the days.

=IF(DAY(P4)<15; ROUND(((\$P\$1-P4)/30);0)+1;ROUND(((\$P\$1-P4)/30);0))

The results:

2011-03-17 = 7

2011-03-13 = 8

-
Im after the number of full months left to the date +1 if there is any remainder? I guess an easier way of explaining this would be "how many paydays left until the date in P1? if payday is the 16th day of the month" –  Mark W Mar 14 '11 at 13:15
You should carefully examine the formula.+1 is not a day addition, it is a month addition. –  evowinds Mar 14 '11 at 13:26
your formula uses 3 inputs. I only have 2 (or 1 if you include a formulae to grab the current date). In my question I talked about the 13th and 17th because, if the input is one of those the answer should differ, just as you have done there, but with only a single constant date variable and a variable for todays date –  Mark W Mar 14 '11 at 13:38
i have also 2 inputs, one of them is to compare the another date. What if i change the formula like this? : =IF(DAY(P4)<15; ROUND(((TODAY()-P4)/30);0)+1;ROUND(((TODAY()-P4)/30);0)) there is only one input(P4) that is the date of to be set. –  evowinds Mar 14 '11 at 14:19
is this LibreOffice specific? MS-excel doesnt like it. I will try it in LibreOffice when I get home. –  Mark W Mar 14 '11 at 14:48