View Single Post
  #26   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips Bob Phillips is offline
external usenet poster
Posts: 1,726
Default How to convert a month to a quarter ......

Surely it would be awful, not useful.Whilst MONTH may work, the next step a
sane user might make is that it also works for YEAR? Here in the UK,
=YEAR("Jan10") would return 2010, presumably in the States it would return
2006. That does not sound useful IMO.



Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Roger Govier" wrote in message
Hi Biff

Roger, have you ever seen or used something like: MONTH("Jan10")?

No I hadn't until I saw Epinn's recent posting, which I noted with
That's a real "turn-up" and I can see how it could be useful in many


Roger Govier

"T. Valko" wrote in message
Type ABC in a cell.

The format of the cell is GENERAL but the value is text.

=TEXT(..............) does the same thing. The value returned is TEXT
but the cell format is still GENERAL. This seems to be what you're
stuck on:

=TEXT(..........) formats the cell as TEXT. It does not!

Roger, have you ever seen or used something like: MONTH("Jan10")?

This is new to me and I can see how this could make some things


"Roger Govier" wrote in message
Hi Epinn

Just stop worrying!!!!
Think of Text() as Format().
Accept that the Text() command returns Text, just as Left(123)
returns 1 as a Text value 1 and just as would the various
Mid() and Right() expressions. If you want a numeric return, you have
to have --Left(123) or Left(!23)*1
Accept that Excel tries to understand things which look like Dates as

Life will go on<bg


Roger Govier

"Epinn" wrote in message
Hi Roger,

Thanks for coming back.

This is my problem.

Using the TEXT() function, is not saying to Format the cell as
Text, it
is saying format the value I give you in the following way. <<

If I can see the job of TEXT( ) as formatting a value in a specific
way and forget about text and number format, things will be
straightforward and I'll be a lot happier.

not saying to Format the cell as Text <<

This is what I am having trouble with.

Let's use these formulae: =TEXT(123,0) =TEXT(123,"@")

If I key 123 to a blank cell (default General), I get 123 as a

But the above TEXT( ) give me 123 as text. ISTEXT( ) returns TRUE
and also 123 is left aligned. Based on these two formulae, I see
TEXT( ) converts 123 to text format which is same as formatting a
cell to TEXT and then key in 123. So I don't understand the comment
"not saying to Format the cell as Text." What have I missed?

Help text says: TEXT - converts a value to text in a specific number

The word "text" caught my attention. This is why I don't understand
Jan30 being converted to a serial number especially when I put
"Jan30" in double quotes. Last week when we discussed about data
type matching and SUMPRODUCT we emphasized that double quotes mean
*text*. So, I am lost.

I do understand what you said about Excel's intelligence of seeing
Jan30 as a date etc. etc. But I am very confused when I try to
connect your write-up to Help text to the 123 example above. There
is a missing link which I fail to see.

Do you see my problem now? Help! Much appreciated.


"Roger Govier" wrote in message
Hi Epinn
I'm still not understanding your problem.
If you format a cell as text, and enter Jan30 it appears as Jan30 in
cell as well as the formula bar, because the pre-formatting of the
tells Excel, don't do anything clever with this, just show the string

Formatting the cell to General doesn't change anything, - as you well
know -, unless you edit the cell (not changing anything) and press
Enter, whereupon the cell value changes to Jan-30 and the formula bar
shows the relevant Excel date.

Using the TEXT() function, is not saying to Format the cell as Text,
is saying format the value I give you in the following way. At this
point, whether you are saying use A1 (which contains Jan30) or use
"Jan30", Excel's "intelligence" kicks in and says well that looks
like a
date to me, so that's how I will treat it.

Using ,"@" or plain 0 as the argument, leaves it as is so you get
If you said Text(A1,"00-00-00") then you would get 03-87-47
If you said Text(A1,"dd mmmm yyyy") then you would get 30 January

If you entered Jan32 in cell A1, it would appear as Jan32.
Text(A1,"@") would return Jan32 because the intelligence would say,
isn't a valid date, so don't try and convert it to a serial number,
then display in the format required.


Roger Govier

"Epinn" wrote in message
Hi Roger,

Thank you for not giving up on me.

Please note that in my post I had two scenarios. I have no problem
understanding each one prior to posting. What you described is
1. I understand the entry being changed to serial number. This is
my problem.

My problem is equating TEXT( ) to the scenario(s). I have read Help
text many times regarding TEXT( ) and still haven't found the missing

In scenario 2, I would format a cell to TEXT (i.e. not using the
General) and key in Jan30, I get Jan30 period. If I change the
back to General afterwards, I still get Jan30, no change to serial
number. I expect the TEXT( ) to be equivalent to scenario 2
when I use @. I thought I was dealing with TEXT (Jan30) and nothing
else. I equate =TEXT("Jan30","@") to formatting a cell to TEXT prior
entering the date, based on my interpretation of what I read about
TEXT( ). Therefore, I was surprised that it returned a serial
I can accept the fact but it will be nice if I know where the
interpretation goes wrong. Do you understand where I got lost?

By the way, did you read the first part of this thread mentioning
my discovery on
=MONTH(A1&1) where A1 = mmm? Are you surprised (like Biff and Don)
it works without DATEVALUE?

May I remind you that I have a unique way of interpreting and testing
functions. Please bear with me. Much appreciated. I am glad that I
got my issues on custom format resolved. Guess you have read my
follow-up posts. I hope I can resolve this too.

Thank you for your patience.


"Roger Govier" wrote in message
Hi Epinn

