New To VBA

[TPRSCHM]

Good Morning,

I'm still new to VBA and have learned a lot over the past month but I seam to have hit a brick wall with trying to figure out how to create a VBA to convert a text in both MDDYYYYY or MMDDYYYY formats to a standard M/DD/YYYY or MM/DD/YYYY format

Thanks
Jim

 

[TPRSCHM]

pbcnick,

That worked like a charm, Thank you so much for helping me out

Jim

 

[pbcnick]

Just pointing out that your formula returns the converted date as a text string, not a real Excel date. If that is okay with the OP, then there is a one-liner equivalent for your function...

Function ConvertDate(ByVal strDate As String) As Variant
   ConvertDate = Evaluate("IF(" & strDate & "=0,"""",TEXT(" & strDate & ",""0\/00\/0000""))")
 End Function

From what I see, if the OP chooses to uncomment the last line in my function (shown below) the result would be a date format. Just wondering if I'm missing something. I always use this format because I can easily compare if a date is before another using simple operators in IF statements, like <, > or =.
Please let me know if I'm missing something.

convertDate = CDate(sM & "/" & sD & "/" & sYYYY) ' use this to convert to Date type (used for comparison)

Also, thank you for the one liner. I should really pick up a book and learn all those helpful methods like TEXT and EVALUATE. (they are methods, right?)
From my quick search it seems TEXT is similar to FormatNumber, so I see what you are doing here which is basically formatting the date nicely versus my archaic DD & MM & YYYY method. noted for future work so when people look at my code I might look a little smarter

TEXT(" & strDate & ",""0\/00\/0000""))"

I see the IF part of your formula to handle the cells with a value of 0. so if it's 0 (TRUE) insert / return a blank, otherwise its FALSE and use the TEXT method to nicely format date and return that.
I also looked at the EVALUATE method and that you are calling the result of the IF with the either a 0 or the formatted Date.
Can you clue me into why the need to use EVALUATE here? Is this needed to make all this work? I tried removed EVALUATE and I couldn't get it to work.
Thanks for your time.

 

[pbcnick]

pbcnick,

That worked like a charm, Thank you so much for helping me out

Jim

glad to help, Jim.

I also learned a thing or two today.
Keep working at your book and learn loops, select case and other forms of control flow to do all kinds of things in VBA!
Hopefully you'll be writing functions in no time!

This is a reminder to self to brush up on formulas and methods (like the ones Rick gave an example).
Since VBA is one of my programming tools, I have a lot of space to learn.

Hope you get some time under your wings as well.
cheers

Nick