LinkBack URL
About LinkBacks
Hi All
i have an export of data where the date format comes out in [25 JAN 21] format, please can someone help with a formula/way to convert into normal date format?
please see attached actual format vs desired output
many thanks
David
Hi All
i have an export of data where the date format comes out in [25 JAN 21] format, please can someone help with a formula/way to convert into normal date format?
please see attached actual format vs desired output
many thanks
David
Last edited by davidverdon; 02-06-2023 at 08:10 AM.
The issue is that the date field is a text value, so for date formatting to work it needs to be converted into a numeric value.
Try this formula in cell B3:
Formula:
=DATE( 2000 + RIGHT(TRIM(A3),2), MONTH( 1 & MID( TRIM(A3), FIND( " ", TRIM(A3))+1, 3) ), LEFT( TRIM(A3), FIND(" ",TRIM(A3) ) ) )
<<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts
Power Query
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3", "Date.4"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Date.4", type text}, {"Date.2", type text}}, "lt-LT"),{"Date.4", "Date.3", "Date.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"), #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Date.1"}) in #"Removed Columns"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks