entering date dd/mmm/yyyy in VB gives mmm/dd/yyyy
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? |
entering date dd/mmm/yyyy in VB gives mmm/dd/yyyy
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? |
entering date dd/mmm/yyyy in VB gives mmm/dd/yyyy
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? |
All times are GMT +1. The time now is 12:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com