Need help with extracting info with multiple criteria

[Nathan Shiffman]

Hello,

I am currently very vexed and stumped by a problem which I believe isn’t hard to solve – for someone else unfortunately.


I have a table that looks something like this:

Status

Start Date

End Date

USER ID

First Name

Last Name

Current

Dec-14

Dec-17

ID00787Z

Gary

Seracuse

Leaver / Old Data

Oct-15

Dec-15

ID86927H

Sari

Loftus

Current

Jan-16

Dec-17

ID86927H

Sari

Loftus

Current

Jan-15

Dec-16

ID00440O

Loren

Lieberman

Leaver / Old Data

Dec-14

Jul-15

ID43683L

Dan

Boyer

Leaver / Old Data

Aug-15

Feb-16

ID43684L

Dan

Boyer

Leaver / Old Data

Feb-15

May-15

ID32370M

Jason

Hamilton

Current

Dec-14

Jul-16

ID94661M

Seth

Wheeler

Leaver / Old Data

Dec-14

Jun-15

ID13664Z

Greg

Montvale

Current

Oct-15

Dec-17

ID17299Z

Celine

Dione

Leaver / Old Data

Dec-14

Jun-15

ID19169P

Mark

Matchen

Leaver / Old Data

Jul-15

Dec-15

ID19169P

Mark

Matchen

Current

Dec-14

Dec-17

ID92260Q

Guy

Laval

Current

Jul-15

Apr-16

ID96137Q

Boris

Hutman

Current

Jan-16

Dec-17

ID19169P

Mark

Matchen


In the status column (where I note the status of the employee) I have this formula:

=IF(DATE(YEAR(TODAY()),MONTH(TODAY()),1)>D5,"Leaver / Old Data",IF(DATE(YEAR(TODAY()),MONTH(TODAY()),1)<C5,"Joiner / New Data","Current"))


Initially this worked for me, but I realize now that I have to separate ‘Leaver’ from ‘Old Data’


Current – means the employee is still employed. Duplicate records mean that the employee change projects.


Leaver = means an employee left the firm (e.g. Greg Montvale)

Old Data = means they it is an older record for either an existing employee (e.g. Sari Loftus and Mark Matchen) or the older record for an employee who has since left the firm (e.g. employee Dan Boyer)


Any insight you can provide would be greatly appreciated.


I know you are busy but if I don’t hear from you in a couple of days I will post the question on your forum.

Sincerely,

Nathan

 

[Luke M]

Hi Nathan, and welcome to the forum!

I'm afraid the formatting of your table became very hard to read. Can you post an example workbook, or at least paste a screenshot?

 

[Nathan Shiffman]

Ouch..Sorry about that.
I uploaded an image.
thanks!

 

[Luke M]

Wonderful, thanks!

Try this formula:
=IF(C2>TODAY(),"Joiner / New Data",
IF(MEDIAN(TODAY(),C2:D2)=TODAY(),"Current",
IF(COUNTIFS($E$2:$E$16,E2,$D$2:$D$16,">"&D2)=0,"Leaver","Old Data")))

 

[Nathan Shiffman]

that's amazing. Thank you very much!