Wrong Date Format in Excel? 4

yalamo · Oct 5, 2005

While trying to input some date variables in an Excel 2000 VBA subroutine, I ran into a curious problem:

My default short date format (according to the Regional and Language Options in the Win XP Control Panel) is dd/mm/yyyy, so that 2 October 2005 should be 02/10/2005, which is the way I want it.

However, I found that 2/10/5 was showing up as 10/02/2005, the way it is in the U.S. But if I input 13/9/5 in the VBA UserForm, it would show up as 13/09/2005 in the Excel spreadsheet, which is correct, as far as I'm concerned. Apparently, if the day of the month is 12 or less, Excel assumes you mean the U.S. format. More than 12, and it decides to use the European forman

I did a workaround by using an If..Then..Else statement, but I find this pretty annoying. Is there a bug in Excel date formats?

SkipVought · Oct 5, 2005

Hi,

FYI Why do Dates and Times seem to be so much trouble? faq68-5827

I'm guessing that Excel is using the regional setting format to PARSE your INPUT DATE STRING and CONVERT it to a DateSerial value.

But then, IF the default DISPLAY FORMAT were mm/dd/yyyy, then that's what you'd be seeing.

So, try changing the format of the cells to dd/mm/yyyy and see if that "solves" the dilema.

Skip,
_{[red]A palindrome gone wrong?[/red]

A man, a plan, a ROOT canal...

PULLEMALL!}

PHV · Oct 5, 2005

While trying to input some date variables in an Excel 2000 VBA subroutine
How you did that ?
Where are the date variables coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

yalamo · Oct 5, 2005

I have set up a user form with a text box, where I want to input a date in d/m/y format.

My point was that Excel was inconsistent in the ouputs it gave me. As long as the day of the month was greater than 12, I was getting the right answers. But when the month of October began, and the days of the month were less than 13, I started having problems.

SkipVought, you're right, it's using the regional setting to parse my date string, but apparently only during the latter half of the month. I could change the global setting to mm/dd/yyyy and see what happens, but that is not the way it's supposed to be, and who knows how many old spreadsheets and documents will start showing incorrect dates.

I never had the problem before, until I started using the User Form to enter my data. As I said, I did a workarounf, but what I'm curious about is whether this is a problem specific to my system (Why Me?) or maybe some other people have come across it.

rmikesmith · Oct 5, 2005

yalamo,

No, I was able to duplicate your experience by changing my regional settings to the European format. Also, with my normal U.S. settings, I experienced the issue in "reverse".
I cured it by using something like the following line:

Code:

ActiveSheet.Range("A1").Value = DateValue(txtDate.Text)

Regards,
Mike

yalamo · Oct 6, 2005

Thanks, rmikesmith.

Your workaround looks simpler than mine, and I'm going to try it.

Since you saw the same problem with the US settings (only in reverse), what I don't understand is: hasn't anyone ever noticed it before? Admittedly, it's a rather recondite problem, but in all the years that Excel VBA has been around, you'd think somebody would have noticed it.

xlbo · Oct 6, 2005

Yes - they have - many times

What is happening is that VBA loves U.S date format - it can't get enough of it

Whenever you send a date via VBA, it will see it as a US date.
01/02/2005 in a userform will be read as 1st February
01/02/2005 in VBA is read as 2nd January

Becuase you cannot have a month > 12, anything >12 in the dd portion of a UK date cannot be converted properly - VBA then decides that this must therefore be TEXT - any dates that look "normal" will in fact be text and not dates at all. Only dates that can be converted (ie those with <=12 in the dd portion of the UK date will be actual dates (but the wrong ones)

To get around this, you must do as rMikeSmith has suggested and 1st convert the date in the textbox to its serial number - use this throughout the code and to input into any cells - the format of the cells will then take care of how the date is displayed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

rmikesmith · Oct 6, 2005

Bravo Geoff!

Excellent description of the issue.

Regards,
Mike

xlbo · Oct 6, 2005

It should be - I've had to deal with it far too many times !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

Loomah · Oct 6, 2005

Even though I only ever seem to be passing through these days I think that's a star worthy explanation on quite a common issue. I certainly had never realised that VBA was treating dates differently to the application itself!

Suddenly it seems obvious, if I think about it!!

;-)

If a man says something and there are no women there to hear him, is he still wrong? [ponder]

How do I get the best answers?

yalamo · Oct 6, 2005

xlbo, thanks for the expa\lanation. Since I started this thread, I think you desrve another star, and one for rmikesmith for his fix as well.

mscallisto · Oct 6, 2005

Thank U very much Geoff !!!

SkipVought · Oct 6, 2005

*** damn colonists!!! *** [curse]

Skip,
_{[red]A palindrome gone wrong?[/red]

A man, a plan, a ROOT canal...

PULLEMALL!}

xlbo · Oct 7, 2005

<snigger>

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

turps · Jan 3, 2006

Hi all

This thread has been very helpful with regard to the date within VBA. But I need a little more help.

I have a form that allows the use to click on a button to input the date and time. This then needs to be added to a worksheet and need to display the date and the time within the same cell. Using your code works with the date but displays it as 00:00 for the time. The time I need to use is the time that the user inputs not the time on the PC. How can I add the time function to this line of code?

Regards

David

PHV · Jan 3, 2006

How can I add the time function to this line of code?
Which line of code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

turps · Jan 3, 2006

Code:

ActiveSheet.Range("A1").Value = DateValue(txtDate.Text)

Regards

David

mrmovie · Jan 3, 2006

what happens with implicit adding??

i.e. DateValue(txtDate.Text) + ....some Time here?

xlbo · Jan 3, 2006

Kinda depends on how you are constructing the date & time really doesn't it - please start a new thread and give more info

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

AnotherHiggins · Jan 3, 2006

turps,

You really should create a new thread. This thread was successfully resolved three months ago.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Wrong Date Format in Excel? 4

Technical User

Programmer

MIS

Technical User

Technical User

Technical User

MIS

Technical User

MIS

Technical User

Technical User

Technical User

Programmer

MIS

Instructor

MIS

Instructor

Technical User

MIS

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor