Displaying dd mmm yyyy

[AlexanderBB]

I'm connecting to Excel 2003 from Access 2007 to populate a worksheet created by a 3rd party program.

From Access I send

xlSh.Cells(iRow, 11).Value = Day(Now) & " " & MonthName(Month(Now), True) & " " & Year(Now)

This should result in dd mmm yyyy but Excel shows this as dd-mmm-yy I guess adhering to a preset date format.

Can I sort this somehow ? Thanks

 

[taigovinda]

I'm not too clear on what your requirement is here... but just a thought: if it only needs to *look* like what you want, and the actual value doesn't matter, then you could try a leading apostrophe in your code from post #1 :

xlSh.Cells(iRow, 11).Value = "'" & Day(Now) & " " & MonthName(Month(Now), True) & " " & Year(Now)

Tai

 

[AlexanderBB]

Thanks Tai, that's a brilliant suggestion! Works spot on....

 

[JLGWhiz]

You have to use Control Panel to set the system default format for the short date.
1.In the search box on the taskbar, type control panel, and then select Control Panel.

2.Under Clock, Language and Region, click Change date, time, or number formats

3.Click Regional and Language Options.

4.In the Region dialog box, click Additional settings.

5.Click the Date tab.

6.In the Short date format list, click a format that uses four digits for the year ("yyyy").

7.Click OK.