CODEWITHSUNDEEP
excelvba
Linexxlol
Linexxlol

Reputation: 77

Having weird bug with date format in excel

So i have an activeX combobox that shows dates in the format "dd-mm-yyyy". The dates are taken from a range on another sheet. I have a linked cell (lets say A1) in which the date from the combobox is displayed.

It works fine for most dates but for some reason on some dates like 10-09-2018 or 11-03-2018, it changes format in the linked cell to "mm-dd-yyyy".

Does it get confused about which of the first two values are day and month?

Edit: the cells in my range are all text, and my linked cell is Date, however it didn't work to change these on either my linkedcell or range.

Code for my combobox, E26 is the cell with the problem, E29 is just a cell where i add 2 years to the date, it always shows the date correctly.

Private Sub ProdDateCombobox_Change()
Range("E26") = Format(Me.ProdDateCombobox.Value, "dd-mm-yyyy")
Range("E29") = DateAdd("yyyy", 2, Me.ProdDateCombobox.Value)
End Sub

Upvotes: 1

Views: 393

Answers (1)

Linexxlol
Linexxlol

Reputation: 77

I managed to fix it! its a bit of a "hack" but i simply replaced

Range("E26") = Format(Me.ProdDateCombobox.Value, "dd-mm-yyyy")

With this:

Range("E29") = DateAdd("yyyy", 0, Me.ProdDateCombobox.Value)

Its weird and i don't know why it works but it works. Thanks for trying to help.

Upvotes: 1

Related Questions