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
How do .csv files render dates from the original source?
-
- NewLounger
- Posts: 3
- Joined: 24 Mar 2021, 17:28
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How do .csv files render dates from the original source?
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.
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.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 24 Mar 2021, 17:28
Re: How do .csv files render dates from the original source?
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
WesternGuy
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How do .csv files render dates from the original source?
Could you attach a sample workbook? We only need a column with problem dates, so you can remove everything else.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 24 Mar 2021, 17:28
Re: How do .csv files render dates from the original source?
Hans, here is a sample. All of the data has been changed, except for the dates. Hope this works.
WesternGuy
WesternGuy
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How do .csv files render dates from the original source?
Data > Text to Columns works for me - see the short video at https://www.dropbox.com/s/6w75nmoq05bps ... 9.wmv?dl=1
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: How do .csv files render dates from the original source?
or you can try Power Query
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...
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"
if you have more csv files in the same format you can try From Folder feature then Change Type...
You do not have the required permissions to view the files attached to this post.
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: How do .csv files render dates from the original source?
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.
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.
Regards,
John
John