Help Please

[Linh Le]

Reaching out to the community for help. I really appreciate it. Thank you.

Is there a good formula to extract most recent departure date based on this data below? The list is non-continuous so each row is separate.

[1999-11-15 -- 2005-04-18] Patricia A. Bannan

[2005-04-18 -- 2007-05-31] Walter T. McCormick

[1998-01-22 -- 1998-06-19] Walter T. McCormick

[1998-06-19 -- 1999-01-01] Christopher P. Conkey

[1998-06-19 -- 2000-08-01] Judith A. Warners

[1999-01-01 -- 2001-05-31] Gary Pzegeo

[2001-04-01 -- 2006-06-30] Kevin D. Girts

[2001-04-01 -- 2007-05-31] Parham Behrooz

[2001-04-01 -- 2007-05-31] Robert A. Calhoun

[2001-12-31 -- 2007-05-31] Eric R. Harper

[2001-12-31 -- 2007-05-31] Mehmet Camurdan

[2001-12-31 -- 2007-05-31] Todd C. Kuimjian

[2006-07-01 -- 2007-05-31] Alex Perrin

[2006-07-01 -- 2007-05-31] Anthony Norris

[2006-07-01 -- 2007-05-31] Michael Lee

[2006-07-01 -- 2007-05-31] Peter Malcom Wilson

[2006-11-13 -- 2007-05-31] Bob Rowe

[2006-11-13 -- 2007-05-31] Christopher Y. Kauffman

[2006-11-13 -- 2007-05-31] Gary Pzegeo

[2006-11-13 -- 2007-05-31] Lisa Brown-Premo

[2007-06-01 -- ] Margaret D. Patel

[2019-04-01 -- ] Robert C. Junkin

[1996-02-01 -- 1997-09-01] Richard M. Cryan

[1997-02-01 -- 2004-03-01] Prescott B. Crocker

[2004-03-01 -- 2006-08-10] Dana E. Erikson

[2004-03-01 -- 2006-08-31] Edgardo Sternberg

[2004-05-03 -- 2007-05-31] Anthony Norris

[2004-05-03 -- 2007-05-31] Lisa Brown-Premo

[2004-05-03 -- 2007-05-31] Peter Malcom Wilson

[2006-08-11 -- 2007-05-31] Gary Pzegeo

[2006-09-01 -- 2007-05-31] Alex Perrin

[2006-09-01 -- 2007-05-31] Michael Lee

[2007-06-01 -- 2021-12-13] Margaret D. Patel

[2018-01-02 -- 2024-02-15] Kandarp Acharya

[2021-12-13 -- ] Petros N. Bocray

[2024-01-16 -- ] David Kowalske

[2008-04-07 -- 2009-11-29] Jeffrey Cucunato

[2008-04-07 -- 2009-11-29] Stuart Spodek

[2008-04-07 -- 2011-09-27] Robert M. Shearer

[2009-11-30 -- 2010-08-05] Curtis Y. Arledge

[2009-11-30 -- 2011-09-27] Kathleen M. Anderson

[2009-11-30 -- 2011-09-27] Matthew Marra

[2011-09-28 -- 2013-04-19] Michael C. Huebsch

[2011-11-28 -- ] Justin Christofel

[2011-11-28 -- 2013-11-27] Philip Brides

[2011-11-28 -- 2015-03-09] Lutz-Peter Wilke

[2011-11-28 -- 2023-04-05] Michael Fredericks

[2015-03-09 -- ] Alex Shingler

[2011-05-31 -- ] Jeffrey A. Geller

[2011-05-31 -- 2013-02-04] Neill Nuttall

[2011-05-31 -- 2013-05-30] Anne Lester

[2011-05-31 -- 2013-05-30] Michael Schoenhaut

[2011-05-31 -- 2013-05-30] Talib Sheikh

[2011-05-31 -- 2018-03-01] James Elliot

[2013-05-30 -- 2018-03-01] Jonathan M. Cummings

[2014-02-28 -- ] Grace Koo

[2014-12-09 -- ] Eric J. Bernbaum

[2020-11-02 -- ] Michael Feser

[2020-11-02 -- ] Philip Camporeale

