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

 

[JLGWhiz]

one way, Add a line:

xlSh.Cells(lRow, 11) = Format(xlSh.Cells(lRow, 11).Value, "dd mmm yyyy")

Or change the original to

xlSh.Cells(lRow, 11) = Format(Date, "dd mmm yyyy")

 

[aschaak]

You ask if you can sort it somehow. Do you want to sort the what has been sent from Access to Excel or do you want to put the date into the dd mmm yyyy format?

If you want to sort it, please remember that as long as it is a date, Excel considers the serial date. So the three examples below are all the same number, just formatted differently:

By the way, to get that third example I highlighted the number, Format, More Number Formats, Custom, dd mmm yyyy.



[TABLE="width: 576"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]11/10/2017[/TD]
[/TR]
[TR]
[TD="align: right"]43050[/TD]
[/TR]
[TR]
[TD="align: right"]12 Nov 2017
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Just an observation - if that is indeed coming in as a real date (and not text), this it doesn't really matter how it is displayed, the underlying value will still be the same, just change the format as needed

 

[AlexanderBB]

Thanks for the replies.
JLG, neither worked for me, the dd-mm-yy format was retained. "Format" seems to be ignored.
Yes, I can set a Custom format, but that means Opening Excel. At the moment that's one step saved, the xls is created (exported) from the 3rd party program, changed in Access then Imported back.
I keep thinking there must be a default date Format in Excel, but I haven't found it (yet?)
( aschaak, it's the date format as displayed in the worksheet that's wanting to be changed)

 

[AlexanderBB]

Yes, it *does* matter how it's displayed because that's whats imported back into the destination, Excel is just the intermediary.

 

[FDibbins]

Yes, it *does* matter how it's displayed because that's whats imported back into the destination, Excel is just the intermediary.

What I meant by "doesnt matter" is that the underlying contents wouldn't change, and that a quick reformat will then show it how you need it to look. Generally, formatting is only a cosmetic application to a cell to make it appear as we want it to

 

[AlexanderBB]

True, but in this case the underlying content isn't relevant, WYSIWU want.

But just to confirm - there is no way to set a default Date Format (in Excel 2003) ?

 

[FDibbins]

Yes, there should be, but you would need to change it in such a way that it changes all default dates in excel. It's been a while since I used 2003, but there is (I know) somewhere a setting for default settings... stuff like decimal places, column width etc. Did you dig into the settings menu>?

 

[AlexanderBB]

Tools-Options was all I could find, and nothing there. Will have to devise some other non-Excel way. Thanks