Find Most recent date in a row and return that date along with Column Heading

[jski21]

Good day Mr. Excel Team,

Is there a known way to find the most recent date in a row and return that date along with the Column Heading it is in? Thanks!

 

[etaf]

=INDEX(B1:L1,MATCH(MAX(B2:L2),B2:L2,0))

Index( B1:L1 = return a value from this range
Find the Highest Value in ,MATCH(MAX(B2:L2) - so as dates are just numbers incremented by 1 each day from 1900 - then the latest date , will be the highest number
Look that MAX up in this ROW B2:L2
Now 0 - means an EXACT match ONLY

Then a Simple MAX(), will return the actual date

Note XL2BB returns US date format - MM/DD/YY
even though i'm in UK

It would be helpful to have your Excel version in the profile, as later versions have more functions and some things are easier to do with those functions

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11
2		3/1/21	4/1/21	5/1/21	6/1/22	7/1/21	8/1/21	9/1/21	10/1/21	11/1/21	12/1/21	1/1/22
3
4	A4
5	6/1/22
6
Sheet1

Cell Formulas
Range	Formula
A4	A4	=INDEX(B1:L1,MATCH(MAX(B2:L2),B2:L2,0))
A5	A5	=MAX(B2:L2)

 

[Oddball2020]

Good day Mr. Excel Team,

Is there a known way to find the most recent date in a row and return that date along with the Column Heading it is in? Thanks!

Also, try this,

=OFFSET($A$2, -1, MATCH(MIN(2:2), 2:2, 0)-1)

MIN can be used across a range of dates and return the most recent, or MAX for oldest.
OFFSET is just being used to return the header above the MIN match.

 

[jski21]

=INDEX(B1:L1,MATCH(MAX(B2:L2),B2:L2,0))

Index( B1:L1 = return a value from this range
Find the Highest Value in ,MATCH(MAX(B2:L2) - so as dates are just numbers incremented by 1 each day from 1900 - then the latest date , will be the highest number
Look that MAX up in this ROW B2:L2
Now 0 - means an EXACT match ONLY

Then a Simple MAX(), will return the actual date

Note XL2BB returns US date format - MM/DD/YY
even though i'm in UK

It would be helpful to have your Excel version in the profile, as later versions have more functions and some things are easier to do with those functions

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11
2		3/1/21	4/1/21	5/1/21	6/1/22	7/1/21	8/1/21	9/1/21	10/1/21	11/1/21	12/1/21	1/1/22
3
4	A4
5	6/1/22
6
Sheet1

Cell Formulas
Range	Formula
A4	A4	=INDEX(B1:L1,MATCH(MAX(B2:L2),B2:L2,0))
A5	A5	=MAX(B2:L2)

etaf,

Very instructive and thank you. I receive an #N/A for the column output and 1/0/1900 for the date output when I use it in my sheet:

CARES Act Grant Balances by Major Program.xlsx
	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
1	AllPIFsInDT	DraftApprovedDT	ScopeOutforSigDT	ScopeSignedDT	ScopeSignedDirDT	ToAcctRound1DT	ToOBMDT	FromLawDT	ToAcctRound2DT	FinishDT	Duration	DaysInCert
2							6/8/2016	6/24/2016	8/4/2016	3 /24/2017	256	#Error	#N/A	1/0/1900
All

Cell Formulas
Range	Formula
BN2	BN2	=INDEX(BB1:BK1,MATCH(MAX(BB2:BK2),BB2:BK2,0))
BO2	BO2	=MAX(BB2:BK2)

 

[jski21]

etaf,

Very instructive and thank you. I receive an #N/A for the column output and 1/0/1900 for the date output when I use it in my sheet:

CARES Act Grant Balances by Major Program.xlsx
	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
1	AllPIFsInDT	DraftApprovedDT	ScopeOutforSigDT	ScopeSignedDT	ScopeSignedDirDT	ToAcctRound1DT	ToOBMDT	FromLawDT	ToAcctRound2DT	FinishDT	Duration	DaysInCert
2							6/8/2016	6/24/2016	8/4/2016	3 /24/2017	256	#Error	#N/A	1/0/1900
All

Cell Formulas
Range	Formula
BN2	BN2	=INDEX(BB1:BK1,MATCH(MAX(BB2:BK2),BB2:BK2,0))
BO2	BO2	=MAX(BB2:BK2)

Actually found a bit of the solution...seems the date fields have some spaces in them (not my spreadsheet mind you. Trying to take over/improve from previous owner). But how could it best handle the blank items?

 

[etaf]

i copied into a sheet and used the formula , only changed the range

I'm on version 365 - try using an array entry , as you are on 2016 version, which does not deal with arrays automatically
Press Ctrl+Shift+Enter

BUT =MAX(BB2:BK2) should work, blanks dont matter, as its a MAX , MIN would

you could put the spreadsheet on a share like onedrive , dropbox i tend to use
Make sure NO private data is available as it is a public forum and will be seen by anyone on the internet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	AllPIFsInDT	DraftApprovedDT	ScopeOutforSigDT	ScopeSignedDT	ScopeSignedDirDT	ToAcctRound1DT	ToOBMDT	FromLawDT	ToAcctRound2DT	FinishDT	Duration	DaysInCert
2							8/6/16	6/24/16	4/8/16	3/24/17	256	#Error	FinishDT	3/24/17
Sheet3

Cell Formulas
Range	Formula
M2	M2	=INDEX(A1:J1,MATCH(MAX(A2:J2),A2:J2,0))
N2	N2	=MAX(A2:J2)

 

[jski21]

i copied into a sheet and used the formula , only changed the range

I'm on version 365 - try using an array entry , as you are on 2016 version, which does not deal with arrays automatically
Press Ctrl+Shift+Enter

BUT =MAX(BB2:BK2) should work, blanks dont matter, as its a MAX , MIN would

you could put the spreadsheet on a share like onedrive , dropbox i tend to use
Make sure NO private data is available as it is a public forum and will be seen by anyone on the internet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	AllPIFsInDT	DraftApprovedDT	ScopeOutforSigDT	ScopeSignedDT	ScopeSignedDirDT	ToAcctRound1DT	ToOBMDT	FromLawDT	ToAcctRound2DT	FinishDT	Duration	DaysInCert
2							8/6/16	6/24/16	4/8/16	3/24/17	256	#Error	FinishDT	3/24/17
Sheet3

Cell Formulas
Range	Formula
M2	M2	=INDEX(A1:J1,MATCH(MAX(A2:J2),A2:J2,0))
N2	N2	=MAX(A2:J2)

 

[jski21]

Thanks etaf. Yeah, 1/0/1900 only occurs if there is no data in any of the columns. I can deal with this for now as it identifies the outliers and need for investigation.

Thanks again for the instruction!

jski

 

[etaf]

you could use an IF to leave the cell blank , if no dates entered in the range
=IF(MAX(BB2:BK2)=0, "", MAX(BB2:BK2))

Will also need to do that on the

=INDEX(BB1:BK1,MATCH(MAX(BB2:BK2),BB2:BK2,0))

As it should see 0 as max and return the first 0 found

=IF (INDEX(BB1:BK1,MATCH(MAX(BB2:BK2),BB2:BK2,0))= 0 , "" , INDEX(BB1:BK1,MATCH(MAX(BB2:BK2),BB2:BK2,0)) )