Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Can anyone help/advise?

share|improve this question
2  
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
add comment

3 Answers

up vote 0 down vote accepted

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.)

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.