[1996-05-01 -- 1999-07-16] Paul H. Drexler

[1996-05-01 -- 2000-09-01] John de Beer

[1996-05-01 -- 2002-09-30] Quentin Faulkner

[1996-05-01 -- 2021-03-01] Daniel J. Fuss

[1999-07-16 -- 2004-08-10] Alexander Lee Muromcew

[1999-07-16 -- 2004-10-01] Eswar C. Menon

[1999-07-16 -- 2004-10-01] John N. Tribolet

[2000-09-01 -- ] David W. Rolley

[2002-09-30 -- 2004-10-01] Robert Ix

[2004-10-01 -- 2011-05-01] Mark B. Baribeau

[2004-10-01 -- 2013-04-11] Warren N. Koontz

[2013-04-11 -- ] Eileen N. Riley

[2013-04-11 -- ] Lee M. Rosenbaum

[2021-03-01 -- ] Matthew J. Eagan

 

[Linh Le]

Hello again, if you copy the data from above #1 as you posted it do those formulas still not work? I did that and both @Rick Rothstein's and the one I posted return a date not 0.

hi. i retried it and it looks like @Rick Rothstein formula is resulting in "1/0/1900" date and not departing date? so, e.g., the second row's the latest dept date is 02/15/2024

 

[hagia_sofia]

And what does this return:

=LET(
a,A1:Z1,
t,TOCOL(a,1),
s,RIGHT(TEXTBEFORE(t,"]"),10),
d,IFERROR(DATE(LEFT(s,4),MID(s,6,2),RIGHT(s,2)),0),
MAX(d))

 

[Linh Le]

And what does this return:

=LET(
a,A1:Z1,
t,TOCOL(a,1),
s,RIGHT(TEXTBEFORE(t,"]"),10),
d,IFERROR(DATE(LEFT(s,4),MID(s,6,2),RIGHT(s,2)),0),
MAX(d))

still getting "1/0/1900"...i retried Rick's formula and it looks like it's pulling start dates from all of the last entries and not departure date. is there a way i can upload my sheet in case it's helpful? thank you so much for all of your help

 

[hagia_sofia]

Based on the screenshot, I still do not know what could be the problem. As far as your question is concerned you could use XL2BB tool or just something like dropbox.

 

[Fluff]

You have the formula in C2 which is causing a circular reference & hence you get 0 as a result. You need to change the range to match your data.

 

[Asbestos_Jen]

I modified Rick's formula to start at the end date (assuming it's always in the same place). I had to commit it as an array in Excel 2007, but it works for me.

