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:
Re: Converting Number to Month in Text Problem
28-Feb-08
On Thu, 28 Feb 2008 05:49:02 -0800, RyanH
<
[email protected]>
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:
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
Re: 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")
:
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
Re: Converting Number to Month in Text Problem
On Thu, 28 Feb 2008 05:49:02 -0800, RyanH
<
[email protected]>
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/tutorial...74-4eba5c821311/wpf-report-engine-part-1.aspx
