Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Compare cell value with Custome Format

0

Hi There,

I am writting a Macro where i have to read a CSV file and in that CSV file there is a field called SystemDate which should conatin the value in "YYYY-MM-DD HH:MM:SS.0" formatĀ .So while reading the CSV file i have to check the SystemDate field if that field value in the above mentioned format or not ?

Appeciate if some one guide me.

Thanks

Smruti

Answer
Discuss

Discussion

Can you just check the date once it is imported into Excel? That way, if Excel doesn't automatically read it as a date, you can assume that is it not the correct format. Or, are you just looking for code that parses the date, which will be a literal string in the CSV file, and then checks to make sure it looks OK before the import?

These two scenarios are quite different.
cappymer1 (rep: 120) Apr 20, '17 at 10:53 am
Add to Discussion

Answers

0

VBA manages dates as numbers, an integer for a day and a fraction for the time of day. To gain control of imported date strings you must convert the strings to numbers. In order to do so you must know the format. Hence, the best solution is not to import the strings. Can you get at the numerical values from which the strings were created?

Presuming that this isn't the case, presuming that all dates are of the same format, and that you have a lot of them, you can write a macro which determines the format. This will not be possible if the year is represented by 2 digits And you don't know its position (first or last). You can look for a date separator (dash, slash, period) if the separator isn't known before you start. With the separator you can split the date string into its 3 groups. The one with 4 digits is the year. You can look for a number greater than 12 in the middle group. If you find one the middle group has the day and the remaining is the month.

With this information you can create the DateSerial number which you can write into your imported data and, thereafter, issue your own date string in any format you like.

All of this might be done on the raw data before import or on the imported data immediate afterwards.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login