"1/1/1799" - is a date
 

Hi

How do I programmatically set a cell to accept "1/1/1799" as a date.

If I pass it in and then reset the NumberFormat of the cell or the column it
continues to see it as a string,

e.g.
MySheet.Cell(1,1).NumberFormat = "dd/mm/yyyy"
MySheet.Cell(1,1).FormulaR1C1 = Cstr(DateValue("1/1/1799"))

So if I want to show it as 01-Jan-1799 it refuses(!) to see it as a date
and continues to show 1/1/1799

Thanks

Simon

"1/1/1799" - is a date
 

Simon,

Excel doesn't support dates prior to 1-Jan-1900. Thus, 1/1/1799
is merely a text string.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Simon Woods" wrote in
message ...
Hi

How do I programmatically set a cell to accept "1/1/1799" as a
date.

If I pass it in and then reset the NumberFormat of the cell or
the column it
continues to see it as a string,

e.g.
MySheet.Cell(1,1).NumberFormat = "dd/mm/yyyy"
MySheet.Cell(1,1).FormulaR1C1 = Cstr(DateValue("1/1/1799"))

So if I want to show it as 01-Jan-1799 it refuses(!) to see it
as a date
and continues to show 1/1/1799

Thanks

Simon

"1/1/1799" - is a date
 

Simon, excel will not work with dates before 1/1/1900, have a look here
about working with pre 1900 dates
http://j-walk.com/ss/excel/usertips/tip028.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"Simon Woods" wrote in message
...
Hi

How do I programmatically set a cell to accept "1/1/1799" as a date.

If I pass it in and then reset the NumberFormat of the cell or the column
it
continues to see it as a string,

e.g.
MySheet.Cell(1,1).NumberFormat = "dd/mm/yyyy"
MySheet.Cell(1,1).FormulaR1C1 = Cstr(DateValue("1/1/1799"))

So if I want to show it as 01-Jan-1799 it refuses(!) to see it as a date
and continues to show 1/1/1799

Thanks

Simon

"1/1/1799" - is a date
 

Thanks Chip and Paul

It all makes sense, now!!!

Simon

"Paul B" wrote in message
...
Simon, excel will not work with dates before 1/1/1900, have a look here
about working with pre 1900 dates
http://j-walk.com/ss/excel/usertips/tip028.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"Simon Woods" wrote in message
...
Hi

How do I programmatically set a cell to accept "1/1/1799" as a date.

If I pass it in and then reset the NumberFormat of the cell or the
column
it
continues to see it as a string,

e.g.
MySheet.Cell(1,1).NumberFormat = "dd/mm/yyyy"
MySheet.Cell(1,1).FormulaR1C1 = Cstr(DateValue("1/1/1799"))

So if I want to show it as 01-Jan-1799 it refuses(!) to see it as a
date
and continues to show 1/1/1799

Thanks

Simon