“dd/mm/yyyy” date format in excel through vba

Question

I am trying to write a date in "dd/mm/yyyy" format in excel sheet thru excel-vba. I achieved it using Cells(1, 1).Value = Format(StartDate, "dd/mm/yyyy"). and my cell value comes out to be 30/04/2014, which is good.....

But there is some weird problem... Since I have to iterate the startDate for whole month by everytime adding it by 1, so the next value comes out to be 1/5/2014 instead of 01/05/2014 until 12th of each month 12/5/2014 and from 13th, the month is again changing to two digits 13/05/2014....

I want it all in two digits as I have to again search for these dates using Range.Find method, in which I am passing value with "dd/mm/yyyy" fromat.

Answer 1

I got it

Cells(1, 1).Value = StartDate
Cells(1, 1).NumberFormat = "dd/mm/yyyy"

Basically, I need to set the cell format, instead of setting the date.

Answer 2

Your issue is with attempting to change your month by adding 1. 1 in date serials in Excel is equal to 1 day. Try changing your month by using the following:

NewDate = Format(DateAdd("m",1,StartDate),"dd/mm/yyyy")