Forum Discussion

KirkExcel's avatar
KirkExcel
Copper Contributor
May 17, 2022
Solved

Date formatting Excel

Hello all,   Have been trying to get text formatted as dates correctly in Excel. Alas, nothing happens. Source is as follows: https://www.investing.com/commodities/crude-oil-historical-data Despit...
excel
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 17, 2022

    KirkExcel Copied the table and pasted as text into Excel. Then used Text to Columns on the data ribbon.

     

    Selected the date column.

    Step 1: Delimited, next

    Step 2: Un-check all delimiters, next

    Step 3: Select Date and MDY from the dropdown, Finish

     

    Then it looks like in the attached file. Column A now has real dates.

     

Lorenzo's avatar
Lorenzo
Silver Contributor
May 17, 2022

Hi KirkExcel 

 

No idea how you get the information (copy/paste, Power Query, something else...) from the webiste you referenced. Values in column A below are copied/pasted from that site

 

in B2:

=DATE(
    --RIGHT(A2,4),
    MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),
    --MID(A2,5,2)
)

then format the cell as you want

KirkExcel's avatar
KirkExcel
Copper Contributor
May 17, 2022
I downloaded the data via the download to spreadsheet function of the site. Haven't tried your method yet, as the text to columns feature ended up working, but thanks for your help.
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    May 17, 2022
    You're welcome. As long as you have something that solves the issue and you're happy with all is fine

Resources