Need advice re: date without slashes

  • Thread starter Thread starter laavista
  • Start date Start date
L

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!
 
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.
 
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
 
Thanks for taking the time to answer!

JBeaucaire said:
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.
Click to expand...
 
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
 
Thanks!!

vqthomf said:
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
Click to expand...
 
Back
Top