How to keep the date format in VBA ?

  • Thread starter Thread starter kauri
  • Start date Start date
K

kauri

I'v got a macro which copies from one worksheet to another. I've foun
that if the date in the first worksheet is 01/03/2004; after runnin
the macro...the new worksheet has the date as 03/01/2004. But if th
date in the first worksheet is 29/03/2004; it is copied correctly ont
the second worksheet.. Any idea how I can fix this ?? Our date forma
is dd/mm/yyyy.


Many thank
 
Hi Kauri,

Force it

With Worksheets("Sheet2")
Worksheets("Sheet1").Range("A1").Copy .Range("H10")
.Range("H10").NumberFormat = "dd/mm/yyyy"
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Kauri,
When you enter a date into Excel it is entered on the
basis of your Regional format, not by the format in a cell.

Excel will recognize that a month cannot be greater than 12
so if the month is greater than 12 then that must be the month
and it will swap your month and day of month. The result is you
have a mixture of dates some with correct dayofmonth and month
and some with them swapped.

Once a date is in Excel it uses your short date Regional format
unless you override it. If you override it and it has the same order
as your short regional date then Excel treats it as your Regional
date and you haven't really overridden the date..

If you send your worksheet to someone in the US with mm/dd/yyyy
(m/d/y) then they will see it in their format unless you overrode it.
If you really overrode it then they will see it as overridden unless
Excel simply recorded it at your end as (d/m/y) in which case it
will appear in the order (m/d/y) in the US.

In VBA all dates are treated as US dates, one of.
Stephen Bullen's chapters is on handling dates,
"Developing Excel applications for international clients "
which you will probably want to read if you stick to the
Excel 2002 or earlier version. Read about at
http://www.bmsltd.ie

The net result is that without you telling exactly what you have
and what coding you are using, we can't really give you a lot of
help. But from the sounds of it the damage is probably already
done within your source worksheet.

You can use formulas on the Worksheet such as
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
Particularly for testing you can remove ambiguity by
formatting with a 4 digit year and 3 character Month name.
dd mmm yyyy
or go with the ISO date format yyyy-mm-dd
unfortunately if you use the ISO date format you will also
want to use that order in your Regional short date format.
And you should find out what you actually have
=A1 format as dd mmm yyyy

In VBA it would be much safer to use such formulas
cell.value = DateSerial(year, month, day)

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Further to above. VBA only works properly with dates in
the format mm-dd-yy. If you want to work with dd-yy-yy (as
I do, being English) you have to do some jiggery pokery
with the dates, making sure that VBA 'eats' them in the
American format surrounded by #. Unambiguous dates such as
31/3/04 (31 Mar) are OK but it screws up on ambiguous
dates such as 1/3/04 (1 Mar to us Brits) inerpreting it as
3 Jan (as in America and other parts of the world)
Trevor
 
Not just VBA, all MS scripting languages that I have used (VBScript,JScript,
WSH, etc.)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I'm on British format (dd-mm-yy)
I've always found VBA to be very good with dates. I assume it reads the
regional settings for how to interpret date strings.

The exception for date processing is Excel which hard coded it's support for
the American date format.
Copying a VBA Date variable to Excel works fine. Copying a VBA String
variable containing dd-mm-yy will be interpretted as mm-dd-yy

Example:

'My regional settings are British date formatting dd-mmm-yy
Sub test()
Dim str As String, dtm As Date

str = "02-03-04"
dtm = str
Debug.Print Format(dtm, "dd-Mmm-yyyy") 'outputs 02-Mar-2004

Range("A1").Value = dtm
Range("A1").NumberFormat = "dd-Mmm-yyyy" 'A1 = 02-Mar-2004

Range("B1").Value = str
Range("B1").NumberFormat = "dd-Mmm-yyyy" 'B1 = 03-Feb-2004
End Sub


The workaround is to always format months as mmm if they are to be passed as
a string.
 
Back
Top