Excel Macro Not Converting and Formatting Date Properly - Help Please?

[bdpna]

Hi all,

New here and to Excel macros. I inherited this one and I can't seem to figure out what should be a simple problem. The macro essentially is taking a CSV file and walking through it line by line to create PDF, Excel, and TXT versions of the CSV data.

The PDF and Excel versions look great. The code looks hacky but it works.

Here's the input line:

G1234, , 53.00,50.00,58.00,56.00,56.00,55.00, AA,10012018

I'm trying to re-output this as a similar CSV but with a properly formatted date for the last item on the line.

What's output unfortunately is:

****G1234**53.00*50.00*58.00*56.00*56.00*55.00*43374.00

What I WANT output is (note the final item, the date):

****G1234**53.00*50.00*58.00*56.00*56.00*55.00*10/01/2018

Here's the code snip for this part of the macro. Please let me know if you want to see more of the macro than this.

Please Login or Register  to view this content.

Help?

[bakerman2]

Use DateSerial to create your Date before writing to textfile.

[AlphaFrog]

Change this...
Trim(Left(Cells(J, 13).Text, 2)) & Trim(Mid(Cells(J, 13).Text, 4, 2)) & Trim(Right(Cells(J, 13).Text, 4))

To this...
Format(Cells(J, 13).Text, "00/00/0000"

[bdpna]

Really kind of everyone to respond, thanks.

I think the second solution looks easier to implement since I don't know anything about excel macros or DateSerial. Unfortunately I inherited this code with no Excel macro training.

Will report back some results.

[bdpna]

I think we're getting so close here but there has to be some funky reason this is converting wrong.

The date string is the CSV of "1012018" seems to convert to "00043101" or "00/04/3101" using your Format command. The date string is the CSV of "10012018" converts to "00/04/3191". What the heck is going on here? Some sort of ASCII mess?

So,

Format(Cells(J, 13).Text, "00/00/0000") of 1012018 = 00/04/3101
Format(Cells(J, 13).Text, "00/00/0000") of 10012018 = 00/04/3191

[bakerman2]

DateSerial(year, month, day)

Please Login or Register  to view this content.

[bdpna]

Thank you so much, this is the closest it's ever been! It's killing me because I just don't see how it could be getting the date so wrong. It's definitely a different conversion depending on the date, but I'm wondering if the fact that sometimes the date is 7 characters and sometimes 8 characters matters?

Here's where we are after that code change. I've never seen it close even in the year format so I'm pleased at that but still baffled at how it's making this leap.

1012018 = 7/1/2004
10012018 = 9/12/2007

Anyone have any other ideas? Really appreciate everyone who took a minute to help, you are nice people.

[bakerman2]

If I put this 10012018 in Range A1 and run

Please Login or Register  to view this content.

this is what I get.
10/01/2018

[bdpna]

I just found an earlier statement in the macro that may answer this issue:

Range("E2:L" & FinalRow).NumberFormat = "0.00"

There were a couple columns removed from the input file and this should end at I now, not J. I assume that NumberFormat command may be what is mucking with our dates!

[bdpna]

That was the problem. I totally missed it when I posted it in the code snippet at the start of the thread. That very first line was causing the date format attempts to be overridden, it tried to format it as currency and it went nutty.

Thank you, marking solved!

[bakerman2]

Glad it sorted out.

[bdpna]

Hey all, resurrecting this old thread as this macro is acting up again. I'll start a new thread if that makes more sense but if anyone has any ideas, I could sure use some help. Between this and quarantine, crazy!

So this macro is parsing though a CSV file that was built previously, parsing it out into an asterisk-delimited text file.

One of the fields in the CSV is a date field, but not in the proper format, so I use this command to right it:

Please Login or Register  to view this content.

On the mydate command, as it makes its way down the file (it gets around 277 lines in, every time) it hits a 13 mismatch error.

Can anyone clue me in on what's in my data that would cause this to happen?

Really appreciate it. At my wit's end!!! Or is there a way to set something to show me all the values here to determine the data error causing crash?

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[bdpna]

My bad and thanks! Done! Can the title flag for solved be removed?

[AlphaFrog]

Did you type in your code in post #12? It has several syntax errors. It wouldn't compile.

The Debug.Print line will list each row number and cell value in the Immediate window (Ctrl+G). If it errors on one row, it will be the last one listed in the Immediate window. Report back what is in that cell.

Please Login or Register  to view this content.