Stubborn Date Format

  • Thread starter Thread starter OrientalPearl
  • Start date Start date
O

OrientalPearl

Hello,

Im trying to populate a cell with a date which is created by
concatenating Day & Month & Year values in a format "dd/mm/yyyy". It is
then assigned to the cell also formatted as 'dd/mm/yyyy'. However the
actual result(displayed in the cell) is still "mm/dd/yyyy". How could
this happen and how should it get rectified?

Thanks in advance and regards
Frank
 
Hello,

Im trying to populate a cell with a date which is created by
concatenating Day & Month & Year values in a format "dd/mm/yyyy". It is
then assigned to the cell also formatted as 'dd/mm/yyyy'. However the
actual result(displayed in the cell) is still "mm/dd/yyyy". How could
this happen and how should it get rectified?

Thanks in advance and regards
Frank
Click to expand...

You're not specific enough for me to be sure exactly what you are doing.

What is a Month value? Is it a number or a text string like "May"?

If you are concatenating numbers, Excel will interpret the input based on your
Windows Regional Settings. The format only controls how the date is displayed.

It would be best to use an unambiguous representation for date input.

e.g.

=DATE(2006,4,3)

or

3 Apr 2006

rather than

04/03/2006 which could be interpreted, depending on the Windows settings, as

4-Mar-2006

or

3-Apr-2006


--ron
 
Thanks Ron for your response.

The month value is also a number. All information, including day, month
and year, is retrieved from an import file, with all 3 as a number.
However DATE(yyyy, mm, dd) may be an Excel funtion and thus not
available in VB macro (I tried but it seems DATE() works returning the
system date but does not with any parameters). Is there another way of
arranging some input into a desired date format in VB code?

I have also checked my window's regional settings by inspecting Control
Panel > Regional and Language Options > Regional Options tab >(after
clicking 'Customize...' button) Date tab. The Short Date section shows
the correct date format: 'd/mm/yyyy', with '/' as the delimiter.

Regards
Frank
 
In VBA, it is DateSerial

from the immediate window:

yyyy = 2006
mm = 4
dd = 22
? dateserial(yyyy,mm,dd)
4/22/06
 
Thanks Tom. DateSerial works! I use:

formatDate = DateSerial(CInt(strYear), CInt(strMonth), CInt(strDay))
masterworkbook.Sheets("shipper").Cells(7, 8).Value = formatDate

The correct date can now be returned. But why was it displayed as
'mm/dd/yyyy' since NOWHERE in Excel and my entire OS has that format at
all? So DateSerial is unavoidable when formatting date? I thought this
could be done by just setting both the cell and its value as the right
format, rather than calling another function. Doubt if I made a detour
somewhere....

Thanks again and regards
Frank
 
Thanks Ron for your response.

The month value is also a number. All information, including day, month
and year, is retrieved from an import file, with all 3 as a number.
However DATE(yyyy, mm, dd) may be an Excel funtion and thus not
available in VB macro (I tried but it seems DATE() works returning the
system date but does not with any parameters). Is there another way of
arranging some input into a desired date format in VB code?

I have also checked my window's regional settings by inspecting Control
Panel > Regional and Language Options > Regional Options tab >(after
clicking 'Customize...' button) Date tab. The Short Date section shows
the correct date format: 'd/mm/yyyy', with '/' as the delimiter.

Regards
Frank
Click to expand...

I see Tom directed you to DateSerial.

So far as the display format you noted using the concatenation method that you
had been using, it'd hard to tell why without more info.

However, I was not aware that you had been processing through VBA. VBA can be
very US-centric in the date area, so I would opine that ambiguous dates (such
as 04/03/2006) would be interpreted in the US fashion and that may explain some
of your issues.


--ron
 
The following statements were used previously:

strFormatDate = strDay & "/" & strMonth & "/" & strYear
masterworkbook.Sheets("shipper").Cells(7, 8).Value = strFormatDate

Clearly, strFormatDate is a string but conforming to the right Date
format. Together with Cells(7, 8) also with the right Date
format(dd/mm/yyyy), it should do as supposed.

I intend to agree with the US-centric feature(inherited from US
culture? ;-) of VBA as pointed out. Nevertheless, I am still amazed by
how powerful and penetrating it is in that it could manage to transform
"03/04/2006"(in April) to "04/03/2006"(in March) without any knowledge
backing up such an arbitrary transform?? Not weirdly enough, it does
know it should keep "26/04/2006" as it is(tried myself)!!

Thanks Ron and regards
Frank
 
masterworkbook.Sheets("shipper").Cells(7, 8).Value = cDate(strFormatDate)

should solve your problem.

"26/04/2006" can't be misinterpreted.

"03/04/2006" is ambiguous. It is a legitimate date either way.
 
The following statements were used previously:

strFormatDate = strDay & "/" & strMonth & "/" & strYear
masterworkbook.Sheets("shipper").Cells(7, 8).Value = strFormatDate

