Log In
Register
Home
Active Member
October 20, 2020
Offline
Hi Myrna, I have viewed your excellent video on converting text and incorrect dates to correct format.
I have a similar problem with a large database where then dates are as such: 200103 (2020-01-03)
IN order to correct this a tried to first to use the split and merge or the faster way of add column from example. This was not working since I was missing the centrury numers 20 as in xx200103. After adding a column in the raw data I finaly ended up with 20200103 and I could accomplish the add column from example.
However the result still became incorrect as seen in attached picture. What did I do wrong?
Big thanks!!
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello Carl,
You have made the date as text. Try below formula. I pretend in this example that the date 200104 is in cell Q25.
=DATUM(20&VÄNSTER(Q25;2);EXTEXT(Q25;3;2);HÖGER(Q25;2))
In English.
=DATE(20&LEFT(Q25,2),MID(Q25,3,2),RIGHT(Q25,2))
This will give you a proper date serial number to work with.
Br,
Anders
Answers Post
October 5, 2010
Offline
Hi Carl,
Please attach your file when asking a qs - without your file I can't see what you are doing or what may have gone wrong.
If you look at my example file I've split the x36 column and then recombined it into the correct format which can then be converted to a date. Here's the code for that.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"x36", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "x36", Splitter.SplitTextByRepeatedLengths(2), {"x36.1", "x36.2", "x36.3"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Position", "Date", each "20" & [x36.1] & "-" & [x36.2] & "-" & [x36.3]),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom", {{"Date", type date}}, "en-SE")
in
#"Changed Type with Locale"
regards
Phil
1 Guest(s)
