VB: date format in an Excel file
 

Dear All,

I got this requirement to export a date string to an Excel file
from VB application. The short date format in each PC's Regioal
Options could be different. When the VB reads the date string
from MSSQL in "MM/dd/yyyy", the application will export it like:

dFormat = GetUserLocaleInfo(LCID, LOCALE_SSHORTDATE)
dS = CDate(Format(itm.SubItems(15), "MM/dd/yyyy"))
xl.Cells(2, 16) = Format(dS, dFormat)

The problem that I am facing is, although the cell format is set
to be "dd-MMM-yy" like "14-Mar-98", there are two types of values
in the column: e.g. "28/09/2005" or "06-Dec-04". After I double
clicked on the cell, "28/09/2005" will then be displayed as
"28-Sep-05".

Is there anyway to show a consistent date format? Could we
control it from the VB program?

TIA!


--
qqxz
------------------------------------------------------------------------
qqxz's Profile: http://www.excelforum.com/member.php...o&userid=30773
View this thread: http://www.excelforum.com/showthread...hreadid=504407

date format in an Excel file
 

Hi

A date is a date is a date, not a string. Formatting is for display only.
What you do here is formatting when you assign it to a variable and also
formatting when you write it into a cell. That is trouble. Assign a real
date to a variable declared as Date, write it directly to a cell and then
format the cell if necessary.

Dim DD as Date
DD = Dateserial(2005, 12, 25)
xl.Cells(2, 16).Value = DD

HTH. Best wishes Harald

"qqxz" skrev i melding
...

Dear All,

I got this requirement to export a date string to an Excel file
from VB application. The short date format in each PC's Regioal
Options could be different. When the VB reads the date string
from MSSQL in "MM/dd/yyyy", the application will export it like:

dFormat = GetUserLocaleInfo(LCID, LOCALE_SSHORTDATE)
dS = CDate(Format(itm.SubItems(15), "MM/dd/yyyy"))
xl.Cells(2, 16) = Format(dS, dFormat)

The problem that I am facing is, although the cell format is set
to be "dd-MMM-yy" like "14-Mar-98", there are two types of values
in the column: e.g. "28/09/2005" or "06-Dec-04". After I double
clicked on the cell, "28/09/2005" will then be displayed as
"28-Sep-05".

Is there anyway to show a consistent date format? Could we
control it from the VB program?

TIA!


--
qqxz
------------------------------------------------------------------------
qqxz's Profile:
http://www.excelforum.com/member.php...o&userid=30773
View this thread: http://www.excelforum.com/showthread...hreadid=504407

VB: date format in an Excel file
 

Thanks! That is very helpful.


--
qqxz
------------------------------------------------------------------------
qqxz's Profile: http://www.excelforum.com/member.php...o&userid=30773
View this thread: http://www.excelforum.com/showthread...hreadid=504407