Formatting dates with TEXT issue

Copper Contributor

I'm having an issue with doing a text format conversion on dates. Specifically, if I take a month from a date (returns an integer between 1 and 12) and then do a TEXT command to format as month (Jan, Feb, etc) it always returns "Jan".

 

e.g. =TEXT(4,"mmm") returns "Jan". =TEXT(MONTH(TODAY()),"mmm") returns "Jan" (and it's March as I write this).

 

I've tried doing the format as "MMM" also, and get the same response. Returning it as a number (i.e. TEXT(4,"00") gives the expected value "04".

 

Am I missing something? Running Excel for Mac v16.23 (190309).

4 Replies
I noticed an extra closing parenthesis after TODAY. Revise your formula this way:
=TEXT(MONTH(TODAY()),
“mmm”)

@Twifoo that's just a typo in my posting, not in Excel.

@fmiddleton 

 

Typical error.

=TEXT(TODAY(),"MMM")

@fmiddleton , in addition to @Detlef Lewin 4 means Jan 04, 1900 (or Jan 04, 1904 for Mac), thus formula returns correct Jan result.