Convert Numbers to Date Format

[GreyFox8991]

Greetings Excel Community,

I have numbers formatted in the following Text or general format: 20221026.. I am trying to format it as mm/dd/yyyy.... I have used a formula as follows but it is not generating the correct output: Formula is
=DATE(LEFT(G2|4)|MID(G2|5|3)|RIGHT(G2|2)). I am including a screenshot. Column "mm/dd/yyyy" is the result of the formula however, it is not displaying the Correct date which is in the "Correct Format" Column. Any insight would be appreciated!

 

[alansidman]

Here is a power query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByRepeatedLengths(4), {"Date.1", "Date.2"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position", "Date.2", Splitter.SplitTextByRepeatedLengths(2), {"Date.2.1", "Date.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position1",{"Date.2.1", "Date.2.2", "Date.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"New Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"New Date", type date}})
in
    #"Changed Type"

Date	New Date
19521016	10/16/1952
19501003	10/3/1950
19280302	3/2/1928
20221026	10/26/2022

 

[Tetra201]

See if the following formula works for you:

=--TEXT(G2,"0000-00-00")

 

[Dave Patton]

T10_1701b.xlsm
	E	F	G
36	20221026	Oct 26, 2022	Oct 26, 2022
1ee

Cell Formulas
Range	Formula
F36	F36	=DATE(LEFT(E36,4),MID(E36,5,2),RIGHT(E36,2))
G36	G36	=--TEXT(E36,"00\/00\/00")