Clearly, strFormatDate is a string but conforming to the right Date
format. Together with Cells(7, 8) also with the right Date
format(dd/mm/yyyy), it should do as supposed.

I intend to agree with the US-centric feature(inherited from US
culture? ;-) of VBA as pointed out. Nevertheless, I am still amazed by
how powerful and penetrating it is in that it could manage to transform
"03/04/2006"(in April) to "04/03/2006"(in March) without any knowledge
backing up such an arbitrary transform?? Not weirdly enough, it does
know it should keep "26/04/2006" as it is(tried myself)!!

Thanks Ron and regards
Frank
Click to expand...


It'd be interesting to figure out exactly what's going on.

I did some testing, changing my regional settings to d/mm/yyyy as yours, and
ran the Sub below.

It suggests that when VBA writes a string to an Excel cell, the result is
US-Centric. But in circumstances where true dates are being referenced, it
interprets the dates per the Regional Settings:

Run 1 with d/mm/yyyy

Windows Regional Date Settings: day-month-year
VBA: 04-Mar-2006
A1: 03-Apr-2006
A2: 04-Mar-2006


Run 2 with US Settings

Windows Regional Date Settings: month-day-year
VBA: 03-Apr-2006
A1: 03-Apr-2006
A2: 03-Apr-2006

So it seems that if VBA writes a "true date" to Excel, the conversion of the
string is in accord with the regional settings. But if VBA writes a "string
date" to Excel, the conversion to a date will be US-centric.

In the Sub, before writing to A2, I converted the date in VBA from the
concatenated string to a true date with the CDate function, as you can see
below. (In A1, I merely wrote the concatenated string).



===========================================
Option Explicit

Sub TestDate()
Dim strRegSettings As String
Dim strFormatDate As String
Const strDay As String = "4"
Const strMonth As String = "3"
Const strYear As String = "2006"

strFormatDate = strDay & "/" & strMonth & "/" & strYear

Select Case Application.International(xlDateOrder)
Case Is = 0
strRegSettings = "month-day-year"
Case Is = 1
strRegSettings = "day-month-year"
Case Is = 2
strRegSettings = "year-month-day"
Case Else
strRegSettings = "undefined"
End Select

Debug.Print "Windows Regional Date Settings: " & strRegSettings

Debug.Print "VBA: " & Format(strFormatDate, "dd-mmm-yyyy")

Range("A1").Value = strFormatDate
Range("A1").NumberFormat = "dd-mmm-yyyy"

Debug.Print "A1: " & Range("A1").Text

Range("A2").Value = CDate(strFormatDate)
Range("a2").NumberFormat = "dd-mmm-yyyy"

Debug.Print "A2: " & Range("a2").Text

End Sub
=======================================
--ron
 
masterworkbook.Sheets("shipper").Cells(7, 8).Value = cDate(strFormatDate)

should solve your problem.

"26/04/2006" can't be misinterpreted.

"03/04/2006" is ambiguous. It is a legitimate date either way.
Click to expand...

It's interesting how VBA works with regard to the Regional Settings.

It seems that within VBA, it does interpret dates in accord with the Windows
Regional settings. (That's why CDate works properly).

However, if it writes a string formatted as a date to an Excel cell, that
string gets converted to a US-centric date.

If that string is first converted within VBA to a true date (i.e. with the
CDate function), then the result is a date interpreted in accord with the
Windows Regional settings.

Weird.

--ron
 
masterworkbook.Sheets("shipper").Cells(7, 8).Value = cDate(strFormatDate)
Yeah. It also does the trick and should be regarded as the decent and
preferred way to DateSerial(feels a bit awkward compared to cDate()!)
"26/04/2006" can't be misinterpreted.
Click to expand...
I know..that's why I said not weird enough...so it's not very stupidly
stubborn!

Thanks again Tom for your help along the way(a few times so far) which
is ALWAYS very helpful!! =)
 
However, if it writes a string formatted as a date to an Excel cell, that
string gets converted to a US-centric date.
Click to expand...

I think what happens is that Excel takes that string from VBA, despite
already in the desired non-US date format, as a US-date format. Then it
tries to be 'smart and helpful' to convert it to the date formatting of
the cell, i.e. non-US date in this case. It keeps doing this until such
a conversion no longer makes sense, e.g. Day > 12.

Use of 'cDate' eliminates the ambiguity because Excel knows the result
of cDate function is already a date with a format it knows, i.e. the
system date format. Then depending on the situation(the date format of
the cell), it either does the transform or not as required.
 
Thanks for the extensive testing on this case Ron!!

Yeah, Excel can perform as expected on this issue if and only if:
A. The value is a true Date, and
B. It knows its Date format.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel is changing date format incorrectly 7
International Date formatting 4
VB Date Format 8
Dates converted into text 5
Date formatting 1
Different Date formats in text to be recognised as date value 8
date format 6
Excel date displayed wrongly 3

Back
Top