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 procedu

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 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 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 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 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 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 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 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 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 wrote:
On 17 Apr, 20:55, Mark Lincoln 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 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 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 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

Hi Stuart €“

The procedure below converts either serial dates or string dates to serial
dates (and optionally to strings) in €˜ddmmyy format. Note the following:

1. If your source dates are text strings, you have to tell the procedure how
the date is constructed by modifying some variables in the declarations
section. Otherwise, date functions (datevalue or dateserial) cant
consistently interpret year, month, or day.

2. The procedure replaces the dates €œin-place€� so test it on dummy data.
Or, modify the column €˜OffSet to put the results in an empty column.

3. You mentioned that your €˜system accepts only numeric values. Dates are
numeric, so I assumed you mean numeric digits. Therefore, the procedure
optionally converts dates to strings that contain only numeric digits.

Sub convertDatesInPlace()
'----------------------------------------------------------------
'If dates are text strings, set the values in next 3 statements.
'If dates are true excel serial dates, no modification is needed.
' Enter the within-string order for (m)onth, (d)ay, and (y)ear.
' Enter the date delimiter used.
' Enter number of digits in the year portion (2 or 4).
m = 1: d = 2: y = 3
delimiter = "-"
yeardigits = 2
Set dateRange = Range("A1:A100") '<====adjust input range to suit.
'-----------------------------------------------------------------
'1 is subtracted for split array index
m = m - 1: d = d - 1: y = y - 1
For Each dt In dateRange
On Error GoTo invalidDate
With dt.Offset(0, 0) '<==adjust column offset for results
If IsDate(dt) Then
.NumberFormat = "ddmmyy"
If WorksheetFunction.IsNumber(dt) Then
'Cell contains serial date
.Value = DateValue(dt)
Else
'cell contains valid textual date string, but must be
'converted to true excel serial date before formatting
'because the DateValue function may misinterpret date.
parts = Split(dt, delimiter)
mnth = Val(parts(m))
dy = Val(parts(d))
yr = Val(parts(y)) + IIf(yeardigits = 2, 2000, 0)
.Value = DateSerial(yr, mnth, dy)
End If
End If
End With
invalidDate:
On Error GoTo 0
Next 'dt

'Optional conversion of serial dates to text
If MsgBox("Output dates as text?", vbYesNo) = vbYes Then
For Each dt In dateRange
If IsDate(dt) Then
With dt.Offset(0, 0)
.NumberFormat = "@"
.Value = Format(Day(dt), "0#") & _
Format(Month(dt), "0#") & _
Right(Year(dt), 2)
End With
End If
Next 'dt
End If
End Sub

-----
Jay