2204-10-21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V					AA
1	[1999-11-15 -- 2005-04-18] Patricia A. Bannan	[2005-04-18 -- 2007-05-31] Walter T. McCormick	[1998-01-22 -- 1998-06-19] Walter T. McCormick	[1998-06-19 -- 1999-01-01] Christopher P. Conkey	[1998-06-19 -- 2000-08-01] Judith A. Warners	[1999-01-01 -- 2001-05-31] Gary Pzegeo	[2001-04-01 -- 2006-06-30] Kevin D. Girts	[2001-04-01 -- 2007-05-31] Parham Behrooz	[2001-04-01 -- 2007-05-31] Robert A. Calhoun	[2001-12-31 -- 2007-05-31] Eric R. Harper	[2001-12-31 -- 2007-05-31] Mehmet Camurdan	[2001-12-31 -- 2007-05-31] Todd C. Kuimjian	[2006-07-01 -- 2007-05-31] Alex Perrin	[2006-07-01 -- 2007-05-31] Anthony Norris	[2006-07-01 -- 2007-05-31] Michael Lee	[2006-07-01 -- 2007-05-31] Peter Malcom Wilson	[2006-11-13 -- 2007-05-31] Bob Rowe	[2006-11-13 -- 2007-05-31] Christopher Y. Kauffman	[2006-11-13 -- 2007-05-31] Gary Pzegeo	[2006-11-13 -- 2007-05-31] Lisa Brown-Premo	[2007-06-01 -- ] Margaret D. Patel	[2019-04-01 -- ] Robert C. Junkin					31-May-07
2	[1996-02-01 -- 1997-09-01] Richard M. Cryan	[1997-02-01 -- 2004-03-01] Prescott B. Crocker	[2004-03-01 -- 2006-08-10] Dana E. Erikson	[2004-03-01 -- 2006-08-31] Edgardo Sternberg	[2004-05-03 -- 2007-05-31] Anthony Norris	[2004-05-03 -- 2007-05-31] Lisa Brown-Premo	[2004-05-03 -- 2007-05-31] Peter Malcom Wilson	[2006-08-11 -- 2007-05-31] Gary Pzegeo	[2006-09-01 -- 2007-05-31] Alex Perrin	[2006-09-01 -- 2007-05-31] Michael Lee	[2007-06-01 -- 2021-12-13] Margaret D. Patel	[2018-01-02 -- 2024-02-15] Kandarp Acharya	[2021-12-13 -- ] Petros N. Bocray	[2024-01-16 -- ] David Kowalske													15-Feb-24
3	[2008-04-07 -- 2009-11-29] Jeffrey Cucunato	[2008-04-07 -- 2009-11-29] Stuart Spodek	[2008-04-07 -- 2011-09-27] Robert M. Shearer	[2009-11-30 -- 2010-08-05] Curtis Y. Arledge	[2009-11-30 -- 2011-09-27] Kathleen M. Anderson	[2009-11-30 -- 2011-09-27] Matthew Marra	[2011-09-28 -- 2013-04-19] Michael C. Huebsch	[2011-11-28 -- ] Justin Christofel	[2011-11-28 -- 2013-11-27] Philip Brides	[2011-11-28 -- 2015-03-09] Lutz-Peter Wilke	[2011-11-28 -- 2023-04-05] Michael Fredericks	[2015-03-09 -- ] Alex Shingler															05-Apr-23
4	[2011-05-31 -- ] Jeffrey A. Geller	[2011-05-31 -- 2013-02-04] Neill Nuttall	[2011-05-31 -- 2013-05-30] Anne Lester	[2011-05-31 -- 2013-05-30] Michael Schoenhaut	[2011-05-31 -- 2013-05-30] Talib Sheikh	[2011-05-31 -- 2018-03-01] James Elliot	[2013-05-30 -- 2018-03-01] Jonathan M. Cummings	[2014-02-28 -- ] Grace Koo	[2014-12-09 -- ] Eric J. Bernbaum	[2020-11-02 -- ] Michael Feser	[2020-11-02 -- ] Philip Camporeale																01-Mar-18
5	[1996-05-01 -- 1999-07-16] Paul H. Drexler	[1996-05-01 -- 2000-09-01] John de Beer	[1996-05-01 -- 2002-09-30] Quentin Faulkner	[1996-05-01 -- 2021-03-01] Daniel J. Fuss	[1999-07-16 -- 2004-08-10] Alexander Lee Muromcew	[1999-07-16 -- 2004-10-01] Eswar C. Menon	[1999-07-16 -- 2004-10-01] John N. Tribolet	[2000-09-01 -- ] David W. Rolley	[2002-09-30 -- 2004-10-01] Robert Ix	[2004-10-01 -- 2011-05-01] Mark B. Baribeau	[2004-10-01 -- 2013-04-11] Warren N. Koontz	[2013-04-11 -- ] Eileen N. Riley	[2013-04-11 -- ] Lee M. Rosenbaum	[2021-03-01 -- ] Matthew J. Eagan													01-Mar-21
Sheet2

Cell Formulas
Range	Formula
AA1:AA5	AA1	=MAX(IFERROR(0+MID(A1:V1,16,10),))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Linh Le]

You have the formula in C2 which is causing a circular reference & hence you get 0 as a result. You need to change the range to match your data.

hi, yes. i caught that rookie mistake and made the adjustments but still no luck.

 

[Linh Le]

I modified Rick's formula to start at the end date (assuming it's always in the same place. I had to commit it as an array in Excel 2007, but it works for me.

