Combo Box Date Format

[Vampy99]

Hi,

I have a simple button with 3 combo box's on it - one each for day, month & year, in that order.

This gives me the variable "vDate"

I then add the string 08:00:00 to the value of "vDate" which gives me variable "wDate".

Just for arguments sake, lets say today's date is entered into the combox's. My variables would now be as follows;

vDate = 22/09/2011
wDate = 22/09/2011 08:00:00

Now, I simply want to display the "wDate" variable in a single cell, lets say A1. However, when I do this the day & month switch around e.g. if wDate = 01/09/2011 08:00:00, when displayed it shows as 09/01/2011 08:00:00.

The weird thing is, this only happens up until the 12th of each month, 13 & onwards displays correctly. I'm thinking 12 - that's how many months there are? I don't particularly care what format the date is in as long as it is constant, I just need the number value for some formula's later on. Also, I need the combobox to remain in a simple format where a user can select a date.

The only things I've tried up to now are formatting the column in Excel, & formatting the column in VB - both to no avail.

Has anyone ever seen this before or can suggest a fix?

Thanks,

Luke

 

[Arul.rajesh]

You have to change your Date settings.

Sub country()
MsgBox "Windows Date setting:- " & Application.International(xlMDY)
End Sub

what does this return?

It should be false for you.
If it is true set it to false.

 

[Vampy99]

You have to change your Date settings.

Sub country()
MsgBox "Windows Date setting:- " & Application.International(xlMDY)
End Sub

what does this return?

It should be false for you.
If it is true set it to false.

It is returning false, so I'm assuming this is ok.

Really at a loss with this one.

 

[Vampy99]

Ok I have found a solution.

I declare "vDate" in cell A1 which just gives me the date - all now showing in the correct format (dd/mm/yyyy).

In cell B1 I have the 8am number, 0.3333333333.

If I just add these together & format as general I get the correct number which I was after.

Thanks,

Luke

 

[Norie]

You could try this:

Range("A1").Value = DateValue(vDate) +TimeValue(8,0,0)