Extract Exact Date from cell that is a formula using textsplit

gmooney

Active Member
Joined
Oct 21, 2004
Messages
257
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try:

Book1
AFAG
4WAVE 01 WAVE 0301/01/2024 07/04/2024
Sheet3
Cell Formulas
RangeFormula
AG4AG4=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.
 
Upvote 0
Solution
Try:

Book1
AFAG
4WAVE 01 WAVE 0301/01/2024 07/04/2024
Sheet3
Cell Formulas
RangeFormula
AG4AG4=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
AFAG
4WAVE 01 WAVE 0301/01/2024 07/04/2024
Sheet3
Cell Formulas
RangeFormula
AG4AG4=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.
 
Upvote 0

Forum statistics

Threads
1,226,735
Messages
6,192,733
Members
453,752
Latest member
Austin2222

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top