Excel Date Conversion: Text Date With 5 Or 6 Characters

The goal is to format the incoming text date from (eg "9302011" to #09/30/2011#) but the code fails at the first lines of the CInt(left..) The code is generating "93" as the month.

Please evaluate the code attached to the Worksheet which is attached.

Excel VBA Date Convert Data

Why isn't the Excel VBA code working?

Sample VBA Code - Date Conversion

 

I appreciate you reviewing the code and provide feedback for improvement & correction.

In the mean time, have a great Sunday. Thank you very much for the support. – E.L.

ANSWER: Excel Date Conversion Theory

Great question. My solution will work with both Excel worksheet functions and Excel VBA.

The "trick" here is that January – September (months 1-9) are 5 characters long while October through December (months 10-12) are 6 characters long.

You could handle this two ways:

  1. Use logic to check if the text string is 5 or 6 characters long and process accordingly
  2. Reformat the text string to be 6 characters long (add a leading zero) and then process

Solution #1 – Check Date String Length

The first way to solve this problem is to use logic to check to see if the text string is 5 or 6 characters long. So the code  using Excel VBA would be:

Answer: Date Conversion Using VBA

Here is the same solution using Excel worksheet formulas:

Excel Date Convertion using Worksheet Functions Cell C2

Excel Date Convertion using Worksheet Functions Cell C2

Excel Date Convertion using Worksheet Functions Cell F2

Solution #2 – Reformat Date String With Leading Zeros

Another technique you can use is to convert the string into a Long datatype and then convert that back into a string. The Excel VBA code would look like:

Solution 2 - Two lines

Using this technique we have a consistently formatted string that is always going to be six characters long.

Here is this solution using Excel worksheet functions:

 

Date-convert-Strat-2-C2

Date-convert-Strat-2-D2

Solution #2 With More Compact Code…

If you wanted to make the code more compact you could re-write it as:

Solution 2 - One line

The risk that you run with the more compact code is that the type conversion of sPromiseDate into a Long datatype would fail and you would have a run time error that would be more tricky to handle than with the solution that uses more lines of code.

For example, if someone had "Fat Finger Syndrome" and entered the date text as "90F2011" this would not be able to be converted into a number and the line of code would create a run-time error. This run-time error would be easier to handle if you had a two step process versus a one step process.

This is a general rule: the more compact the code (fewer lines) the more difficult it is to implement error handling. The more lines of code the easier it is to implement error handling.

, ,

One Response to Excel Date Conversion: Text Date With 5 Or 6 Characters

  1. George WJ Shearer March 26, 2012 at 6:17 pm #

    Here's my approach (the concept works in any environment/language):
    NewDate = Right$("0" & OldDate, 6)

    This will convert 5-char dates to 6, and leave 6-char dates as 6. Now ALL your dates are 6 chars.

    Examples:
    Right$("0" & "101212", 6) = Right$("0101212", 6) = "101212"
    Right$("0" & "91012", 6) = Right$("091212", 6) = "091212"

Leave a Reply