I have a file which is sent to me by a third party which I am formatting
using a macro. I am having trouble with the date.

In the cell it contains the date "10/16/2006". I need to covert it into the
format 20061016, but since it is in US format I cannot convert it into a
date and then change it into a custom date like I usually would. I need
advice on how to do this using a macro.

Would this work as a first step?

=YEAR(yourcell)&MONTH(yourcell)&DAY(yourcell)


Cammy wrote:
I have a file which is sent to me by a third party which I am formatting
using a macro. I am having trouble with the date.

In the cell it contains the date "10/16/2006". I need to covert it into the
format 20061016, but since it is in US format I cannot convert it into a
date and then change it into a custom date like I usually would. I need
advice on how to do this using a macro.

Unfortunately this mixes up the day and the month, because of the US
formatting of the date, so it does not work for the date given in the example
below...

" wrote:

Would this work as a first step?

=YEAR(yourcell)&MONTH(yourcell)&DAY(yourcell)


Cammy wrote:
I have a file which is sent to me by a third party which I am formatting
using a macro. I am having trouble with the date.

In the cell it contains the date "10/16/2006". I need to covert it into the
format 20061016, but since it is in US format I cannot convert it into a
date and then change it into a custom date like I usually would. I need
advice on how to do this using a macro.

I guess the first question is, is the underlying date serial number
correct?
Select the cell containing 10/16/2006. Press Alt-F11 to go to the
editor. Press Ctrl-g to show the immediate pane. Type in
? activecell.formula
Do you get 39006? If so, the underlying date serial number is correct
and you can go from there. If not, hmmm.
James
Cammy wrote:
Unfortunately this mixes up the day and the month, because of the US
formatting of the date, so it does not work for the date given in the example
below...

" wrote:

Would this work as a first step?

=YEAR(yourcell)&MONTH(yourcell)&DAY(yourcell)


Cammy wrote:
I have a file which is sent to me by a third party which I am formatting
using a macro. I am having trouble with the date.

In the cell it contains the date "10/16/2006". I need to covert it into the
format 20061016, but since it is in US format I cannot convert it into a
date and then change it into a custom date like I usually would. I need
advice on how to do this using a macro.

See Jim's post. Let us know if the date is a serial date value or text.
Maybe there is another solution.


Cammy wrote:
Unfortunately this mixes up the day and the month, because of the US
formatting of the date, so it does not work for the date given in the example
below...

" wrote:

Would this work as a first step?

=YEAR(yourcell)&MONTH(yourcell)&DAY(yourcell)


Cammy wrote:
I have a file which is sent to me by a third party which I am formatting
using a macro. I am having trouble with the date.

In the cell it contains the date "10/16/2006". I need to covert it into the
format 20061016, but since it is in US format I cannot convert it into a
date and then change it into a custom date like I usually would. I need
advice on how to do this using a macro.

Is this an Excel file or a text file?
Send (my private email) a sample file for me to look at.
When will the whole world start using the rational yyyy/mm/dd format!
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Cammy" wrote in message
...
I have a file which is sent to me by a third party which I am formatting
using a macro. I am having trouble with the date.

In the cell it contains the date "10/16/2006". I need to covert it into
the
format 20061016, but since it is in US format I cannot convert it into a
date and then change it into a custom date like I usually would. I need
advice on how to do this using a macro.

Hi guys, sorry I have been travelling for a few days. When I try to get the
activecell formula I get

10/19/2006 not 39006 as you have predicted.

Bernard, I will send you a copy of the file. Please note I am using UK excel
to do this.

Am off travelling again in the early part of next week, but will check your
posts/email when I return to the office.

Bernard kindly helped me convert the text into a date format. This is the
solution I used when I recorded this process into a macro.....

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True
Selection.NumberFormat = "yyyymmdd"

or to do this without the macro....

Select these cells (or the cells in your sample file) and use Data | Text to
Columns.
Skip Step 1 and Step 2 (just click the OK button)
In Step 3 look to the right of the dialog box for the "Column data format"
area
Select the Data the option (click the radio button to the left of Date) and
in the option box select MDY - this is the existing format of the entries to
be changes. Excel will convert it to a real date.
Click OK; Now the entries have become real dates.

Thanks everyone for your help!

"Cammy" wrote:

Hi guys, sorry I have been travelling for a few days. When I try to get the
activecell formula I get

10/19/2006 not 39006 as you have predicted.

Bernard, I will send you a copy of the file. Please note I am using UK excel
to do this.

Am off travelling again in the early part of next week, but will check your
posts/email when I return to the office.