How do .csv files render dates from the original source?

[WesternGuy]

I have started to download monthly files from my bank as .csv files and then save/convert them as .xlsx or .xlsm files. One of the columns is a date that looks like MM/DD/YYYY, e.g., 01/04/2021. I tried to reformat it as a “Date” — YYYY-MM-DD, but it did not work – nothing happened. I tried to use the DATEVALUE function and all I got was #VALUE! . Is there anyway to find out what type of data this “Date” is – numeric, text, etc., as all the other values – Deposits, Withdrawals, etc., convert as numbers, which is what they should do. I tried using the MONTH function to extract the month, but that produced another #VALUE! error. I was able to extract the month as a numeric value using the LEFT( X2,2) function and that gave me the month as a number, but I would really like to produce a “date” value that I can format and treat as a real date from whatever the date is that comes through during the .csv translation.

I am running Windows 10-20H2 and Excel as part of Office 365.

All of the dates that are produced by the .csv conversion are exactly the same, so maybe I have to extract each component separately and then recombine them into a date somehow (any ideas).

Any insight that anyone can provide will be greatly appreciated. TIA.

WesternGuy

[HansV]

Welcome to Eileen's Lounge!

Try the following:
Select the column with dates.
Format it as yyyy-mm-dd.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited, then click Next >.
Click Next > again.
Select Date, and select DMY from the dropdown next to it.
Click Finish.

[WesternGuy]

HansV, thanks for the reply. The problem I have is that the "Date" column will not reformat as I have tried this without success, therefore, I am unable to proceed with your suggestions.

WesternGuy

[HansV]

Could you attach a sample workbook? We only need a column with problem dates, so you can remove everything else.

[WesternGuy]

Hans, here is a sample. All of the data has been changed, except for the dates. Hope this works.

WesternGuy

[HansV]

Data > Text to Columns works for me - see the short video at https://www.dropbox.com/s/6w75nmoq05bps ... 9.wmv?dl=1

[hamster]

or you can try Power Query

dates.png

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Column1", type date}}, "en-US")
in
    #"Changed Type with Locale"

after that, dates will change to your local dates format
if you have more csv files in the same format you can try From Folder feature then Change Type...

[jstevens]

WesternGuy,

MyOnlineTrainingHub is another good resource should you want to use a Power Query solution. Click on the hyperlink and it will take you to a youtube video.

Mynda has a series of videos that are quite good.