Calculating MONTHNAME

users
#1

Ok, this is really driving me nuts...

Given:

=MONTH(NOW())
results in the number of the current month (1, for January)

I want to simply translate this to the monthname, so I used:

=TEXT(MONTH(NOW()),"MMM")
this almost gives me what I want, but it results in "Dec', instead of
"Jan" - WTF???

Changing it to:

=TEXT(MONTH(NOW())+1,"MMM")
gives me "Jan", which is what I want.

Why do I have to add a '1' to it???

#2

Bug???

#3

If it is its been there a long time, because I first encountered this a
loooong time ago (I finally decided to ask about it).

Would appreciate someone confirming I'm not just crazy, and it should
work as I'm expecting.

#4

13.01.2017 u 21:17, Tanstaafl je napisao/la:

If it is its been there a long time, because I first encountered this a
loooong time ago (I finally decided to ask about it).

Would appreciate someone confirming I'm not just crazy, and it should
work as I'm expecting.

Bug???

Ok, this is really driving me nuts...

Given:

=MONTH(NOW())
results in the number of the current month (1, for January)

I want to simply translate this to the monthname, so I used:

=TEXT(MONTH(NOW()),"MMM")
this almost gives me what I want, but it results in "Dec', instead of
"Jan" - WTF???

Changing it to:

=TEXT(MONTH(NOW())+1,"MMM")
gives me "Jan", which is what I want.

And

=TEXT(MONTH(NOW()) + 3; "MMM")

gives january

#5

Try =text(now(),"MMM")

Tony.

#6

13.01.2017 u 21:28, Kruno je napisao/la:

13.01.2017 u 21:17, Tanstaafl je napisao/la:

If it is its been there a long time, because I first encountered this a
loooong time ago (I finally decided to ask about it).

Would appreciate someone confirming I'm not just crazy, and it should
work as I'm expecting.

Bug???

Ok, this is really driving me nuts...

Given:

=MONTH(NOW())
results in the number of the current month (1, for January)

I want to simply translate this to the monthname, so I used:

=TEXT(MONTH(NOW()),"MMM")
this almost gives me what I want, but it results in "Dec', instead of
"Jan" - WTF???

Changing it to:

=TEXT(MONTH(NOW())+1,"MMM")
gives me "Jan", which is what I want.

And

=TEXT(MONTH(NOW()) + 3; "MMM")

gives january

Oops, I got it, it does days, sorry for trolling :frowning:

#7

Hello,

Day "1" is December 31, 1899 (at least, this is what I get when I
display "1" with a YYYY-MM-DD format): this is why
'TEXT(MONTH(NOW()),"MMM")"' gives "December". To get something like
this to come out consistently, I always use a formula like this:

=TEXT(DATE(1900;MONTH(NOW());1);"MMM") [basically asking for the month
of Jan 1, 1900]

In this case, the formula is not influenced by the date encoding scheme
and will yield the desired result.

I hope this helps.

Rémy Gauthier.

> If it is its been there a long time, because I first encountered this

a

loooong time ago (I finally decided to ask about it).

Would appreciate someone confirming I'm not just crazy, and it should
work as I'm expecting.

> Bug???
>
> > Ok, this is really driving me nuts...
> >
> > Given:
> >
> > =MONTH(NOW())
> > results in the number of the current month (1, for January)
> >
> > I want to simply translate this to the monthname, so I used:
> >
> > =TEXT(MONTH(NOW()),"MMM")
> > > > > this almost gives me what I want, but it results in "Dec',

instead of

#8

Think the issue is that with "MMM", it treats the number as a date value
instead of as being a month, so that 1 would result in December, and 2-12
result in January.

So, the text option with month() doesn't seem to work at all, since the values
don't match. Here is using choose to get what I think. Longer formula, but it
seems to work, and would allow you do choose what exact format you want
for each month.

Number
'=CHOOSE(MONTH(A4),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

                                                                   1
Dec

                                                                   2
Jan

                                                                   3
Jan

                                                                   4
Jan

                                                                   5
Jan

                                                                   6
Jan

                                                                   7
Jan

                                                                   8
Jan

                                                                   9
Jan

                                                                  10
Jan

                                                                  11
Jan

                                                                  12
Jan

Dates
'=CHOOSE(MONTH(A4),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

                                                            01/01/17
Jan

                                                            02/02/17
Feb

                                                            03/06/17
Mar

                                                            04/07/17
Apr

                                                            05/09/17
May

                                                            06/10/17
Jun

                                                            07/12/17
Jul

                                                            08/13/17
Aug

                                                            09/14/17
Sep

                                                            10/16/17
Oct

                                                            11/17/17
Nov

                                                            12/19/17
Dec

#9

14.01.2017 u 08:55, Michael D. Setzer II je napisao/la:

Think the issue is that with "MMM", it treats the number as a date value
instead of as being a month, so that 1 would result in December, and 2-12
result in January.

So, the text option with month() doesn't seem to work at all, since the values
don't match. Here is using choose to get what I think. Longer formula, but it
seems to work, and would allow you do choose what exact format you want
for each month.

We all tried to to pass integer value (result of MONTH()) to a TEXT() function which interpreted integer as number of days. Passing _date_ value does the job.

In documentation, MONTH() returns integer, and integer can not be treated as date data type, it just a plain number.

Tony's suggestion is correct - it passes date value (data type) to TEXT() function which calculates number of days passed since 1899-31-12 till now and returns correct result formated as "MMM".

So

= TEXT(NOW(), "MMM")

since OP wants it with NOW(), and Remy's suggestion seams longish now.

We were using TEXT() function wrong.

#10

Ok, this is really driving me nuts...

Given:
=MONTH(NOW())
results in the number of the current month (1, for January) I want to simply translate this to the monthname, so I used:
=TEXT(MONTH(NOW()),"MMM")
this almost gives me what I want, but it results in "Dec", instead of "Jan" - W[hat is happening here]?

You are guilty of overkill. The TEXT() function with format "MMM" will extract the abbreviated month name from a *date*, so you don't need to use the MONTH() function to do that bit first. As someone has said, all you need is
=TEXT(NOW(),"MMM")

If you use the MONTH() function first, you do indeed get the number 1 for (the current) January, of course, but the TEXT() expression then interprets this 1 as a date, not a month number. Date/time values are expressed as days and fractions of a day, of course, and the default date origin is 30 December 1899, so 1 corresponds to midnight at the beginning of 31 December 1899. That is why you are getting "Dec" - nothing to do with December 2016.

Changing it to:
=TEXT(MONTH(NOW())+1,"MMM")
gives me "Jan", which is what I want. Why do I have to add a '1' to it?

You don't, and you shouldn't. Adding 1 gives the result 2, which corresponds to midnight at the start of 1 January 1900, so you will indeed see "Jan", but this is something of a red herring. If you add 2 or 3 or 4 and so on, you will continue to see "Jan"; only when you add 32 and the represented date spills over to 1 February 1900 will you see "Feb".

Bug?

Really?

If it is its been there a long time, because I first encountered this a loooong time ago (I finally decided to ask about it). Would appreciate someone confirming I'm not just crazy, and it should work as I'm expecting.

No, it should not work as you expected - but I have no evidence that you are crazy.

I trust this helps.

Brian Barker

#11

I've already posted the solution. Just remove the call to month and do.

=text (now (),"MMM")

Regards,
Tony.

#12

Worked perfectly, thanks Tony!