Hello,
My idea is as follows. (Someone may come with better ideas.)
Assuming that you have names in column-A and dates in column-B;
1. First of all create the function (after the instructions) in your VBA
module. Maybe copy-pasted.
2. In cell C2 you can write following formula against the dates
"=duration(B2)" and copy the formula in column-C(for all the data in
column-B).
3. To get the end result
a) below the Total write the formula [=countif("A:A","<>""")]
b) below the less than 6 months write this formula
[=countif("C:C","=0")]
c) below the less than 1 Year write this formula [=countif("C:C","=1")]
d) below the less than 2 Years write this formula [=countif("C:C","=2")]
e) below the less than 3 Years write this formula [=countif("C:C","=3")]
'-----------------------------------
Function duration(t As Date)
'0. Less than 6 months
'1. 6-12 Months
'2. 1-2 years
'3. 2-3 years
'4. More than 3 years
Dim t2 As Date
t2 = DateAdd("m", 6, t)
If t2 > Date Then
duration = 0
Else
t2 = DateAdd("m", 12, t)
If t2 > Date Then
duration = 1
Else
t2 = DateAdd("m", 24, t)
If t2 > Date Then
duration = 2
Else
t2 = DateAdd("m", 36, t)
If t2 > Date Then
duration = 3
Else
duration = 4
End If
End If
End If
End If
End Function
'------------------------------------------
On Thu, Oct 9, 2008 at 10:58 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>
>
>
> >
>
--
Akhilesh Kumar Karna
--~--~---------~--~----~------------~-------~--~----~
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>>
