Display current monthly Data

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Ok, I want to have Sheet1 display Employee Name (A1), Date of Hire (B1) and
Years of Service (C1). then in each row show the info like this example:

Employee Name, Date of Hire, Years of service
Mike shmo, 4/10/2000, 8

I have this Sheet1 done and the code im using to get the "Years of service"
is
=DATEDIF(B2,TODAY(),"m")/12.

Now what i cant figure out is Sheet2 that will auto populate whos
aniverseries from Sheet1 that are in the current month and display them just
like they are in Sheet1.

Thanks,
Chad
 
Source table in Sheet1 as posted, data from row2 down
with hire dates in B2 down

In Sheet2,
In A2:
=IF(Sheet1!B2="","",IF(MONTH(Sheet1!B2)=MONTH(TODAY()),ROW(),""))
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, fill down to cover the max expected extent of
data in Sheet1, eg down to D200? Format col C as dates to taste. Hide
away/minimize col A. Cols B to D returns the required results, all neatly
bunched at the top.

As an aside (it doesn't affect the extracts above),
in Sheet1's col C, why not just use:
=DATEDIF(B2,TODAY(),"y")
 
Max, I just wanted to thank you for taking the time to figure this out! It
worked perfectly....
 
Back
Top