VBA NumberFormat

[Digit]

Dear group,



Currently I'm building a VBA-program in access which exports a set of data
to excel.



One of the fields is a date field. This I would like to be displayed as
m/d/yyyy.



I've chosen to export the date as integer (ie. 32291 for 5/28/1988)

This since excel otherwise recognizes the input as text or swaps day and
months.

To obtain the date format on the excel sheet I use following code:

AppExcel.Range("G4:G" & Rij).NumberFormat = "m/d/yyyy"
This code has as result that the integer is put in 'd/m/yyyy' format (system
standard)



Since this is the only sheet where this format is required I do not want to
change the system settings.



Exporting the date as string gives visually the correct result however makes
it impossible to sort/calculate later on.



What is the best way to obtain the desired result?



Thanks for your reply,



D

 

[RB Smissaert]

Changing the day format to dd seems to do the trick, but you may of course
not want 01 etc.

Sub test()

Cells(1) = 37200
Cells(2) = 37200
Cells(3) = 37200

Cells(1).NumberFormat = "m/d/yyyy"
Cells(2).NumberFormat = "m/dd/yyyy"
Cells(3).NumberFormat = "dddd d mmmm yyyy"

End Sub


RBS