Converting Number to Month in Text Problem
 

I am having an issue with converting the month number to the month
abbreviation.

Below Should Return "Feb", but it is returning "Jan"
1.) ="Jobs Due In or Before "& TEXT(TODAY(NOW()),"mmm")

Below Should Return "Mar", but it is returning "Jan"
2.)="Jobs Due In "& TEXT(MONTH(TODAY())+1,"mmm")

3.)="Jobs Due After " & TEXT(MONTH(TODAY())+1,"mmm")

My computer time is set to Thursday, Feb. 27, 2008. Anybody have any ideas?

Thanks
Ryan

Converting Number to Month in Text Problem
 

Try these amendments:
="Jobs Due In or Before "& TEXT(TODAY(),"mmm")
="Jobs Due In "& TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),"mmm")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RyanH" wrote:
I am having an issue with converting the month number to the month
abbreviation.

Below Should Return "Feb", but it is returning "Jan"
1.) ="Jobs Due In or Before "& TEXT(TODAY(NOW()),"mmm")

Below Should Return "Mar", but it is returning "Jan"
2.)="Jobs Due In "& TEXT(MONTH(TODAY())+1,"mmm")

3.)="Jobs Due After " & TEXT(MONTH(TODAY())+1,"mmm")

My computer time is set to Thursday, Feb. 27, 2008. Anybody have any ideas?

Thanks
Ryan

Converting Number to Month in Text Problem
 

I'm surprised that you say that formula 1 returns "Jan" as in my case it
points out that there is an error in the formula. [You may wish to look at
Excel Help to remind yourself of the syntax of the TODAY() function.]

As for formulae 2 and 3, you'll again need to remind yourself (with Help) of
the syntax and operation of the functions you are using.
[As a hint, put the formula =MONTH(TODAY())+1 in a cell, and format the cell
firstly as General, then as Date, and then think what answer you would
expect if you put that date into the TEXT() function.]
--
David Biddulph

"RyanH" wrote in message
...
I am having an issue with converting the month number to the month
abbreviation.

Below Should Return "Feb", but it is returning "Jan"
1.) ="Jobs Due In or Before "& TEXT(TODAY(NOW()),"mmm")

Below Should Return "Mar", but it is returning "Jan"
2.)="Jobs Due In "& TEXT(MONTH(TODAY())+1,"mmm")

3.)="Jobs Due After " & TEXT(MONTH(TODAY())+1,"mmm")

My computer time is set to Thursday, Feb. 27, 2008. Anybody have any
ideas?

Thanks
Ryan

Converting Number to Month in Text Problem
 

On Thu, 28 Feb 2008 05:49:02 -0800, RyanH
wrote:

I am having an issue with converting the month number to the month
abbreviation.

Below Should Return "Feb", but it is returning "Jan"
1.) ="Jobs Due In or Before "& TEXT(TODAY(NOW()),"mmm")

Below Should Return "Mar", but it is returning "Jan"
2.)="Jobs Due In "& TEXT(MONTH(TODAY())+1,"mmm")

3.)="Jobs Due After " & TEXT(MONTH(TODAY())+1,"mmm")

My computer time is set to Thursday, Feb. 27, 2008. Anybody have any ideas?

Thanks
Ryan

If you are really using formula 1, it will return an error, because
TODAY(NOW()) is not valid.

Did you type these in? IT's always a better idea to copy the actual formula
and paste it in.

Your other problem is that you are not taking into account the fact that Excel
stores dates as serial numbers with 1 = 1 Jan 1900 (or 2 Jan 1904).

So when you execute MONTH(TODAY()) that will return a 2. 2, as a date,
represents 2 Jan 1900; so when you format to show just the month, it, naturally
enough, shows a Jan.

You need to format the date itself, not a derivation of the month of the date.

e.g.

=TEXT(TODAY(),"mmm")

By the way, this is a common mistake to make.
--ron

Month
 

I suspect the reason this is a "common" mistake is because Excel is not consistent. In order to return the day name from a date you first have to extract the day number, then format it eg

=TEXT(WEEKDAY(A1),"dddd")

so you might well assume (as I did) that to return the month name the formula would be