2204-10-21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V					AA
1	[1999-11-15 -- 2005-04-18] Patricia A. Bannan	[2005-04-18 -- 2007-05-31] Walter T. McCormick	[1998-01-22 -- 1998-06-19] Walter T. McCormick	[1998-06-19 -- 1999-01-01] Christopher P. Conkey	[1998-06-19 -- 2000-08-01] Judith A. Warners	[1999-01-01 -- 2001-05-31] Gary Pzegeo	[2001-04-01 -- 2006-06-30] Kevin D. Girts	[2001-04-01 -- 2007-05-31] Parham Behrooz	[2001-04-01 -- 2007-05-31] Robert A. Calhoun	[2001-12-31 -- 2007-05-31] Eric R. Harper	[2001-12-31 -- 2007-05-31] Mehmet Camurdan	[2001-12-31 -- 2007-05-31] Todd C. Kuimjian	[2006-07-01 -- 2007-05-31] Alex Perrin	[2006-07-01 -- 2007-05-31] Anthony Norris	[2006-07-01 -- 2007-05-31] Michael Lee	[2006-07-01 -- 2007-05-31] Peter Malcom Wilson	[2006-11-13 -- 2007-05-31] Bob Rowe	[2006-11-13 -- 2007-05-31] Christopher Y. Kauffman	[2006-11-13 -- 2007-05-31] Gary Pzegeo	[2006-11-13 -- 2007-05-31] Lisa Brown-Premo	[2007-06-01 -- ] Margaret D. Patel	[2019-04-01 -- ] Robert C. Junkin					31-May-07
2	[1996-02-01 -- 1997-09-01] Richard M. Cryan	[1997-02-01 -- 2004-03-01] Prescott B. Crocker	[2004-03-01 -- 2006-08-10] Dana E. Erikson	[2004-03-01 -- 2006-08-31] Edgardo Sternberg	[2004-05-03 -- 2007-05-31] Anthony Norris	[2004-05-03 -- 2007-05-31] Lisa Brown-Premo	[2004-05-03 -- 2007-05-31] Peter Malcom Wilson	[2006-08-11 -- 2007-05-31] Gary Pzegeo	[2006-09-01 -- 2007-05-31] Alex Perrin	[2006-09-01 -- 2007-05-31] Michael Lee	[2007-06-01 -- 2021-12-13] Margaret D. Patel	[2018-01-02 -- 2024-02-15] Kandarp Acharya	[2021-12-13 -- ] Petros N. Bocray	[2024-01-16 -- ] David Kowalske													15-Feb-24
3	[2008-04-07 -- 2009-11-29] Jeffrey Cucunato	[2008-04-07 -- 2009-11-29] Stuart Spodek	[2008-04-07 -- 2011-09-27] Robert M. Shearer	[2009-11-30 -- 2010-08-05] Curtis Y. Arledge	[2009-11-30 -- 2011-09-27] Kathleen M. Anderson	[2009-11-30 -- 2011-09-27] Matthew Marra	[2011-09-28 -- 2013-04-19] Michael C. Huebsch	[2011-11-28 -- ] Justin Christofel	[2011-11-28 -- 2013-11-27] Philip Brides	[2011-11-28 -- 2015-03-09] Lutz-Peter Wilke	[2011-11-28 -- 2023-04-05] Michael Fredericks	[2015-03-09 -- ] Alex Shingler															05-Apr-23
4	[2011-05-31 -- ] Jeffrey A. Geller	[2011-05-31 -- 2013-02-04] Neill Nuttall	[2011-05-31 -- 2013-05-30] Anne Lester	[2011-05-31 -- 2013-05-30] Michael Schoenhaut	[2011-05-31 -- 2013-05-30] Talib Sheikh	[2011-05-31 -- 2018-03-01] James Elliot	[2013-05-30 -- 2018-03-01] Jonathan M. Cummings	[2014-02-28 -- ] Grace Koo	[2014-12-09 -- ] Eric J. Bernbaum	[2020-11-02 -- ] Michael Feser	[2020-11-02 -- ] Philip Camporeale																01-Mar-18
5	[1996-05-01 -- 1999-07-16] Paul H. Drexler	[1996-05-01 -- 2000-09-01] John de Beer	[1996-05-01 -- 2002-09-30] Quentin Faulkner	[1996-05-01 -- 2021-03-01] Daniel J. Fuss	[1999-07-16 -- 2004-08-10] Alexander Lee Muromcew	[1999-07-16 -- 2004-10-01] Eswar C. Menon	[1999-07-16 -- 2004-10-01] John N. Tribolet	[2000-09-01 -- ] David W. Rolley	[2002-09-30 -- 2004-10-01] Robert Ix	[2004-10-01 -- 2011-05-01] Mark B. Baribeau	[2004-10-01 -- 2013-04-11] Warren N. Koontz	[2013-04-11 -- ] Eileen N. Riley	[2013-04-11 -- ] Lee M. Rosenbaum	[2021-03-01 -- ] Matthew J. Eagan													01-Mar-21
Sheet2

