|re: "I can't format the dates which are in System driven format,"|
I don't know what you mean by "System driven format" but I can tell you that very often data that gets downloaded from websites and/or other applications ends up in Excel as Text instead of numbers or dates.
Since I can't see your spreadsheet from where I'm sitting, I'm going to take a guess and suggest that both of your problems are related to each other.
Let's start with Problem 2:
re: I end-up getting dates as 02-Sep-2011, but the actual date is 02/09/2011
This statement does not really make sense. 02/09/2011 could actually be 02-Sep-2011 or it could actually be 09-Feb-2011, depending on how Excel (actually Windows) is set up. Let me explain.
The format in which Excel recognizes dates is based on the settings in the Regional and Language Control Panel, Customize button, Date tab. This is what determines whether the first 2 digits represent the day or the month.
My guess is that the values that you cannot format as dates are those where Excel can not recognize the month.
For example, since your system is recognizing 02/09/2011 as 02-Sep-2011 I'll assume that your Windows setting is something like dd-mmm-yyyy.
Excel has no problem changing 02/09/2011 to 02-Sep-2011 since 09 is a valid month, but it wouldn't know what to do with 02/15/2011 since there is no 15th month. My guess is that Excel decides that values like 02/15/2011 must be text and therefore cannot be formatted as a date.
Try changing your Control panel setting to something like mm-dd-yyyy and see if that helps.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.