Date Format Issue

[aquixano]

In the attached workbook the cells in A1 and B1 on Sheet1 have the same date format. Cell A1 I entered manually and Cell B1 is from an import using VBA from a .csv file. I want Cell B1 to look like A1 and if I click in B1 and hit enter it does. I can probably code in an Edit/Enter routine into my vba, but that seems sloppy and I was wondering if there is a better way to accomplish the desired result.

Any suggestions? Bonus credit if you can tell me why the import is behaving that way when the format is the same.  Thanks for reading and any suggestions.

[AlphaFrog]

A1 is a serial date, and B1 is a Text date. Your CSV file dates import as text entries. Even though they "look" like dates, Excel doesn't recognize them as serial dates.

One method to convert your B1 Text-date to a serial date is to select it and press enter. Another is to select the column of dates and use either Text-to-Columns or the Replace function.

How are you importing the CSV file?

[MarvinP]

Hi aquixano,

If you imported data is text then you can use =DateValue() to convert it to real dates. Then format that column to show them as you desire.

[aquixano]

I populate a userform with info from the .csv in case I want to edit anything before posting to my workbook using this code (the date code is between the two asterisk lines)

Please Login or Register  to view this content.

I use the following line of code to post to the final worksheet I use to log the info.

Please Login or Register  to view this content.

Let me know if you have other questions - thanks for the help.

[Marc L]

Cell A1 I entered manually and Cell B1 is from an import using VBA from a .csv file.

So it's just a bad import so rather than fixing the data after the import
the better is to just well import them as Excel can handle this with a correct import setup !

Or to fix after you can use the Text To Columns feature from the Data menu, easy to use with an assistant
like the Import Wizard, just needs the appropriate answers in particular for the cell / column format.

[aquixano]

Thanks AlphaFrog & MarvinP
I added the Datevalue to the line: .Range("K" & CloseRW & "").FormulaR1C1 = Datevalue(PendingForm.tbDATE) and it solved the problem.

I should have realized it was text based on the justification was using – guess I never realized that simply clicking in the cell and hitting enter would change the contents type (even though nothing was actually changed.

Thanks to both of you for the help!

[aquixano]

Thanks for the suggestion Mark L!

[Marc L]

Both ways can be used under VBA …

[AlphaFrog]

You're welcome. Thanks for the feedback.

The red bit seems superfluous.

Please Login or Register  to view this content.

...and there was talk of bonus points.

[aquixano]

AlphaFrog - you definitely deserve bonus points for all the help you’ve given me today & in the past. I’ll double check the red highlighted code. Seems like I have an old memory that I couldn’t make a concatenated reference work in a piece of code without it and have just continued to do it. But I may have accidentally solved a different problem with it too. That or I’m just “mis-remembering.” Thanks for the suggestion.