View Single Post
  #16   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 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