Need advice date without slashes
 

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!

Need advice date without slashes
 

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.


"laavista" wrote:

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!

Need advice date without slashes
 

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 NextNext 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
---------------
Jacob Skaria


"laavista" wrote:

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!

Need advice date without slashes
 

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

"Jacob Skaria" wrote:

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 NextNext 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
---------------
Jacob Skaria


"laavista" wrote:

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!

Need advice date without slashes
 

Thanks for taking the time to answer!

"JBeaucaire" wrote:

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.


"laavista" wrote:

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!

Need advice date without slashes
 

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

"laavista" wrote:

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!

Need advice date without slashes
 

Thanks!!

"vqthomf" wrote:

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

"laavista" wrote:

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!