If you enter JAN30 into a cell, then dependant upon your Regional
settings, Excel will interpret that as Jan-30 in the UK and it the
formula bar you will see 01/01/1930 and in the US it must see it as
30-Jan and presumably the formula bar shows 30/01/2006.

Since Excel determines for itself that entries of that type are
"presumed" to be dates, it automatically converts them to a serial
and uses a date format to display them. It does not surprise me that
Text function therefore returns the serial number of the date, albeit
a number in Text format (ISTEXT() for that cell returns True).

The result I see here in the UK is 10959 as a text value, not 38747
it is interpreted as 01/01/1930 as mentioned earlier.

Incidentally, Text("Jan30",0) will return the Text number just the
as using "@".


Roger Govier

"Epinn" wrote in message
(1) When I key in 1/1/2006 to a blank cell, the format is changed to
Date. When I change it back to General I can see the serial number.

(2) When I format a blank cell to Text, key in 1/1/2006 and change
format back to General, I don't get the serial number and I still see
1/1/2006 as Text.

So, I thought =TEXT("JAN30","@") would work like #2 above and was
surprised that a serial number was returned.

I am quite confused and am not sure what I have missed.


"Epinn" wrote in message

I think I have missed something.

But you get the same result as numeric with:

=DATEVALUE("Jan30") <<

According to Help text "DATEVALUE returns the serial number of the
represented by date_text. Use DATEVALUE to convert a date represented
text to a serial number." So, DATEVALUE is doing what it is supposed
do. No surprise there.

From what I have read, TEXT is supposed to "convert a value to text
in a
specific number format." I didn't expect =TEXT("JAN30","@") to
return a
serial number when I use "@". I think this is the norm which I
aware?? What have I missed. Please help.



"T. Valko" wrote in message
=TEXT("JAN30","@") returns 38747 (left aligned) Is this text?

Yes. The TEXT function returns TEXT unless you coerce it to numeric:


But you get the same result as numeric with:



"Epinn" wrote in message

You can't see how it does this though, it
evaluates straight through to the month number ......<<

Yes, I know. I also did "evaluate formula" before I posted. But try

=TEXT("JAN30","@") returns 38747 (left aligned) Is this text?
=MONTH(TEXT("JAN30","@")) returns 1.

Not sure how to interpret this.

This even works:

=MONTH("Jun1") = 6
=MONTH("Jun3500") = 6 <<

But this won't work.


I think this is as far as I want to take it. Anyone interested in
this with earlier version(s) or 2007?

What a difference a week makes! Last weekend I discovered how
is to match data type when it comes to SUMPRODUCT. This week is the
complete opposite. Excel is too temperamental or have I missed


"T. Valko" wrote in message
Any comments on why MONTH(A1&1) works?


That should not work but it does! You may have discovered something

If A1 = Jun (or June)

A1&1 is *definitely* a TEXT value: Jun1, but MONTH( ) seems to be
parse it as a date serial number. You can't see how it does this
evaluates straight through to the month number but it obviously must.

This even works:

=MONTH("Jun1") = 6
=MONTH("Jun3500") = 6

This does not work:

=MONTH("Jan") = #VALUE!
=MONTH(Jan) = #NAME?

I've never seen this before. I've never seen anyone use this in a
It appears to work. I hope I can remember this!


"Epinn" wrote in message
Thank you both for your response. I just felt that there had to be a
function to take care of text. Believe me, I did check the list of
functions but somehow I missed DATEVALUE. It was probably staring at
I was too sleepy and my eyes didn't open wide enough. On the bright
side, I
got a bonus by posting. It is interesting that by using CEILING
INT, I don't have to add 2.

All of the following formulae work. A1 = mmm

=IF(A1="","",CEILING(MONTH(DATEVALUE(A1&" 1"))/3,1))

but =IF(A1="","",CEILING(MONTH(DATEVALUE(A1&"-1-2006"))/3,1)) returns
I think d-mmm-yyyy is ISO date and mmm-d-yyyy is not recognized.

Now that I have found *the* date function, I realize that I don't
need it to have my formula working. The following formula works just
well for all 12 months.


See, I don't need to use DATEVALUE nor even any double quotes. Is it
"safe?" Oh, my God, I sound like you know who. I must have been
brainwashed. <G

This also works. =IF(A1="","",CEILING(MONTH(A1&" 1")/3,1))
Note: double quotes and space.

I don't quite understand. A1&1 or A1&" 1" are text and MONTH( ) has
problem with that?? According to Help text MONTH has a syntax like
MONTH(serial number). Wonder what is happening here?

If I remember correctly, last night I also tried A1&" 1" and it
#VALUE. But then "nothing" worked last night. Today everything
including the stuff that I don't expect to work.

Any comments on why MONTH(A1&1) works? Does it work for you too?



"Epinn" wrote in message

...... using date function(s)?

I have no problem converting a *date* to a quarter using the

=IF(ISBLANK(C1),"",INT((MONTH(C1)+2)/3)) where C1 = 1/1/2006.

A blank cell is month 1 because it is treated as 1/1/1900.
returns 1 when C1 is blank. I think we have discussed this before.
I feel better checking for blanks, so that I won't get quarter 1 for

If I key in a month as Jan, Feb ...... I can't use the above formula
other date functions. I can only think of using VLOOKUP, CHOOSE,
PivotTable (grouping) requires a date.

If I can convert Jan to 1 to 1/1/2006, then I can use the above
although VLOOKUP may be more direct.

Have I missed any date functions that may be able to do the job? Any