Cell Formulas
Range	Formula
AA1:AA5	AA1	=MAX(IFERROR(0+MID(A1:V1,16,10),))
Press CTRL+SHIFT+ENTER to enter array formulas.

this one has some promise, but unfortunately the departure date is random in all of the rows...

 

[Fluff]

Both formulae work for me

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2		31/05/2007	31/05/2007	[1999-11-15 -- 2005-04-18] Patricia A. Bannan	[2005-04-18 -- 2007-05-31] Walter T. McCormick	[1998-01-22 -- 1998-06-19] Walter T. McCormick	[1998-06-19 -- 1999-01-01] Christopher P. Conkey	[1998-06-19 -- 2000-08-01] Judith A. Warners	[1999-01-01 -- 2001-05-31] Gary Pzegeo	[2001-04-01 -- 2006-06-30] Kevin D. Girts	[2001-04-01 -- 2007-05-31] Parham Behrooz	[2001-04-01 -- 2007-05-31] Robert A. Calhoun	[2001-12-31 -- 2007-05-31] Eric R. Harper	[2001-12-31 -- 2007-05-31] Mehmet Camurdan	[2001-12-31 -- 2007-05-31] Todd C. Kuimjian	[2006-07-01 -- 2007-05-31] Alex Perrin	[2006-07-01 -- 2007-05-31] Anthony Norris	[2006-07-01 -- 2007-05-31] Michael Lee	[2006-07-01 -- 2007-05-31] Peter Malcom Wilson	[2006-11-13 -- 2007-05-31] Bob Rowe	[2006-11-13 -- 2007-05-31] Christopher Y. Kauffman	[2006-11-13 -- 2007-05-31] Gary Pzegeo	[2006-11-13 -- 2007-05-31] Lisa Brown-Premo	[2007-06-01 -- ] Margaret D. Patel	[2019-04-01 -- ] Robert C. Junkin
3		15/02/2024	15/02/2024	[1996-02-01 -- 1997-09-01] Richard M. Cryan	[1997-02-01 -- 2004-03-01] Prescott B. Crocker	[2004-03-01 -- 2006-08-10] Dana E. Erikson	[2004-03-01 -- 2006-08-31] Edgardo Sternberg	[2004-05-03 -- 2007-05-31] Anthony Norris	[2004-05-03 -- 2007-05-31] Lisa Brown-Premo	[2004-05-03 -- 2007-05-31] Peter Malcom Wilson	[2006-08-11 -- 2007-05-31] Gary Pzegeo	[2006-09-01 -- 2007-05-31] Alex Perrin	[2006-09-01 -- 2007-05-31] Michael Lee	[2007-06-01 -- 2021-12-13] Margaret D. Patel	[2018-01-02 -- 2024-02-15] Kandarp Acharya	[2021-12-13 -- ] Petros N. Bocray	[2024-01-16 -- ] David Kowalske
4		05/04/2023	05/04/2023	[2008-04-07 -- 2009-11-29] Jeffrey Cucunato	[2008-04-07 -- 2009-11-29] Stuart Spodek	[2008-04-07 -- 2011-09-27] Robert M. Shearer	[2009-11-30 -- 2010-08-05] Curtis Y. Arledge	[2009-11-30 -- 2011-09-27] Kathleen M. Anderson	[2009-11-30 -- 2011-09-27] Matthew Marra	[2011-09-28 -- 2013-04-19] Michael C. Huebsch	[2011-11-28 -- ] Justin Christofel	[2011-11-28 -- 2013-11-27] Philip Brides	[2011-11-28 -- 2015-03-09] Lutz-Peter Wilke	[2011-11-28 -- 2023-04-05] Michael Fredericks	[2015-03-09 -- ] Alex Shingler
5		01/03/2018	01/03/2018	[2011-05-31 -- ] Jeffrey A. Geller	[2011-05-31 -- 2013-02-04] Neill Nuttall	[2011-05-31 -- 2013-05-30] Anne Lester	[2011-05-31 -- 2013-05-30] Michael Schoenhaut	[2011-05-31 -- 2013-05-30] Talib Sheikh	[2011-05-31 -- 2018-03-01] James Elliot	[2013-05-30 -- 2018-03-01] Jonathan M. Cummings	[2014-02-28 -- ] Grace Koo	[2014-12-09 -- ] Eric J. Bernbaum	[2020-11-02 -- ] Michael Feser	[2020-11-02 -- ] Philip Camporeale
6		01/03/2021	01/03/2021	[1996-05-01 -- 1999-07-16] Paul H. Drexler	[1996-05-01 -- 2000-09-01] John de Beer	[1996-05-01 -- 2002-09-30] Quentin Faulkner	[1996-05-01 -- 2021-03-01] Daniel J. Fuss	[1999-07-16 -- 2004-08-10] Alexander Lee Muromcew	[1999-07-16 -- 2004-10-01] Eswar C. Menon	[1999-07-16 -- 2004-10-01] John N. Tribolet	[2000-09-01 -- ] David W. Rolley	[2002-09-30 -- 2004-10-01] Robert Ix	[2004-10-01 -- 2011-05-01] Mark B. Baribeau	[2004-10-01 -- 2013-04-11] Warren N. Koontz	[2013-04-11 -- ] Eileen N. Riley	[2013-04-11 -- ] Lee M. Rosenbaum	[2021-03-01 -- ] Matthew J. Eagan
Sheet5

