Extract Exact Date from cell that is a formula using textsplit

[gmooney]

I have cell (AF4) with a formula are utilizing the following formula to return results.

=LET(a,VALUE(TEXTSPLIT($AE4,,CHAR(10))),b,XLOOKUP(a,'[2024.xlsx]Data'!P$2:P$35000,'[2024.xlsx]Data'!D$2:D$35000,"",0),TEXTJOIN(CHAR(10),TRUE,b))

This will return something like:

WAVE 01
WAVE 03

From that I have another cell (AG4) that I want create a formula that will looking up the date value for WAVE 01, WAVE 02, WAVE 03, etc......

The formula I have for it is:

=LET(a,(TEXTSPLIT($AF4,,CHAR(10))),b,XLOOKUP(a,WAVE LU Date!D$2:D$13,WAVE LU Date!C$2:C$13,"",0),TEXTJOIN(CHAR(10),TRUE,b))

It does what it is supposed to be returning:

45404
45460

I need to get these into proper date format.

Any help would be greatly appreciated.

 

[Eric W]

Try:

Book1
	AF	AG
4	WAVE 01 WAVE 03	01/01/2024 07/04/2024
Sheet3

Cell Formulas
Range	Formula
AG4	AG4	=LET(a,(TEXTSPLIT($AF4,CHAR(10))),b,XLOOKUP(a,'WAVE LU Date'!D$2:D$13,'WAVE LU Date'!C$2:C$13,"",0),TEXTJOIN(CHAR(10),TRUE,TEXT(b,"mm/dd/yyyy")))

Use the date format you prefer.

 

[gmooney]

Try:

Book1
	AF	AG
4	WAVE 01 WAVE 03	01/01/2024 07/04/2024
Sheet3

Cell Formulas
Range	Formula
AG4	AG4	=LET(a,(TEXTSPLIT($AF4,CHAR(10))),b,XLOOKUP(a,'WAVE LU Date'!D$2:D$13,'WAVE LU Date'!C$2:C$13,"",0),TEXTJOIN(CHAR(10),TRUE,TEXT(b,"mm/dd/yyyy")))

Use the date format you prefer.

Try:

Book1
	AF	AG
4	WAVE 01 WAVE 03	01/01/2024 07/04/2024
Sheet3

Cell Formulas
Range	Formula
AG4	AG4	=LET(a,(TEXTSPLIT($AF4,CHAR(10))),b,XLOOKUP(a,'WAVE LU Date'!D$2:D$13,'WAVE LU Date'!C$2:C$13,"",0),TEXTJOIN(CHAR(10),TRUE,TEXT(b,"mm/dd/yyyy")))

Use the date format you prefer.

Thank you Eric...didn't realize it was this simple.