Using VBA to format Date

[eoinymc]

Hi all,

I am using VBA to format a date and it worked fine, but just decided to stop working it seems!

The code I have is this:

DateVal = Format("01-" & Ind.Cells(2, 4).Value, "mmmm")

The value of Ind.Calls(2, 4) will be either Jan/Feb/Mar/etc...

And I want it to be formatted as January..any ideas why this doesn't work or any other way of doing it?

I know I could just use an index/match or vlookup on a table that has Jan-January, etc...

Any help would be welcome.

cheers,

Eoin

 

[exceldevs]

Hi Eoin,

If you do a MsgBox Format("01-" & Ind.Cells(2, 4).Value, "mmmm"), what do you get?
How about MsgBox ind.Cells(2, 4).Value, what does it show?

 

[Jonmo1]

Your code works fine for me.

Are you sure Ind.Cells(2,4) is a TEXT string of Jan or Feb etc?
Or is it actually a date value with the cell formatted as mmm ?

If so, try
DateVal = Format(Ind.Cells(2, 4), "mmmm")

 

[eoinymc]

Everytime I do it, I get the following error:

Compile Error:

Wrong number of arguments or invalid property assignment.

But, I know it should work!!!! Arrrggghh!

I have literally copied what you sent me...!

And if I put anything in there, I get the same compile error...

There should only be 2 arguments for Format?! Shouldn't there?

Cheers,

Eoin

 

[Jonmo1]

Do you have another macro or function named "Format" ?

 

[eoinymc]

Yes.....................yes I do! AAAAAAAAAAAAARrrrrrggghhh!

Thanks for your help!

 

[Jonmo1]

Glad to help, thanks for the feedback.