View Single Post
  #23   Report Post  
Posted to microsoft.public.excel.newusers
Roger Govier Roger Govier is offline
external usenet poster
 
Posts: 2,886
Default How to convert a month to a quarter ......

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
interest.
That's a real "turn-up" and I can see how it could be useful in many
cases.

--
Regards

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
easier.

Biff

"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
Dates.

Life will go on<bg

--
Regards

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
number.

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
format.



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.



Epinn


"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
the
cell as well as the formula bar, because the pre-formatting of the
cell
tells Excel, don't do anything clever with this, just show the string
I
enter.

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,
it
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
38747.
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
2006

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


--
Regards

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
scenario
1. I understand the entry being changed to serial number. This is
not
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
piece.

In scenario 2, I would format a cell to TEXT (i.e. not using the
default
General) and key in Jan30, I get Jan30 period. If I change the
format
back to General afterwards, I still get Jan30, no change to serial
number. I expect the TEXT( ) to be equivalent to scenario 2
especially
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
to
entering the date, based on my interpretation of what I read about
TEXT( ). Therefore, I was surprised that it returned a serial
number.
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
about
my discovery on
=MONTH(A1&1) where A1 = mmm? Are you surprised (like Biff and Don)
that
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.

Epinn

"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
date
and uses a date format to display them. It does not surprise me that
the
Text function therefore returns the serial number of the date, albeit
as
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
as
it is interpreted as 01/01/1930 as mentioned earlier.

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

--
Regards

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.
No
problem.

(2) When I format a blank cell to Text, key in 1/1/2006 and change
the
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

"Epinn" wrote in message
...
Biff,

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
date
represented by date_text. Use DATEVALUE to convert a date represented
by
text to a serial number." So, DATEVALUE is doing what it is supposed
to
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
wasn't
aware?? What have I missed. Please help.

Thanks.

Epinn

"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:

=--TEXT("JAN30","@")
=TEXT("JAN30","@")+0
=TEXT("JAN30","@")*1

But you get the same result as numeric with:

=DATEVALUE("Jan30")

Biff

"Epinn" wrote in message
...
Biff,

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
this.

=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.

=MONTH("Jan12006")

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

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

Epinn

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


Hmmm............

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

If A1 = Jun (or June)

A1&1 is *definitely* a TEXT value: Jun1, but MONTH( ) seems to be
able
to
parse it as a date serial number. You can't see how it does this
though,
it
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
formula.
It appears to work. I hope I can remember this!

Biff

"Epinn" wrote in message
...
Thank you both for your response. I just felt that there had to be a
date
function to take care of text. Believe me, I did check the list of
date
functions but somehow I missed DATEVALUE. It was probably staring at
me
but
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
instead
of
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))
=IF(A1="","",CEILING(MONTH(DATEVALUE(A1&"-1"))/3,1))
=IF(A1="","",CEILING(MONTH(DATEVALUE(A1&"-2006"))/3,1))
=IF(A1="","",CEILING(MONTH(DATEVALUE("1-"&A1&"-2006"))/3,1))

but =IF(A1="","",CEILING(MONTH(DATEVALUE(A1&"-1-2006"))/3,1)) returns
#VALUE.
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
really
need it to have my formula working. The following formula works just
as
well for all 12 months.

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

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
no
problem with that?? According to Help text MONTH has a syntax like
this:
MONTH(serial number). Wonder what is happening here?

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

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

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

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

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

=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.
=month(C1)
returns 1 when C1 is blank. I think we have discussed this before.
Hence,
I feel better checking for blanks, so that I won't get quarter 1 for
a
blank
cell.

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

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

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

Thanks.

Epinn