Formatting dates with TEXT issue

New 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:

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




Typical error.


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