Hello Ajit, I was just going through this formula, I have a couple of questions on the solution that you have given. Hope you can help me on this.
1) Why is there a difference if we just press ENTER in the below formula. Why CTRL + SHIFT + ENTER? 2) Why doesn't it give the answer if we give the range as B:B in the formula? Thanks, Arjun On Oct 10, 7:17 pm, "AJIT NAVRE" <[EMAIL PROTECTED]> wrote: > Set up the table (2nd table) with headers (required Period Slabs) and use > the following formulas, > Less Than 6 months Formula > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") < 6,1)) > > 6 Months To 1 Year > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") >= 6,IF(DATEDIF(B2:B7,TODAY(),"m") <= > 12,1))) > > 1 year To 2 Years > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") >= 13,IF(DATEDIF(B2:B7,TODAY(),"m") <= > 24,1))) > > 2 Years To 3 Years > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") >= 25,IF(DATEDIF(B2:B7,TODAY(),"m") <= > 35,1))) > > Greater Than 3 Years > > =COUNT(IF(DATEDIF(B2:B7,TODAY(),"m") >= 37,1)) > > Remember: > On Each formula, after entering the last closing paranthesis ")", end the > formula by pressing CTRL+SHIFT+ENTER, and not only ENTER. > > The Range B2:B7 should be replaced with the appropriate Joining Date Range > Address In Your Data. Make it a point not to enter the entire column as in > B:B. > > This solution assumes that, you want this tenure break-up only for a limited > slab (6m, 1Y, 2Y, > 3Y). If you want to calculate tenure for an infinite > slab, then you probably need a macro. > > Let me know if this was what you were looking for. > > Regards, > > Ajit > > On Thu, Oct 9, 2008 at 7:28 PM, Krishna Kishore <[EMAIL PROTECTED]>wrote: > > > > > > > Hello All > > > I am looking for a help to create a file in excel which can capture the > > exact tenure of people in an organization. Your inputs can help me in > > achieving this. > > > I am looking for a report in which excel can segregate the staff in the > > following categories. > > > Name > > > Date of Joining > > > Rahul > > > 19-12-2004 > > > Lakshman > > > 22-11-2005 > > > Azhar > > > 01-01-2008 > > > Vinod > > > 01-08-2008 > > > Through this the end result should be out in this format > > > Total > > > Less than 6 months > > > 6 months to 1 year > > > 1 year to 2 years > > > 2 years to 3 years > > > 4 > > > 1 > > > 1 > > > 1 > > > 1 > > > I would be thankful to all in advance. > > > <http://smsxite.com/picdetail.php?catId=43&tid=39> > > > Krishna Kishore > > [image: Friendship SMS] <http://smsxite.com/picdetail.php?catId=43&tid=39> > > -- > Thank You, > > Ajit Navre > > image001.jpg > 23KViewDownload- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~----------~----~----~----~------~----~------~--~---
