Convert Date into Proper date

sadath

Board Regular
Joined
Oct 10, 2004
Messages
246
Hi

i have table from web source
Source = Web.Page(Web.Contents("http://www.accuweather.com/en/sa/dammam/297095/january-weather/297095?monyr=1/1/2016&view=table")),

Date column showing Fri 1/1 , Sat 1/2
how can i convert into proper date (dd/mm/2016) eg. 1/1/2016, 2/2/2016
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,250
Chop off the day names from the front, concatenate /2016 to the back, then convert to date format
 

citizenbh

Board Regular
Joined
Sep 19, 2013
Messages
139
As recommended by Matt try this:

Code:
let
    Source = Web.Page(Web.Contents("http://www.accuweather.com/en/sa/dammam/297095/january-weather/297095?monyr=1/1/2016&view=table")),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    Data = #"Removed Other Columns"{0}[Data],
    #"Split Column by Delimiter" = Table.SplitColumn(Data,"Date",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Date.1", "Date.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type text}, {"Date.2", type text}, {"Hi/Lo", type text}, {"Precip", type text}, {"Snow", type text}, {"Forecast", type text}, {"Avg. HI / LO", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year", each 2016),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Year", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each [Date.2]&"/"&[Year]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom",type date}},"en-US")
in
    #"Changed Type2"

Pay attention to the "en_US" in order to obtain the required format "DD / MM / YYYY"
 

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
212
Reading through the forum just trying to gain knowledge, is this line (#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom",type date}},"en-US") the equivalent of changing the date using locale?
 

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Almost: it is the equivalent of changing the data type for column "Custom" to date, using locale.
Probably this is what you intended to ask?
 

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
212
Correct, I have been reading/working through the book M is for data Monkey that you suggested to me. Slowly picking things up.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top