Excel 2002 Date Formatting

S

skoladycz

Assuming the date entered is "01.27.2005", I want to format as "JAN.2005"
(not "Jan.2005") - is this possible?
 
D

Dave Peterson

Can you use a helper cell and show the results there?

=UPPER(TEXT(DATEVALUE(SUBSTITUTE(A1,".","/")),"mmm.yyyy"))

Worked ok with my USA date settings (mdy).

substitute() changed the dots to slashes.
datevalue() made it a real date
text() changed it to Jan.2005
and upper() changed the case.
 
Top