Splitting date fails for a part of date column due to different cell format

**Moriexcel** · 11-09-2014, 07:16 AM

Hello,
I have more than 1,000 .csv files (sample attached) with thousands of records in each (not fixed duration, and here i made it shorter).THIS IMAGE IS MY DESIRED OUTPUT. desired output1.png

1. I need to split date to YEAR/MONTH/DAY (in 3 columns). While the cell format is MDY, so I tried to change the format of date column to DMY first.
But the problem is, the cell format is different and the pattern is repeated for the next years. (pls see the image)

2. After splitting, how can I delete the reference DATE column, while I am using YEAR/MONTH/DAY functions? (because i don't need it anymore)

Thank you in advance

**mike7952** · 11-09-2014, 07:47 AM

Maybe this

Please Login or Register  to view this content.

**Moriexcel** · 11-09-2014, 08:18 AM

Thanks Mike, but there is a small problem.11-9-2014 8-56-28 PM.png

**mike7952** · 11-09-2014, 08:23 AM

What's the small problem

**Moriexcel** · 11-09-2014, 08:37 AM

The first 12 days are not correct. Column A should be 1-Jan-1948, 2-Jan-1948, ... , 12-Jan-1948

**mike7952** · 11-09-2014, 07:49 PM

If the Month is always d/m/yyyy then this will work

Please Login or Register  to view this content.

**Marc L** · 11-09-2014, 11:03 PM

Hi,

open a new workbook and paste this code to Sheet1 worksheet module :

Please Login or Register  to view this content.

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

**Moriexcel** · 11-16-2014, 09:14 AM

Thank you MIKE !
This one works for splitting Day-Month-Year in a same column.

**Marc L** · 11-16-2014, 09:43 AM

Works like a breeze on my side with different Excel versions on different computers with your attached csv file !
If you follow the directive …

Which error ? (code, message, line, …)

**Marc L** · 11-16-2014, 10:47 AM

Can't view your jpg files ! Did you try to see them from posts ?

My code works on my side with your attached csv file from post #1, did you try with it ?

**Moriexcel** · 11-16-2014, 11:10 AM

Sorry for the image. It works ! May I ask you to change something, please?
1. I have some extra information on my 1st row which I don't need it. [not necessary I can delete it manually or ...]
2. I need the converted data overwrite in the same file. While your code is running in a new workbook and I have to open new workbook to run the code, save and close repeatedly for each file.
3. Converted data are written in columns C,D,E,F & G and are not starting from column A.
MarcL code-3.png

Thank you again.

**Marc L** · 11-16-2014, 11:31 AM

Can you explain in detail ?

**Moriexcel** · 11-16-2014, 11:42 AM

Originally Posted by Marc L

Can you explain in detail ?

Am I running the code correctly?
1. Opening a new workbook and paste the code in sheet1 module
2. Replacing source folder of my files

Please Login or Register  to view this content.

3. Playing the code, asks me to choose the file (e.g. 1344108.csv)
4. Converting data from original file to existing new workbook (which i have run the code in)
5. Manually save the new workbook with my original file name and replace it.

1. ** I need those data converted and overwrite on 1344108, instead of writing the data on new workbook or saving the new file with file name (e.g. 1344108) and save it in another folder. [the one is easy to write the code]
2. ** I have so many files, and I have to do 1-5 steps for each file in a folder.

**Marc L** · 11-16-2014, 12:59 PM

You just want to mod a 3 columns csv file to a 5 columns csv file by overwriting source file ?

No need Excel to do that ! (but VBA can of course)

**Moriexcel** · 11-16-2014, 01:09 PM

Originally Posted by Marc L

You just want to mod a 3 columns csv file to a 5 columns csv file by overwriting source file ?

No need Excel to do that ! (but VBA can of course)

Yes. Exactly.
Just please consider that in Row1 there is some extra information which I don't need it and should be deleted by program.
Thank you !

**Marc L** · 11-16-2014, 09:16 PM

This pure VBA demonstration modifies each 3 columns comma separated csv file of a source folder :

Please Login or Register  to view this content.

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

**Moriexcel** · 11-17-2014, 08:35 AM

If you don't mind, it gives me a message box saying that: "Mod in folder XXXX : 0", while I am running for a number of files.
Where should I paste this code and run?
Do I have to upload some sample files here?

**Marc L** · 11-17-2014, 09:23 AM

As usual, works on my side with attached sample file !
This codes doesn't need a specific location 'cause there is nothing to do with Excel ‼

Zero mod means a bad source folder or no 3 columns comma separated csv file in the folder …

**Marc L** · 11-17-2014, 09:24 AM

You can follow the code via F8 key to understand your issue …

**Moriexcel** · 11-17-2014, 09:43 AM

Deleted content and added another reply instead due to my own fault.

**Moriexcel** · 11-17-2014, 10:16 AM

CRAZY STUPID fault ! When I paste the source folder, I've deleted the last "\" !
THAAAAAANKS.

There is only one problem. This the end of this headache to you

If you read the title of this thread, I have a small problem in my dataset with 1st 12 days of each month. Mike suggests a code to solve it.
Could you please add Mike's code to convert data correctly?

11-17-2014 10-00-09 PM.png

**Marc L** · 11-17-2014, 10:30 AM

I hadn't this issue with your sample file, did you try with it ?

**Moriexcel** · 11-17-2014, 01:52 PM

Originally Posted by Marc L

I hadn't this issue with your sample file, did you try with it ?

Your code is definitely correct and works well. But yes, I do have that problem even on my sample file. The one I have discussed with Mike earlier.
But, since I have this problem on my datasets, I would appreciate if you could help me to combine both with the first priority to run that (Mike's code) and then run your code.

**Marc L** · 11-17-2014, 02:36 PM

My post #16 code works - on my side with different Excel version on different computers - whatever the date format !

So I don't understand your issue. Same issue with my post #7 code ?

But if you prefer Mike's code, no problemo, use it !

**Moriexcel** · 11-17-2014, 03:29 PM

I already approved your #16 code as complete solution for my issue posted in #1. Mike's code covers only the thing I mentioned. It is depending on my windows date format, that's why I need to run a code before, to change the format of 1st column to DMY.
Thanks for your patience, since I run the code wrongly sometimes !!

By the way, I have sent a private message to you. Have you received it?
I would like to expand the problem maybe in another NEW Thread and I would appreciate if you could help me to expand your code.
MANY THANKS again.

**Marc L** · 11-18-2014, 07:42 PM

Thanks for the rep' !

If my code works well with your sample file (as on my side), I can't understand your issue !
Needs a crystal clear and complete explanation …

**Moriexcel** · 11-19-2014, 09:09 AM

Let's upload a real csv file. I have tried to make it clear in images.
1. The problem of date column is, 1st 12 days of each month has different format cell from the others. Before splitting, please write a code to read Col. A in DMY format.
image link

2. The second issue in Col A.(date) is in some files there some missing dates in between.
For this sample file, years 1980 & 2009 are missing.
I need the missing dates fill-in in final converted file and "?" assigned in Value column for them.

So, the final output will be :
final output.png

**Marc L** · 11-20-2014, 04:47 PM

Sorry but I'm a bit busy, I'll see your files in the next days …

**Marc L** · 11-21-2014, 10:55 AM

There was no ID in your original post ‼

The only issue with your last attached file is that you wrote you don't need first line
but in fact there are two lines to avoid in this one !

Please Login or Register  to view this content.

**Moriexcel** · 11-21-2014, 11:26 AM

Many thanks.
I asked for just a part of my problem, but then I realized that it is not a big deal for a professional like you

So, I have uploaded the original file. I wanted to delete the first row manually before, but now your program can avoid 1st row. (Thanks for saving my time)

The "ID" column is not available in the original file, while I need it to be added to 1st Column as I shown in last image. ID is a FileName.

**Moriexcel** · 11-21-2014, 11:36 AM

The content was deleted because it was not related to the main topic.
If you are forum moderator please delete this post only. Thank you

**Marc L** · 11-21-2014, 12:01 PM

Maybe we are some experts but the purpose is to show you the way to moderate your code yourself,
not to do your work !

I work only with existing data in files. For missing date, think about the way to do …

So this is the last mod :

Please Login or Register  to view this content.

**Moriexcel** · 11-21-2014, 01:00 PM

Yes, indeed, you're right
Thank you very much. It helps me a lot to save my time.

Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Try this !

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Try this !

Re: Try this !

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Re: Splitting date fails for a part of date column due to different cell format

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] excel 2007 splitting emulgamated date / time cell without usingthe text to column function

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions