Can't get the Date to format correctly with VBA

[BradA]

Hello,

Each morning we run a report which I am in the process of automating. The one sticking point I have is something that has tripped me up several times in the past: One of the fields that is pulled into the daily report is a date field. It is displayed in Excel in mm/dd/yyyy format, but when you click in one of the date cells and look at the value that's actually in the cell it's in m/d/yyyy format. I want to take the date in mm/dd/yy format and do a couple things with it. First, I want to have it feed into cell B3 such that B3 displays "Data as of (whatever the date is in the report, in mm/dd/yy format)". I am trying to do this in the following way:

        Range("B3").Value = "Data as of " & Range("W6").Value
        Range("B3").NumberFormat = "mm/dd/yy"

where B3 is where I ultimately want it displayed and where W6 is where the date is currently residing...but I can't get it to display as anything other that m/d/yyyy format. Second, I want to use the date, this time in yyyymmdd format, in the book name. I think I can do that on my own, if I could get it to accept the date in yyyymmdd format.

Any help is greatly appreciated.

Thanks

 

[Rick Rothstein]

You are assigning a text string to B3, so it is not a date... hence, you cannot format it as a date. I think this single line of code may be what you are after...

Range("B3").Value = "Data as of " & Format(Range("W6").Value, "mm/dd/yy")

I think you can use the same structure as above for your second question, just use "yyyymmddd" for the second argument to the Format function call.

 

[Trebor76]

Hi BradA,

For your first query, try this:

Range("B3").Value = "Data as of " & Format(Range("W6").Value, "mm/dd/yyyy")

Not too sure about what you're actually after re your second query, but I'd say you'd be able to adapt the above to meet your needs.

HTH

Robert

 

[BradA]

Rick and Trebor,

Thanks to both of you for your responses. What an easy and wonderful solution. Much appreciated.