Jun 03 2020 06:55 AM
Hello there
I have a message i copy every day and it has dates in them as following format
Wed Jun 3rd, 2020
How can i come up with an equation to change this into a date?
Regards
Jun 03 2020 07:31 AM
I managed to make it work with LEFT and RIGHT equations then DATEVALUE then DATE to combine all of them
Let me know if theres any easier way? because this depends on the format never changing
Jun 03 2020 08:05 AM
Jun 03 2020 09:32 AM
I think you should at least consider using Power Query to import the data. The following splits the text and rebuilds the date, in this case using non-US formatting
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Dates", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Weekday", "Month", "Day", "Year"}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Split Column by Delimiter", "Day", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Day", "Suffix"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Character Transition", "Date", each [Day]&[Month]&[Year]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Weekday", "Month", "Day", "Suffix", "Year"})
in
#"Removed Columns"
Jun 03 2020 11:38 AM
@Peter Bartholomew @Dustin-Doucette
Its just text from a website, and it comes in the format provided above
I managed to get it to work with this formula
=DATE(RIGHT(G2,4),MONTH(DATEVALUE(RIGHT(LEFT(G2,7),3)&"1")),LEFT(RIGHT(G2,10),2))
So i am extracting and changing it into date format but again this would continue to work only if the format stays the same, which I think most probably it would
but is there any other way in excel not VBA or MACRO etc.. I dont have knowledge of those things
Jun 03 2020 03:44 PM
Power Query is on the Data ribbon tab and there is a 'get from web' option in which you provide the URL and then Excel reads the data back for editing before loading it to a Table. If the source data content changes over time, the refresh button will fetch the new dataset from the web, edit it as before, and load it to the workbook. No need for a browser or for any cut and paste, just 'refresh'.