Need advice re: date without slashes

[laavista]

My customer is providing a spreadsheet with dates. Most of the dates are in
the correct format that I need -- mm/dd/yy. However, some of the dates are
coming in without slashes, e.g., 111109.

I'm using VBA to format the spreadsheet, then will compare each row's date
with another date in another workbook. I need all the dates to be in the
mm/dd/yy format.

Formatting the cell does not work.

Should I loop through each date, use an "isdate" function to see if it's a
valid date, then if not, parse it and add the slashes?

Is there a better method?

Your help would be greatly appreciated!

 

[JBeaucaire]

That's how I would do it. I don't think you any other choice since the values
are already in Excel as General number, formatting the cell won't change that
fact.

 

[Jacob Skaria]

You can use the 'Convert Text to Columns Wizard to convert the dates

--Select the range of dates which needs to be corrected.

--From menu Data>'Text to Columns' will populate the 'Convert Text to Columns
Wizard'.

--Hit Next>Next will take you to Step 3 of 3 of the Wizard.

--From 'Column Data format' select 'Date' and select the date format in which
your data is ('MDY').

--Hit Finish. MSExcel will now convert the dates to the default date format
of your computer.

If this post helps click Yes

 

[laavista]

I wasn't aware of this functionality. I think I can make this work.
Thanks!

 

[laavista]

Thanks for taking the time to answer!

That's how I would do it. I don't think you any other choice since the values
are already in Excel as General number, formatting the cell won't change that
fact.

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

 

[vqthomf]

If you want to do it by code you could also use The instr and the mid function
if instr(activecell,"/")=0 then
activecell.value=cdate(mid(activecell,1,2( & "/" & mid(activecell,3,2) &
"/" & mid(activecell,5,2))
end if

hope this helps
Charles

 

[Karan]

Thanks!!

If you want to do it by code you could also use The instr and the mid function
if instr(activecell,"/")=0 then
activecell.value=cdate(mid(activecell,1,2( & "/" & mid(activecell,3,2) &
"/" & mid(activecell,5,2))
end if

hope this helps
Charles