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