The subject line is exactly what I need,

I do not want the dates to have any characters between them. I just
literally want six digits.

Our system cannot accept character only numberics.

If a macro could do this that would be amazing,

Let me know,

Thanks

Stuart

Hi Stuart -

It sounds like the Replace function might work for you. Here is a
demonstration procedure:

Sub Stuart()
Const dt = "14/03/07"
new_dt = Replace(dt, "/", "")
MsgBox new_dt
End Sub

---
Jay


"Stuart" wrote:

> The subject line is exactly what I need,
>
> I do not want the dates to have any characters between them. I just
> literally want six digits.
>
> Our system cannot accept character only numberics.
>
> If a macro could do this that would be amazing,
>
> Let me know,
>
> Thanks
>
> Stuart
>
>

On Apr 17, 3:06 pm, Stuart <swilson2...@gmail.com> wrote:
> The subject line is exactly what I need,
>
> I do not want the dates to have any characters between them. I just
> literally want six digits.
>
> Our system cannot accept character only numberics.
>
> If a macro could do this that would be amazing,
>
> Let me know,
>
> Thanks
>
> Stuart


Change the cell formatting to "ddmmyy".

You have to help excel a little bit--by typing in those slashes/dashes so that
excel knows that it's a date.

But after that, you can format the cell/range anyway you want including:

ddmmyy

The underlying value will still be a date, but it'll look the way you want.

Stuart wrote:
>
> The subject line is exactly what I need,
>
> I do not want the dates to have any characters between them. I just
> literally want six digits.
>
> Our system cannot accept character only numberics.
>
> If a macro could do this that would be amazing,
>
> Let me know,
>
> Thanks
>
> Stuart

--

Dave Peterson

Stuart

Not a macro but you could probably develop one from this.

=1000000+(YEAR(A1)-2000)*10000+MONTH(A1)*100+DAY(A1)


Gord Dibben MS Excel MVP

On 17 Apr 2007 12:06:08 -0700, Stuart <(E-Mail Removed)> wrote:

>The subject line is exactly what I need,
>
>I do not want the dates to have any characters between them. I just
>literally want six digits.
>
>Our system cannot accept character only numberics.
>
>If a macro could do this that would be amazing,
>
>Let me know,
>
>Thanks
>
>Stuart

I cannot get any of the above suggestions to work. When I change the
date formatting to ddmmyy it does not work for anything above
04/12/2007

Is this something someone can help with?

Kind Regards

Stuart







On 17 Apr, 20:38, Gord Dibben <gorddibbATshawDOTca> wrote:
> Stuart
>
> Not a macro but you could probably develop one from this.
>
> =1000000+(YEAR(A1)-2000)*10000+MONTH(A1)*100+DAY(A1)
>
> Gord Dibben MS Excel MVP
>
> On 17 Apr 2007 12:06:08 -0700, Stuart <swilson2...@gmail.com> wrote:
>
>
>
> >The subject line is exactly what I need,
>
> >I do not want the dates to have any characters between them. I just
> >literally want six digits.
>
> >Our system cannot accept character only numberics.
>
> >If a macro could do this that would be amazing,
>
> >Let me know,
>
> >Thanks
>
> >Stuart- Hide quoted text -
>
> - Show quoted text -

I'm guessing from your original post that you're entering dates into
cells. Is this true? If not, ignore me. Otherwise...

If I change a cell's format to ddmmyy and enter 4-17-07, the cell
displays 170407. What do you see?

On Apr 17, 3:45 pm, Stuart <swilson2...@gmail.com> wrote:
> I cannot get any of the above suggestions to work. When I change the
> date formatting to ddmmyy it does not work for anything above
> 04/12/2007
>
> Is this something someone can help with?
>
> Kind Regards
>
> Stuart
>
> On 17 Apr, 20:38, Gord Dibben <gorddibbATshawDOTca> wrote:
>
>
>
> > Stuart
>
> > Not a macro but you could probably develop one from this.
>
> > =1000000+(YEAR(A1)-2000)*10000+MONTH(A1)*100+DAY(A1)
>
> > Gord Dibben MS Excel MVP
>
> > On 17 Apr 2007 12:06:08 -0700, Stuart <swilson2...@gmail.com> wrote:
>
> > >The subject line is exactly what I need,
>
> > >I do not want the dates to have any characters between them. I just
> > >literally want six digits.
>
> > >Our system cannot accept character only numberics.
>
> > >If a macro could do this that would be amazing,
>
> > >Let me know,
>
> > >Thanks
>
> > >Stuart- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

