Change Date Format 1

[woodyinoz]

Hi all,

How do I do the following in VBA for Excel date formats?

I have a cell with the date value 25/11/2005

However, I want this cell to ignore the 25 and simply say Nov2005.

Can this be done and if so how?

Thanks in advance.

Woody.

 

[woodyinoz]

Hi again!

I was being stupid.... This is how I did it... Obviously!

    Columns("B:B").Select
    Selection.NumberFormat = "mmmmyyyy"

Cheers,

Woody

 

[woodyinoz]

Back again!

Ok... I've done that... But, now I want to add two characters to the date to make it something like July2005TW

Any ideas?

Cheers,

Woody

 

[GlennUK]

Where are these letters coming from? At the moment you have a normal date in the cell, with formatting to make it look the way you want ... the contents haven't changed. Do you want to change the contents of the cell, or just the way it looks, and would you consider using another cell to do your manipulations in?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[woodyinoz]

Glenn,

Yep, I would consider using a different cell.

At present the letters are held in a seperate field, for arguements sake, say field D2. The Date is held in field B2.

I want field D2 to combine the 2 fields (as text if necessary) to look like the follwing July2005AW.

I know that putting the following =TEXT(B2,"mmmmyyyyy"&D2) in cell E2 will give me what I want but how to I write this into a cell using VBA? When I put:

ActiveCell.Offset(0, 5).Formula = =TEXT(B2,"mmmmyyyyy"&D2)

I get an error due to the " around the date format.....

Any ideas?

 

[woodyinoz]

Sorry the code I put in is:

ActiveCell.Offset(0, 5).Formula = "=TEXT(B2,"mmmmyyyyy"&D2)

 

[GlennUK]

Try:
ActiveCell.Offset(0, 5).Formula = "=TEXT(B2,""mmmmyyyyy"")&D2"

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.