Date formatting

[Guest]

Hello
Cell A1 7/1/2007
Cell A2 =month(a1) and returns 7 if I format as "MM" it returns 01. How do
I get this to return 07.
Thanks!

 

[Guest]

=TEXT(A1,"mm")

 

[jamison.folland]

Hi Sandy,

I used to run in to this as well. Because A2 has a formula to return
month from A1, your value is 7. Since you then are formatting as
month, it is in essence taking 7 as a date, which is 7 January 1900.
That is why you get 01 (if you had set format as "MMM" it would say
"Jan").

To fix this in cell A2, simply make A2=A1, and then set format as
"MMM". You will then get "Jul" in that cell.

Cheers,
Jamison