How to convert a month to a quarter ......

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

Try incorporating this
=MONTH(DATEVALUE(C1& "-2006"))

--
Don Guillett
SalesAid Software

"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

Here's another one:

C1 = mmm

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

Biff

"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

=IF(C1="","",ROUNDUP(MONTH(C1)/3,0))


"Epinn" wrote:

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

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

Thank you! I was thinking exactly the same thing i.e. ROUNDUP.

Do you have any comments on why month(A1&1) works? A1= Jan Details in my second post.

Epinn

"Teethless mama" wrote in message ...
=IF(C1="","",ROUNDUP(MONTH(C1)/3,0))


"Epinn" wrote:

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

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

Glad I am not seeing things.

These work:

=MONTH("12-Jan")
=MONTH("1-2008")
=MONTH("12-11")

These won't work:

=MONTH("2008Mar")
=MONTH("2008-1")

Both return #VALUE.

This reminds me of my thread in September on WEEKDAY ( ).

http://groups.google.ca/group/micros...2ccbaf 0eca06



WEEKDAY( ) and probably other date functions won't allow this kind of situation to happen. Shall we say MONTH( ) is an anomaly and we should apply my new discovery with caution i.e. if anyone wants to do so? I personally won't. Who knows if it still works with a patch/upgrade?



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

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

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