• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate No. of months

S

ssraghunathan

New Member
I have the data (dd-mmm-yyyy) wherein i need to calculate no. of months served as upto 31st March 2012 from their joining date.

a) If anybody serve less than a year then calculate actual months

b) If any employee who is more than 18 months, the value restricted to 18 months.

c) In all other cases, it should be 12 months.


Ex. 1


Dt.of joining : 1-apr-2007

No. of Months served : 5 Years as on 31st March 2012

But the calculation is for 12 Months.


Ex. 2


Dt.of joining : 1-oct-2010

No. of Months served : 1 Year 6 months as on 31st March 2012

But the calculation is for 18 Months.


Ex. 3


Dt.of joining : 1-oct-2011

No. of Months served : 6 months as on 31st March 2012

But the calculation is for 6 Months.


Anybody help me.
 
Assuming you'r date of March 31, 2012 is in A1.


=IF(DATEDIF(A2,$A$1+1,"M")>18,12,DATEDIF(A2,$A$1+1,"M"))


I put the +1 in the formula because it looks like you were counting the full month as being completed on the end of the month, whereas the function would normally only count it being completed when you go from the 1st of a month to the 1st of the month.
 
Luke, Thanks for your support.


But I have an error in few cases especially for whose joining dates in September 2010. It should be 12 months only. but the result was showing 18 months.


One more issue which I forget to mention that the months should be rounded to nearest value 15 days either side. (eg. joining dates befor 15th it should be whole month for the current month and other cases it should from next month).


Thanks


Raghunathan S
 
Hi, ssraghunathan!


Please give a look at this link: http://chandoo.org/forums/topic/excel-2007


It started as a date & months adding problem as wrong values appeared for certain dates with function DATEDIF and parameter "m", it ended as a bug of an undocumented (last version was in Excel 2000) function, which Microsoft apparently tried to maintain it unused, unreachable, unknown or those who didn't use it before, as they couldn't eliminate it because of compatibility issues.


In that link, in the sixth post backwards from last one, you'll find an easy and safe simple workaround for avoid using it, in the 12th. post from the beginning.


Just advise if anything from the link isn't fully clear.


Regards!
 
Hi Raghunathan ,


The three examples you have given are not really indicative of the rules you have given.


Taking you own specific case of someone joining in September 2010 , the difference in months ( obviously this has to be calculated first , before the given rules can be applied ) is March 2012 - September 2010 , which is 19 months.


Now , if we apply your rules :


(a) does not apply ; (b) applies , and since 19 is more than 18 , the number of months is restricted to 18. (c) will not apply , because you have mentioned "in all other cases" , which means those cases to which rules (a) and (b) do not apply.


Please clarify your rule-logic.


Narayan
 
@SirJB7


Just a forum tip, you can link directly to a post by right clicking on # symbol underneath a post. Exact post you were referencing is:

http://chandoo.org/forums/topic/excel-2007#post-16879
 
@Luke M

Hi!

Thank you for the tip, it was about time I learn it... :)

Regards!

PS: yes, I now feel a little stupid as I remember how I counted one by one so as to lead the user to the right comment... :)))
 
You must log in or register to reply here.
Back
Top