Use the below function (changing
[E:E] to the appropriate range for your needs) to circumvent this issue (or change to any other format such as "mm/dd/yyyy"):
[E:E].Select With Selection .NumberFormat = "General" .Value = .Value End With
P.S. In my experience, this VBA solution works SIGNIFICANTLY faster on large data sets and is less likely to crash Excel than using the 'warning box' method.
~ Answered on 2016-04-21 13:41:33