Either

Cells(i, j) = CDate(NewDate)

or declare NewDate as type Date and

Cells(i, j) = NewDate

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"mcphc" wrote in message
...
I have some VB code that changes a cells date value to a new date entered
in
by using an inputbox.

Here is some of the code

CellText = Cells(i, j).Value

NewDate = InputBox("Enter new date", "New Date")

Cells(i, j) = Application.WorksheetFunction.Substitute(.Cells(i, j),
CellText, NewDate)

The cell value is say 31/12/2006 and is formatted to show 31-Dec-06. If
the
entered date for NewDate is say 06/01/2007 the new value for the cell
should
be 06/01/2007 and the cell should show 06-Jan-07. But what actually
happens
is the cell value is 01/06/2007 and the cell shows 01-Jun-07.

I have the regional settings of the computer set to English (Ireland).

How do I get the date to enter in Irish format and not american format?

The problem is the cell with the date I'm replacing is sometimes stored as a
date and sometimes stored as text with other text before or after the date
ie. "DATE: 31/12/2007".

The below will work for cell values stored as dates but not cell values
stored as text.

"Bob Phillips" wrote:

Either

Cells(i, j) = CDate(NewDate)

or declare NewDate as type Date and

Cells(i, j) = NewDate

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"mcphc" wrote in message
...
I have some VB code that changes a cells date value to a new date entered
in
by using an inputbox.

Here is some of the code

CellText = Cells(i, j).Value

NewDate = InputBox("Enter new date", "New Date")

Cells(i, j) = Application.WorksheetFunction.Substitute(.Cells(i, j),
CellText, NewDate)

The cell value is say 31/12/2006 and is formatted to show 31-Dec-06. If
the
entered date for NewDate is say 06/01/2007 the new value for the cell
should
be 06/01/2007 and the cell should show 06-Jan-07. But what actually
happens
is the cell value is 01/06/2007 and the cell shows 01-Jun-07.

I have the regional settings of the computer set to English (Ireland).

How do I get the date to enter in Irish format and not american format?

I don't see why that is a problem, just overwrite it with the date entered
as I showed.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"mcphc" wrote in message
...
The problem is the cell with the date I'm replacing is sometimes stored as
a
date and sometimes stored as text with other text before or after the date
ie. "DATE: 31/12/2007".

The below will work for cell values stored as dates but not cell values
stored as text.

"Bob Phillips" wrote:

Either

Cells(i, j) = CDate(NewDate)

or declare NewDate as type Date and

Cells(i, j) = NewDate

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"mcphc" wrote in message
...
I have some VB code that changes a cells date value to a new date
entered
in
by using an inputbox.

Here is some of the code

CellText = Cells(i, j).Value

NewDate = InputBox("Enter new date", "New Date")

Cells(i, j) = Application.WorksheetFunction.Substitute(.Cells(i, j),
CellText, NewDate)

The cell value is say 31/12/2006 and is formatted to show 31-Dec-06. If
the
entered date for NewDate is say 06/01/2007 the new value for the cell
should
be 06/01/2007 and the cell should show 06-Jan-07. But what actually
happens
is the cell value is 01/06/2007 and the cell shows 01-Jun-07.

I have the regional settings of the computer set to English (Ireland).

How do I get the date to enter in Irish format and not american format?