=TEXT(MONTH(A1),"mmmm")

rather than

=TEXT(A1,"mmmm")

Anyway, thanks for your help in solving my problem. It's a pity that Excel Help does not contain this sort of information.



Ron Rosenfeld wrote:

Converting Number to Month in Text Problem
28-Feb-08

On Thu, 28 Feb 2008 05:49:02 -0800, RyanH
wrote:


If you are really using formula 1, it will return an error, because
TODAY(NOW()) is not valid.

Did you type these in? IT's always a better idea to copy the actual formula
and paste it in.

Your other problem is that you are not taking into account the fact that Excel
stores dates as serial numbers with 1 = 1 Jan 1900 (or 2 Jan 1904).

So when you execute MONTH(TODAY()) that will return a 2. 2, as a date,
represents 2 Jan 1900; so when you format to show just the month, it, naturally
enough, shows a Jan.

You need to format the date itself, not a derivation of the month of the date.

e.g.

=TEXT(TODAY(),"mmm")

By the way, this is a common mistake to make.
--ron

Previous Posts In This Thread:

On Thursday, February 28, 2008 8:49 AM
Ryan wrote:

Converting Number to Month in Text Problem
I am having an issue with converting the month number to the month
abbreviation.

Below Should Return "Feb", but it is returning "Jan"
1.) ="Jobs Due In or Before "& TEXT(TODAY(NOW()),"mmm")

Below Should Return "Mar", but it is returning "Jan"
2.)="Jobs Due In "& TEXT(MONTH(TODAY())+1,"mmm")

3.)="Jobs Due After " & TEXT(MONTH(TODAY())+1,"mmm")

My computer time is set to Thursday, Feb. 27, 2008. Anybody have any ideas?

Thanks
Ryan

On Thursday, February 28, 2008 8:57 AM
demechani wrote:

Converting Number to Month in Text Problem
Try these amendments:
="Jobs Due In or Before "& TEXT(TODAY(),"mmm")
="Jobs Due In "& TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),"mmm")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RyanH" wrote:

On Thursday, February 28, 2008 9:12 AM
David Biddulph wrote:

I'm surprised that you say that formula 1 returns "Jan" as in my case it
I'm surprised that you say that formula 1 returns "Jan" as in my case it
points out that there is an error in the formula. [You may wish to look at
Excel Help to remind yourself of the syntax of the TODAY() function.]

As for formulae 2 and 3, you'll again need to remind yourself (with Help) of
the syntax and operation of the functions you are using.
[As a hint, put the formula =MONTH(TODAY())+1 in a cell, and format the cell
firstly as General, then as Date, and then think what answer you would
expect if you put that date into the TEXT() function.]
--
David Biddulph

"RyanH" wrote in message
...

On Thursday, February 28, 2008 9:45 AM
Ron Rosenfeld wrote:

Converting Number to Month in Text Problem
On Thu, 28 Feb 2008 05:49:02 -0800, RyanH
wrote:


If you are really using formula 1, it will return an error, because
TODAY(NOW()) is not valid.

Did you type these in? IT's always a better idea to copy the actual formula
and paste it in.

Your other problem is that you are not taking into account the fact that Excel
stores dates as serial numbers with 1 = 1 Jan 1900 (or 2 Jan 1904).

So when you execute MONTH(TODAY()) that will return a 2. 2, as a date,
represents 2 Jan 1900; so when you format to show just the month, it, naturally
enough, shows a Jan.

You need to format the date itself, not a derivation of the month of the date.

e.g.

=TEXT(TODAY(),"mmm")

By the way, this is a common mistake to make.
--ron


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 1
http://www.eggheadcafe.com/tutorials...ne-part-1.aspx

Month
 

I suspect the reason this is a "common" mistake is because Excel
is not consistent. In order to return the day name from a date
you first have to extract the day number, then format it eg

=TEXT(WEEKDAY(A1),"dddd")

The above is not correct... you will not always get the correct day name
using it. Try it out on today's date (3/30/2010)... it will return Monday as
an answer, not Tuesday. The way you would get the day name is the same way
you get the month name...

