CODEWITHSUNDEEP
excelvbaexcel-formula
K Sudbury
K Sudbury

Reputation: 45

Excel date format VBA

I set the value of a cell to the current date using the following code

rng.Value = Format(Now(), "dd/mm/yyyy")
rng.NumberFormat = "dd/mm/yyyy"

When I run this code the date is inserted in to the cell and the format is "Date" but the data in the cell is left aligned. Formulas don't interpret the value as a date.

Cell using code above

When I click into the cell and then press ENTER the value shifts to the right and the date now works with the formulas.

enter image description here

If I enter the date manually the formulas also work. It is only when I use VBA.

Upvotes: 2

Views: 982

Answers (2)

jkpieterse
jkpieterse

Reputation: 3006

Two remarks. 1. You are in fact inserting both date and time (that is what the NOW function returns). If you only need the date, use the VBA Date function instead. 2. As ashleedawg already mentioned in the comments, Format returns a string. Change your code to:

rng.Value = Date
rng.NumberFormat = "dd/mm/yyyy"

Upvotes: 3

Rory
Rory

Reputation: 34075

Don't format the input, format the cell (and you can just use Date):

rng.Value = Date
rng.NumberFormat = "dd/mm/yyyy"

Upvotes: 5

Related Questions