date format 2000 vs 2003

[MrPez]

I've got an issue with date formatting in excel 2000.
I have a spreadsheet that was written in excel 2003 and in a certain cell the date appears as dd mmm yyyy. When the spreadsheet is opened in 2000 it changes to day, dd mmmm yyyy (e.g. Friday, 23 September 2009) and this no longer fits and displays as ######.
I've tried putting some formatting code in VBA and =today() in the cell, but the formatting just gets ignored.
I've also tried having an empty cell with this startup macro

Private Sub Workbook_Open()
Worksheets("T&G Report").Range("H8").Value = Format(Date, "dd mmm yyyy")
End Sub

but that doesn't seem to have any effect either.
Any suggestions?

[venkat1926]

please check the format in that excel 2000 file.
and also see what is in the formula bar of ths file

[Richard Schollar]

Hi

Don't know why it would do that, but your start up macro should change the number formatting of the cell, not the value:

Private Sub Workbook_Open()
Worksheets("T&G Report").Range("H8").NumberFormat =  "dd mmm yyyy"
End Sub

Richard

[MrPez]

Richard - thanks, it works. Not completely sure where I was going wrong, but it works now!