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

 

[hagia_sofia]

Hello, please test the following:

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

 

[Linh Le]

Hello, please test the following:

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

hi @hagia_sofia,

thank you. the formula resulted in a "0" solution. what am i doing wrong here? =LET(a,A1:z1,t,TOCOL(a,1),s,TEXTAFTER(TEXTBEFORE(t,"]"),"-- "),d,IFERROR(DATE(LEFT(s,4),MID(s,6,2),RIGHT(s,2)),0),MAX(d))

 

[hagia_sofia]

You are doing nothing wrong, it is definitely my bad - please test if the adjusted formula will work:

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

 

[Linh Le]

You are doing nothing wrong, it is definitely my bad - please test if the adjusted formula will work:

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

hmm, still no luck...=LET(a,A1:AA1,t,TOCOL(a,1),s,TEXTAFTER(TEXTBEFORE(t,"]"),CHAR(10),2),d,IFERROR(DATE(LEFT(s,4),MID(s,6,2),RIGHT(s,2)),0),MAX(d))

 

[hagia_sofia]

Ok, round 3:

=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))

 

[Rick Rothstein]

Does this formula do what you want...

=MAX(IFERROR(0+MID(A1:V1,2,10),))

 

[Linh Le]

Ok, round 3:

=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))

thank you. still no luck w/ this one. wonder if there's a better way than formula?

 

[Linh Le]

Does this formula do what you want...

=MAX(IFERROR(0+MID(A1:V1,2,10),))

no sir. not this one either...thank you.

 

[hagia_sofia]

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.