Formula to extract date based on certain criteria in adjacent cell
Dear Experts
I need some help with an excel formula (example spread sheet attached which will hopefully make sense) - basically I am trying to produce 2 formulas to work out when someone joined their current company (Company 1) & also when they got their 1st job - listed down in column A is their employer and column B the dates they worked in that job & column C their job title (which is always formatted with the company name first then a hyphen & their job title. Their current company is always the 1st company listed, but they may have had multiple jobs in that company (i.e. they got promoted).
So in cell F1 needs to be the date they joined their current company (in the example that should be Apr 2015), in G1 needs to be the date they started their 1st job (in the example this should be Jun 1996)
Can anyone help?
Example.xlsx
I need some help with an excel formula (example spread sheet attached which will hopefully make sense) - basically I am trying to produce 2 formulas to work out when someone joined their current company (Company 1) & also when they got their 1st job - listed down in column A is their employer and column B the dates they worked in that job & column C their job title (which is always formatted with the company name first then a hyphen & their job title. Their current company is always the 1st company listed, but they may have had multiple jobs in that company (i.e. they got promoted).
So in cell F1 needs to be the date they joined their current company (in the example that should be Apr 2015), in G1 needs to be the date they started their 1st job (in the example this should be Jun 1996)
Can anyone help?
Example.xlsx
Roy Cox
Does the information refer to only one person?
ASKER
Yes its always only one person
For When They Joined Current Company in F1:
Enter below Array Formula confirmed with Ctrl+Shift+Enter:
Correlate_GetDates.xlsx
Enter below Array Formula confirmed with Ctrl+Shift+Enter:
=LEFT(INDEX($B$1:$B$200,SMALL(ROW($B$1:$B$200)+(100*($B$1:$B$200="")), 3))&"",9)=LEFT(LOOKUP(2,1/(B:B<>""),B:B),9)Correlate_GetDates.xlsx
The dates need to be proper dates, see attached
Example.xlsx
Example.xlsx
ASKER
Hi Shums, Thanks for that, the "For When They Started Their 1st Job in G1" is perfect, works a treat. The Array formula isn't working right - it looks like it just picks up the second date, which in this instance is all fine but there may be instances where people have been promoted many times, so the array really needs to look at the 1st change of company name in column A
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Roy, I can get those converted into proper dates
ASKER
Perfect, thank you for your help on that - works a treat