=TEXT(A1,"dddd")

Excel is consistent with this.

--
Rick (MVP - Excel)



"Christopher Moseley" wrote in message
...
I suspect the reason this is a "common" mistake is because Excel is not
consistent. In order to return the day name from a date you first have to
extract the day number, then format it eg

=TEXT(WEEKDAY(A1),"dddd")

so you might well assume (as I did) that to return the month name the
formula would be

=TEXT(MONTH(A1),"mmmm")

rather than

=TEXT(A1,"mmmm")

Anyway, thanks for your help in solving my problem. It's a pity that
Excel Help does not contain this sort of information.



Ron Rosenfeld wrote:

Converting Number to Month in Text Problem
28-Feb-08

On Thu, 28 Feb 2008 05:49:02 -0800, RyanH

wrote:


If you are really using formula 1, it will return an error, because
TODAY(NOW()) is not valid.

Did you type these in? IT's always a better idea to copy the actual
formula
and paste it in.

Your other problem is that you are not taking into account the fact that
Excel
stores dates as serial numbers with 1 = 1 Jan 1900 (or 2 Jan 1904).

So when you execute MONTH(TODAY()) that will return a 2. 2, as a date,
represents 2 Jan 1900; so when you format to show just the month, it,
naturally
enough, shows a Jan.

You need to format the date itself, not a derivation of the month of the
date.

e.g.

=TEXT(TODAY(),"mmm")

By the way, this is a common mistake to make.
--ron

Previous Posts In This Thread:

On Thursday, February 28, 2008 8:49 AM
Ryan wrote:

Converting Number to Month in Text Problem
I am having an issue with converting the month number to the month
abbreviation.

Below Should Return "Feb", but it is returning "Jan"
1.) ="Jobs Due In or Before "& TEXT(TODAY(NOW()),"mmm")

Below Should Return "Mar", but it is returning "Jan"
2.)="Jobs Due In "& TEXT(MONTH(TODAY())+1,"mmm")

3.)="Jobs Due After " & TEXT(MONTH(TODAY())+1,"mmm")

My computer time is set to Thursday, Feb. 27, 2008. Anybody have any
ideas?

Thanks
Ryan

On Thursday, February 28, 2008 8:57 AM
demechani wrote:

Converting Number to Month in Text Problem
Try these amendments:
="Jobs Due In or Before "& TEXT(TODAY(),"mmm")
="Jobs Due In "& TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),"mmm")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RyanH" wrote:

On Thursday, February 28, 2008 9:12 AM
David Biddulph wrote:

I'm surprised that you say that formula 1 returns "Jan" as in my case it
I'm surprised that you say that formula 1 returns "Jan" as in my case it
points out that there is an error in the formula. [You may wish to look
at
Excel Help to remind yourself of the syntax of the TODAY() function.]

As for formulae 2 and 3, you'll again need to remind yourself (with Help)
of
the syntax and operation of the functions you are using.
[As a hint, put the formula =MONTH(TODAY())+1 in a cell, and format the
cell
firstly as General, then as Date, and then think what answer you would
expect if you put that date into the TEXT() function.]
--
David Biddulph

"RyanH" wrote in message
...

On Thursday, February 28, 2008 9:45 AM
Ron Rosenfeld wrote:

Converting Number to Month in Text Problem
On Thu, 28 Feb 2008 05:49:02 -0800, RyanH

wrote:


If you are really using formula 1, it will return an error, because
TODAY(NOW()) is not valid.

Did you type these in? IT's always a better idea to copy the actual
formula
and paste it in.

Your other problem is that you are not taking into account the fact that
Excel
stores dates as serial numbers with 1 = 1 Jan 1900 (or 2 Jan 1904).

So when you execute MONTH(TODAY()) that will return a 2. 2, as a date,
represents 2 Jan 1900; so when you format to show just the month, it,
naturally
enough, shows a Jan.

You need to format the date itself, not a derivation of the month of the
date.

e.g.

=TEXT(TODAY(),"mmm")

By the way, this is a common mistake to make.
--ron


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 1
http://www.eggheadcafe.com/tutorials...ne-part-1.aspx