CODEWITHSUNDEEP
vbaexcelms-office
flohdieter
flohdieter

Reputation: 130

VBA Excel 2013 Format Function changes Date

The following line of code changes not only the format of date, it also changes the value of the date. Why does this happen and how can I avoid it?

Code:

rng.Cells(intStartRow, rng.Columns.Count + 1) = _
Format(rng.Cells(intStartRow, rng.Columns.Count + 1).Value, "mmm-yy")

Value of rng.Cells(intStartRow, rng.Columns.Count + 1).Value before executing the code:

enter image description here

Value of rng.Cells(intStartRow, rng.Columns.Count + 1).Value after executing the code:

enter image description here

Upvotes: 0

Views: 281

Answers (2)

INOPIAE
INOPIAE

Reputation: 293

The reason why it happens is that the date information is stored internally as decimal number and is displayed with the date format applied to the cell. In your code you change the date information to a string as the result of the function Format() is always a string. A soultion to solve the problem is suppliey by Rory

Upvotes: 1

Rory
Rory

Reputation: 34035

If you wish to convert to an actual date and change the displayed format, use:

With rng.Cells(intStartRow, rng.Columns.Count + 1)
   .Value = CDate(.Value)
   .NumberFormat = "mmm-yy"
End With

Upvotes: 2

Related Questions