date format by formula help

[orsm6]

Hi all - recently got help to take AU date format in one cell and convert it to US date format by formula in the cell to right. It worked fine, but now we are further along in the month something weird is happening.

you can see from the image uploaded that:
- 12/02/2024 converts fine to 2/12/2024......

- but 13/02/2024 is converting to 2/1/2025??? where it should be 2/13/2024

this is the formula =LET(sDt,TEXT(A41,"dd/mm/yyyy"),aDt,TEXTSPLIT(sDt,"/"),DATE(INDEX(aDt,,3),INDEX(aDt,,1),INDEX(aDt,,2)))

where am i going wrong?
TIA

 

[orsm6]

I suspect it is because it thinks there is a 13th month in the formula so it is erroring out somehow.

i am using the converted date to do a lookup into a table of data where the dates in the table have been unfortunately formatted differently to AU

 

[orsm6]

I think that i need to convert the format, but then store the value as text?

 

[orsm6]

i tried using =TEXT(A41,"m/dd/yy")

it works for any date that is 13/mm/yy and up... but for any value that is less it converts it to an actual date and my lookup won't work.

 

[RoryA]

TEXT will not return an actual date. If the values in column A are actual dates, then a lookup should work regardless of format, unless the values in the lookup table are stored as text?

 

[orsm6]

TEXT will not return an actual date. If the values in column A are actual dates, then a lookup should work regardless of format, unless the values in the lookup table are stored as text?

yeah - as a work around I have done this... =IF(DAY(A41)<=12,LET(sDt,TEXT(A41,"dd/mm/yyyy"),aDt,TEXTSPLIT(sDt,"/"),DATE(INDEX(aDt,,3),INDEX(aDt,,1),INDEX(aDt,,2))),TEXT(A41,"m/dd/yy")) so that if it is recognised as a date it will get the right format, but if not a date it will place text string in a format i need it in.

i did it like this.. because the source data can be both date and text.

 

[RoryA]

How does the source data get into the worksheet?

 

[orsm6]

How does the source data get into the worksheet?

the data is downloaded from a web based portal as a csv. the values are copied out of the csv and paste into the final dest sheet.
the formula then interrogates the data