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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
<<inline: image001.jpg>>
