Excel VBA date issues

[Tim Timmers]

Hi,

I have a user form in which I enter a date via a textbox.
The code behind the form stores the dat in a specific cell. Later I read back the date information from this cell and notice that excel gets the date wrong. When I enter the date 6 september 2016 (actually I key in 6-9-2016) the date is displayed in the excel sheet as 6-sep-2016 but when I read back the cell to display the date in a second userform the afore mentioned date comes back as 9 june 2016.

How do I get the correct date back?

I am working in a Windows 7 environment with Excel 2007.
Region an language settings in Windows under the Formats tab: English (UK); short date dd-MMM-yyyy
Location tab: Netherlands.

Can anyone help?

 

[WaterGypsy]

It looks as if the problem is that you are switching date formats at some point ....

Is the date stored as a date field (e.g. 42619) or is it a text field (e.g 09-06-2016). To be safest I'd make sure it is stored as a date field.

 

[Tim Timmers]

Hi,

I modifies my code a little and it now seems to work.
In the old situation writing /reading the date to/from a cell and formatting it as a date was done in two lines of code. I have now changed it to one line of code like:

<<some lines of code<some code="" of="" lines="">>>
.TextBoxFDate = Format$(Range("D45").Value, "short date") 'read the date from cell D45 and display it in a textbox on a userform
<< more lines of code>>

Regrads, Tim</some>

 

[hensleyj]

I had a similar problem for quite a while and there is a simple solution.

When entering the date in the textbox, that pushes it to the CELL, the problem comes from the cell which is usually set to a date format.

Change the cell format on the sheet to "text".

Now when you enter the date in the textbox, it pushes the text as is written with no alterations.

When you display this CELL in another userform or textbox, it will read it correctly if you set the date format correctly.

Prior to doing this, sometimes the date would be correct..othertimes it would be wrong. It seemed to have a mind of its own.