Cell Formulas
Range	Formula
B2:B6	B2	=MAX(IFERROR(0+MID(D2:Y2,16,10),))
C2:C6	C2	=LET( a,D2:Z2, t,TOCOL(a,1), s,RIGHT(TEXTBEFORE(t,"]"),10), d,IFERROR(DATE(LEFT(s,4),MID(s,6,2),RIGHT(s,2)),0), MAX(d))

 

[Linh Le]

Both formulae work for me

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2		31/05/2007	31/05/2007	[1999-11-15 -- 2005-04-18] Patricia A. Bannan	[2005-04-18 -- 2007-05-31] Walter T. McCormick	[1998-01-22 -- 1998-06-19] Walter T. McCormick	[1998-06-19 -- 1999-01-01] Christopher P. Conkey	[1998-06-19 -- 2000-08-01] Judith A. Warners	[1999-01-01 -- 2001-05-31] Gary Pzegeo	[2001-04-01 -- 2006-06-30] Kevin D. Girts	[2001-04-01 -- 2007-05-31] Parham Behrooz	[2001-04-01 -- 2007-05-31] Robert A. Calhoun	[2001-12-31 -- 2007-05-31] Eric R. Harper	[2001-12-31 -- 2007-05-31] Mehmet Camurdan	[2001-12-31 -- 2007-05-31] Todd C. Kuimjian	[2006-07-01 -- 2007-05-31] Alex Perrin	[2006-07-01 -- 2007-05-31] Anthony Norris	[2006-07-01 -- 2007-05-31] Michael Lee	[2006-07-01 -- 2007-05-31] Peter Malcom Wilson	[2006-11-13 -- 2007-05-31] Bob Rowe	[2006-11-13 -- 2007-05-31] Christopher Y. Kauffman	[2006-11-13 -- 2007-05-31] Gary Pzegeo	[2006-11-13 -- 2007-05-31] Lisa Brown-Premo	[2007-06-01 -- ] Margaret D. Patel	[2019-04-01 -- ] Robert C. Junkin
3		15/02/2024	15/02/2024	[1996-02-01 -- 1997-09-01] Richard M. Cryan	[1997-02-01 -- 2004-03-01] Prescott B. Crocker	[2004-03-01 -- 2006-08-10] Dana E. Erikson	[2004-03-01 -- 2006-08-31] Edgardo Sternberg	[2004-05-03 -- 2007-05-31] Anthony Norris	[2004-05-03 -- 2007-05-31] Lisa Brown-Premo	[2004-05-03 -- 2007-05-31] Peter Malcom Wilson	[2006-08-11 -- 2007-05-31] Gary Pzegeo	[2006-09-01 -- 2007-05-31] Alex Perrin	[2006-09-01 -- 2007-05-31] Michael Lee	[2007-06-01 -- 2021-12-13] Margaret D. Patel	[2018-01-02 -- 2024-02-15] Kandarp Acharya	[2021-12-13 -- ] Petros N. Bocray	[2024-01-16 -- ] David Kowalske
4		05/04/2023	05/04/2023	[2008-04-07 -- 2009-11-29] Jeffrey Cucunato	[2008-04-07 -- 2009-11-29] Stuart Spodek	[2008-04-07 -- 2011-09-27] Robert M. Shearer	[2009-11-30 -- 2010-08-05] Curtis Y. Arledge	[2009-11-30 -- 2011-09-27] Kathleen M. Anderson	[2009-11-30 -- 2011-09-27] Matthew Marra	[2011-09-28 -- 2013-04-19] Michael C. Huebsch	[2011-11-28 -- ] Justin Christofel	[2011-11-28 -- 2013-11-27] Philip Brides	[2011-11-28 -- 2015-03-09] Lutz-Peter Wilke	[2011-11-28 -- 2023-04-05] Michael Fredericks	[2015-03-09 -- ] Alex Shingler
5		01/03/2018	01/03/2018	[2011-05-31 -- ] Jeffrey A. Geller	[2011-05-31 -- 2013-02-04] Neill Nuttall	[2011-05-31 -- 2013-05-30] Anne Lester	[2011-05-31 -- 2013-05-30] Michael Schoenhaut	[2011-05-31 -- 2013-05-30] Talib Sheikh	[2011-05-31 -- 2018-03-01] James Elliot	[2013-05-30 -- 2018-03-01] Jonathan M. Cummings	[2014-02-28 -- ] Grace Koo	[2014-12-09 -- ] Eric J. Bernbaum	[2020-11-02 -- ] Michael Feser	[2020-11-02 -- ] Philip Camporeale
6		01/03/2021	01/03/2021	[1996-05-01 -- 1999-07-16] Paul H. Drexler	[1996-05-01 -- 2000-09-01] John de Beer	[1996-05-01 -- 2002-09-30] Quentin Faulkner	[1996-05-01 -- 2021-03-01] Daniel J. Fuss	[1999-07-16 -- 2004-08-10] Alexander Lee Muromcew	[1999-07-16 -- 2004-10-01] Eswar C. Menon	[1999-07-16 -- 2004-10-01] John N. Tribolet	[2000-09-01 -- ] David W. Rolley	[2002-09-30 -- 2004-10-01] Robert Ix	[2004-10-01 -- 2011-05-01] Mark B. Baribeau	[2004-10-01 -- 2013-04-11] Warren N. Koontz	[2013-04-11 -- ] Eileen N. Riley	[2013-04-11 -- ] Lee M. Rosenbaum	[2021-03-01 -- ] Matthew J. Eagan
Sheet5

Cell Formulas
Range	Formula
B2:B6	B2	=MAX(IFERROR(0+MID(D2:Y2,16,10),))
C2:C6	C2	=LET( a,D2:Z2, t,TOCOL(a,1), s,RIGHT(TEXTBEFORE(t,"]"),10), d,IFERROR(DATE(LEFT(s,4),MID(s,6,2),RIGHT(s,2)),0), MAX(d))

yes, it does. #usererror =). thank you @hagia_sofia @Rick Rothstein @Fluff @Asbestos_Jen. one other question - if i want to show "--" for entries that doesn't have a departure date how would i go about it? thanks again. i really appreciate it.