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...
Code:
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.
Code:
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
Code:
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.