substitute formula not working correctly on a specific date formula

I've attached a workbook illustrating the raw format I get dates in, and the format I need. I've tried the SUBSTITUTE formula but what it does is convert to the Excel format (e.g. 43359.12523).

Once it does that, I am unable to format the date as I wish.

Any advice?
Thanks!

=TEXT(A8,"yyy.mm.dd")

Thanks Ghozi!

Formatting dates (or any other numeric values) with the text function is not advisable unless absolutely necessary, it has the potential to cause more problems than it fixes.

Just apply a custom format to the raw data, no formula needed.

yyyy.mm.dd

Thanks Jason, I appreciate the advice. I assume you mean to go to the format window, go to the"custom" settings and create one manually using yyyy.mm.dd... and that willl turn my existing raw data into a readable date format I can use in whatever other formulas I need to.

That is correct, the formulas might need to manipulate the dates slightly as your raw data contains a time as well, so will not match up to another cell that is date only.

Whenever you enter dates, it is best to enter them in line with your regional settings, then use a custom format to change the appearance as needed.