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

Hi Epinn,

To convert a month to a quarter using date functions, you can use the following formula:

In this formula, C1 is the cell containing the month (e.g. "Jan", "Feb", etc.). The DATEVALUE function is used to convert the month to a date by concatenating it with "1-" and "-2000" to create a date in the format "1-Jan-2000". The MONTH function then extracts the month number from this date, which is then used in the same formula you provided to calculate the quarter.

Here are the steps to use this formula:

1. Enter the formula in a cell where you want the quarter to be displayed.
2. Replace "C1" in the formula with the cell containing the month.
3. Press Enter to calculate the quarter.

This formula should work for any month entered as text (e.g. "Jan", "Feb", etc.). Let me know if you have any questions or if there's anything else I can help with.

Best regards,
[Your Name]

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

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

=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

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

(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

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

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

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

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

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

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

Type ABC in a cell.

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

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

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

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

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

Biff

"Roger Govier" wrote in message
...
Hi Epinn

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

Life will go on<bg

--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thanks for coming back.

This is my problem.

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



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



not saying to Format the cell as Text <<



This is what I am having trouble with.



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



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

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



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



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



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



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



Epinn


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

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

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

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

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


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thank you for not giving up on me.

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

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

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

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

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

Thank you for your patience.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

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

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

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

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

--
Regards

Roger Govier


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

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

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

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

Epinn

"Epinn" wrote in message
...
Biff,

I think I have missed something.

But you get the same result as numeric with:

=DATEVALUE("Jan30") <<

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

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

Thanks.

Epinn

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

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

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

But you get the same result as numeric with:

=DATEVALUE("Jan30")

Biff

"Epinn" wrote in message
...
Biff,

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

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

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

Not sure how to interpret this.

This even works:

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

But this won't work.

=MONTH("Jan12006")

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

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

Epinn

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

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

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

If A1 = Jun (or June)

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

This even works:

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

This does not work:

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

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

Biff

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

All of the following formulae work. A1 = mmm

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

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

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

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

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

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

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

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

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

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

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

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

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

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

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

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

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

Thanks.

Epinn

Hi Biff

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

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

--
Regards

Roger Govier


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

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

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

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

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

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

Biff

"Roger Govier" wrote in message
...
Hi Epinn

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

Life will go on<bg

--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thanks for coming back.

This is my problem.

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



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



not saying to Format the cell as Text <<



This is what I am having trouble with.



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



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

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



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



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



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



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



Epinn


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

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

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

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

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


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thank you for not giving up on me.

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

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

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

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

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

Thank you for your patience.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

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

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

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

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

--
Regards

Roger Govier


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

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

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

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

Epinn

"Epinn" wrote in message
...
Biff,

I think I have missed something.

But you get the same result as numeric with:

=DATEVALUE("Jan30") <<

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

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

Thanks.

Epinn

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

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

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

But you get the same result as numeric with:

=DATEVALUE("Jan30")

Biff

"Epinn" wrote in message
...
Biff,

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

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

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

Not sure how to interpret this.

This even works:

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

But this won't work.

=MONTH("Jan12006")

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

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

Epinn

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

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

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

If A1 = Jun (or June)

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

This even works:

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

This does not work:

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

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

Biff

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

All of the following formulae work. A1 = mmm

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

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

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

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

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

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

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

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

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

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

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

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

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

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

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

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

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

Thanks.

Epinn

Thank you both.

Believe it or not, I don't like this any more than you do. Let me clarify, my goal is not trying to make sense of Excel although it may seem so. My goal is to make sure that I haven't misunderstood anything. If that's how Excel behaves, I'll accept it.

The following is what I take away:

TEXT converts a value to a specified format and returns text data type. But it does not format a cell as Text. If the value looks like a date, @ and 0 converts it to a serial number.

One article says @ is a text placeholder which doesn't mean much to me. Can you tell me a bit more please? @ = as is, status quo??? We can use @ with custom format as well. Is it the same?

If I apply custom format @*? to A1 and then key in honey, I get honey??? in A1. In this case, @ means more than as is.

If I write =TEXT(a1,"@*?") where A1 is honey, I get honey and no question mark. What is the difference of @ under custom format and TEXT( )?

Please enlighten me. Thank you. I'll do my best to give everybody including myself a break.

Epinn

"Roger Govier" wrote in message ...
Hi Epinn

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

Life will go on<bg

--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thanks for coming back.

This is my problem.

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



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



not saying to Format the cell as Text <<



This is what I am having trouble with.



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



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

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



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



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



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



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



Epinn


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

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

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

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

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


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thank you for not giving up on me.

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

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

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

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

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

Thank you for your patience.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

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

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

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

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

--
Regards

Roger Govier


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

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

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

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

Epinn

"Epinn" wrote in message
...
Biff,

I think I have missed something.

But you get the same result as numeric with:

=DATEVALUE("Jan30") <<

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

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

Thanks.

Epinn

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

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

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

But you get the same result as numeric with:

=DATEVALUE("Jan30")

Biff

"Epinn" wrote in message
...
Biff,

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

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

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

Not sure how to interpret this.

This even works:

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

But this won't work.

=MONTH("Jan12006")

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

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

Epinn

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

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

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

If A1 = Jun (or June)

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

This even works:

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

This does not work:

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

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

Biff

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

All of the following formulae work. A1 = mmm

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

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

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

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

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

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

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

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

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

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

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

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

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

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

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

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

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

Thanks.

Epinn

Hi Epinn

"@" is not a format, it is used just as a placeholder when formatting a
cell through CellFormat.
As far as it's use with formatting using Text() is concerned, I do not
see that it really has a place and is essentially ignored.

What is a placeholder?
If you wanted a cell to always contain the text "sq cm." or "cm²" you
could enter that in a cell so that any values you entered would also
display that text, but the cell would still contain just a numeric value
that can be used in further calculation.
Without the @ placeholder, Excel does not know whether you want 123 sq
cm. as a result, or sq cm. 123.

Entering the text without a placeholder, Excel gives up and just shows
the text you have entered without any numeric value (even though that
value is still actually held within that cell and can be used, the
treatment is just like the special formatting case of ;;; which shows
blank, even though values are contained within the cell).

When you enter @ "sq cm" Excel knows that the text should follow any
number and the result would be 123 sq cm.
When you enter "sq cm." @ Excel knows the text should follow any numeric
value and the result would be sq cm. 123

That, as far as I am concerned is the sole purpose of "@". It is a
placeholder in Custom formatting, and has no role whatsoever under
Text().

Your *? giving Honey????? well the "*" is the symbol to give repeating
text to fill the remainder of the cell width, using the character that
follows the "*". It is very clever, and as you widen the column, it will
add further characters to pad out the extra space.

I have never tried to use it with Text before, but presumably it is not
supported in this context. One of life's mysteries<bg
You would need to use
=A1&REPT("?,X-LEN(A1))
where X was the cell width. Setting X to 8 seems to give the same type
of fill with a standard width column, but it is not "intelligent" like
the *? formatting, and does not expand as you increase column width,
unless you change the value of X. You could achieve this with something
like
=A1&REPT("?",CELL("width",B1)-LEN(A1))
assuming the formula was placed in B1.

Epinn, this is the total extent of my knowledge. There is nothing else I
can say to help you. If you are still concerned then someone else is
going to have to resolve those issues for you - I cannot.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Thank you both.

Believe it or not, I don't like this any more than you do. Let me
clarify, my goal is not trying to make sense of Excel although it may
seem so. My goal is to make sure that I haven't misunderstood anything.
If that's how Excel behaves, I'll accept it.

The following is what I take away:

TEXT converts a value to a specified format and returns text data type.
But it does not format a cell as Text. If the value looks like a date,
@ and 0 converts it to a serial number.

One article says @ is a text placeholder which doesn't mean much to me.
Can you tell me a bit more please? @ = as is, status quo??? We can use
@ with custom format as well. Is it the same?

If I apply custom format @*? to A1 and then key in honey, I get honey???
in A1. In this case, @ means more than as is.

If I write =TEXT(a1,"@*?") where A1 is honey, I get honey and no
question mark. What is the difference of @ under custom format and
TEXT( )?

Please enlighten me. Thank you. I'll do my best to give everybody
including myself a break.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

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

Life will go on<bg

--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thanks for coming back.

This is my problem.

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



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



not saying to Format the cell as Text <<



This is what I am having trouble with.



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



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

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



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



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



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



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



Epinn


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

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

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

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

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


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thank you for not giving up on me.

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

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

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

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

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

Thank you for your patience.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

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

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

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

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

--
Regards

Roger Govier


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

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

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

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

Epinn

"Epinn" wrote in message
...
Biff,

I think I have missed something.

But you get the same result as numeric with:

=DATEVALUE("Jan30") <<

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

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

Thanks.

Epinn

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

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

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

But you get the same result as numeric with:

=DATEVALUE("Jan30")

Biff

"Epinn" wrote in message
...
Biff,

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

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

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

Not sure how to interpret this.

This even works:

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

But this won't work.

=MONTH("Jan12006")

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

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

Epinn

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

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

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

If A1 = Jun (or June)

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

This even works:

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

This does not work:

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

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

Biff

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

All of the following formulae work. A1 = mmm

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

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

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

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

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

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

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

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

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

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

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

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

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

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

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

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

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

Thanks.

Epinn

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

--

HTH

Bob Phillips

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

"Roger Govier" wrote in message
...
Hi Biff

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

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

--
Regards

Roger Govier


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

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

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

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

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

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

Biff

"Roger Govier" wrote in message
...
Hi Epinn

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

Life will go on<bg

--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thanks for coming back.

This is my problem.

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



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



not saying to Format the cell as Text <<



This is what I am having trouble with.



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



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

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



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



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



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



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



Epinn


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

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

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

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

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


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thank you for not giving up on me.

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

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

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

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

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

Thank you for your patience.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

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

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

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

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

--
Regards

Roger Govier


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

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

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

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

Epinn

"Epinn" wrote in message
...
Biff,

I think I have missed something.

But you get the same result as numeric with:

=DATEVALUE("Jan30") <<

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

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

Thanks.

Epinn

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

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

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

But you get the same result as numeric with:

=DATEVALUE("Jan30")

Biff

"Epinn" wrote in message
...
Biff,

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

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

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

Not sure how to interpret this.

This even works:

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

But this won't work.

=MONTH("Jan12006")

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

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

Epinn

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

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

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

If A1 = Jun (or June)

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

This even works:

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

This does not work:

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

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

Biff

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

All of the following formulae work. A1 = mmm

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

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

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

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

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

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

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

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

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

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

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

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

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

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

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

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

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

Thanks.

Epinn

Bob,

I thought of YEAR the other day but didn't test it. As you have expected, YEAR returns 2006 and DAY works. I personally don't feel safe using MONTH either. In case you missed my previous post, this is what I said:

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? <<

Roger,

Don't worry. I did say I would give everybody a break. I thought of starting a new thread for my last question ...... but you have to understand that you are good in explaining things. I really have to take a break now cause I have other priorities. Feel free to jump for joy! <G Thanks for your help.

Epinn

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

--

HTH

Bob Phillips

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

"Roger Govier" wrote in message
...
Hi Biff

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

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

--
Regards

Roger Govier


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

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

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

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

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

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

Biff

"Roger Govier" wrote in message
...
Hi Epinn

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

Life will go on<bg

--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thanks for coming back.

This is my problem.

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



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



not saying to Format the cell as Text <<



This is what I am having trouble with.



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



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

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



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



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



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



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



Epinn


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

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

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

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

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


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thank you for not giving up on me.

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

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

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

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

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

Thank you for your patience.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

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

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

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

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

--
Regards

Roger Govier


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

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

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

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

Epinn

"Epinn" wrote in message
...
Biff,

I think I have missed something.

But you get the same result as numeric with:

=DATEVALUE("Jan30") <<

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

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

Thanks.

Epinn

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

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

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

But you get the same result as numeric with:

=DATEVALUE("Jan30")

Biff

"Epinn" wrote in message
...
Biff,

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

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

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

Not sure how to interpret this.

This even works:

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

But this won't work.

=MONTH("Jan12006")

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

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

Epinn

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

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

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

If A1 = Jun (or June)

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

This even works:

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

This does not work:

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

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

Biff

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

All of the following formulae work. A1 = mmm

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

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

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

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

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

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

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

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

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

Thanks.

Epinn

"Epinn" wrote in message
...
Hi,

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

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

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

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

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

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

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

Thanks.

Epinn