Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 26th 06, 08:46 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default How to convert a month to a quarter ......

What's L & G ?


Biff

"Don Guillett" wrote in message
...
I just tested this on xl2002 and it worked. Strange but good. I am
referring this thread to the L & G lists

--
Don Guillett
SalesAid Software

"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















  #12   Report Post  
Old November 26th 06, 08:53 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default How to convert a month to a quarter ......

=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












  #13   Report Post  
Old November 26th 06, 09:48 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default How to convert a month to a quarter ......

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













  #14   Report Post  
Old November 26th 06, 10:12 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default How to convert a month to a quarter ......

(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














  #15   Report Post  
Old November 26th 06, 10:27 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default How to convert a month to a quarter ......

Another excel list I belong to

--
Don Guillett
SalesAid Software

"T. Valko" wrote in message
...
What's L & G ?


Biff

"Don Guillett" wrote in message
...
I just tested this on xl2002 and it worked. Strange but good. I am
referring this thread to the L & G lists

--
Don Guillett
SalesAid Software

"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


















  #16   Report Post  
Old November 27th 06, 09:56 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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















  #17   Report Post  
Old November 27th 06, 06:22 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default How to convert a month to a quarter ......

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
















  #18   Report Post  
Old November 27th 06, 07:02 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default How to convert a month to a quarter ......

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

















  #19   Report Post  
Old November 27th 06, 10:42 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default How to convert a month to a quarter ......

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


















  #20   Report Post  
Old November 27th 06, 10:59 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default How to convert a month to a quarter ......

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





















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert 2 digit month to 4 digit years and months BB Excel Worksheet Functions 2 September 17th 06 09:33 PM
How to create a rolling 6 month average? anley Excel Discussion (Misc queries) 3 August 14th 06 12:41 PM
convert number to month and year in formula Soth Excel Worksheet Functions 3 July 19th 06 08:32 PM
maturity schedule fixed inc securities by month, quarter and year maturity schedule Excel Worksheet Functions 1 April 1st 06 12:09 AM
Convert financial week into corresponding month RichHoughton Excel Discussion (Misc queries) 8 May 24th 05 05:40 PM


All times are GMT +1. The time now is 11:26 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017