Converting string

[KGee]

I have string values representing the month of a transaction in two digit format. I need the values in MMM format and am currently using a case statement to convert them. I wanted to know if there was a way to use the format function to accomplish the same thing with one line of code. I've tried the line below but it always returns JAN no matter which two digit string is present.

newMonth = UCase(Format(oldMonth, "MMM"))

I want to go from this:
01
02
03

to this:
JAN
FEB
MAR

 

[mrMozambique]

Not sure about the VBA for this conversion, but can you just do it in Excel with a quick VLOOKUP? You can easily make a table with the MM and MMM equivalents and then just lookup your value in the table and return the second column. Then paste the values into your original column and delete the vlookup column.

 

[KGee]

Unfortunately this runs as part of a scripted batch job so I need to do it in VBA. I have something that works, I just wanted to know if it was possible to replace the case statement with a shorter piece of code.

Thanks

 

[delaneyjm]

Perhaps this?

newMonth = UCase(Format(DateSerial(Year(Date), CInt(oldMonth), 1), "MMM"))

 

[KGee]

I knew I needed to convert it but couldn't figure out which function. This works great.

Cheers!