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?