On 17 Apr, 20:55, Mark Lincoln <mlinc...@earthlink.net> wrote:
> I'm guessing from your original post that you're entering dates into
> cells. Is this true? If not, ignore me. Otherwise...
>
> If I change a cell's format to ddmmyy and enter 4-17-07, the cell
> displays 170407. What do you see?
>
> On Apr 17, 3:45 pm, Stuart <swilson2...@gmail.com> wrote:
>
>
>
> > I cannot get any of the above suggestions to work. When I change the
> > date formatting to ddmmyy it does not work for anything above
> > 04/12/2007
>
> > Is this something someone can help with?
>
> > Kind Regards
>
> > Stuart
>
> > On 17 Apr, 20:38, Gord Dibben <gorddibbATshawDOTca> wrote:
>
> > > Stuart
>
> > > Not a macro but you could probably develop one from this.
>
> > > =1000000+(YEAR(A1)-2000)*10000+MONTH(A1)*100+DAY(A1)
>
> > > Gord Dibben MS Excel MVP
>
> > > On 17 Apr 2007 12:06:08 -0700, Stuart <swilson2...@gmail.com> wrote:
>
> > > >The subject line is exactly what I need,
>
> > > >I do not want the dates to have any characters between them. I just
> > > >literally want six digits.
>
> > > >Our system cannot accept character only numberics.
>
> > > >If a macro could do this that would be amazing,
>
> > > >Let me know,
>
> > > >Thanks
>
> > > >Stuart- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


Hi there,

When I key in "4-17-07" I still see "4-17-07" even after formatting to
ddmmyy.

I am keying the dates in at the moment to test this however the data
will be pasted in from a text dump and there will be no manual keying
in of the dates.

I just want to be able to format it the way I need to see the data.

Stuart

Stuart,

Here's a (very) quick-and-dirty bit of code:

Sub DoIt()
Dim S As String
Open "C:\testfile.txt" For Input As #1
S = Input(6, #1)
Close #1
Range("A1") = S
End Sub

Cell A1 was first formatted as Text. I've assumed the input from your
system is in the format you're describing, so testfile.txt contains
the string 050407 (or April 5, 2007). The result of the code is that
Cell A1 contains 050407, just as the input string does.

So the short answer, if my assumptions are correct, is to format your
date field(s) as Text before importing your data.

Does this help?

Mark

On Apr 17, 3:58 pm, Stuart <swilson2...@gmail.com> wrote:
> On 17 Apr, 20:55, Mark Lincoln <mlinc...@earthlink.net> wrote:
>
>
>
>
>
> > I'm guessing from your original post that you're entering dates into
> > cells. Is this true? If not, ignore me. Otherwise...
>
> > If I change a cell's format to ddmmyy and enter 4-17-07, the cell
> > displays 170407. What do you see?
>
> > On Apr 17, 3:45 pm, Stuart <swilson2...@gmail.com> wrote:
>
> > > I cannot get any of the above suggestions to work. When I change the
> > > date formatting to ddmmyy it does not work for anything above
> > > 04/12/2007
>
> > > Is this something someone can help with?
>
> > > Kind Regards
>
> > > Stuart
>
> > > On 17 Apr, 20:38, Gord Dibben <gorddibbATshawDOTca> wrote:
>
> > > > Stuart
>
> > > > Not a macro but you could probably develop one from this.
>
> > > > =1000000+(YEAR(A1)-2000)*10000+MONTH(A1)*100+DAY(A1)
>
> > > > Gord Dibben MS Excel MVP
>
> > > > On 17 Apr 2007 12:06:08 -0700, Stuart <swilson2...@gmail.com> wrote:
>
> > > > >The subject line is exactly what I need,
>
> > > > >I do not want the dates to have any characters between them. I just
> > > > >literally want six digits.
>
> > > > >Our system cannot accept character only numberics.
>
> > > > >If a macro could do this that would be amazing,
>
> > > > >Let me know,
>
> > > > >Thanks
>
> > > > >Stuart- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> Hi there,
>
> When I key in "4-17-07" I still see "4-17-07" even after formatting to
> ddmmyy.
>
> I am keying the dates in at the moment to test this however the data
> will be pasted in from a text dump and there will be no manual keying
> in of the dates.
>
> I just want to be able to format it the way I need to see the data.
>
> Stuart- Hide quoted text -
>
> - Show quoted text -

On Apr 17, 3:58 pm, Stuart <swilson2...@gmail.com> wrote:
> When I key in "4-17-07" I still see "4-17-07" even after formatting to
> ddmmyy.

I think the problem you are experiencing is because Excel does not
recognize 4-17-07 as a date, probably because the date settings in
your Control Panel indicate that dates should be in the format dd-mm-
yy rather than mm-dd-yy. Try entering 17-4-7 and see if that gets
formatted correctly.

Cheers,
David G