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?