 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006How to convert a month to a quarter ......
Hi, ...... using date function(s)? I have no problem converting a *date* to a quarter using the following formula. =IF(ISBLANK(C1),"",INT((MONTH(C1)+2)/3)) where C1 = 1/1/2006. A blank cell is month 1 because it is treated as 1/1/1900. =month(C1) returns 1 when C1 is blank. I think we have discussed this before. Hence, I feel better checking for blanks, so that I won't get quarter 1 for a blank cell. If I key in a month as Jan, Feb ...... I can't use the above formula or any other date functions. I can only think of using VLOOKUP, CHOOSE, etc., even PivotTable (grouping) requires a date. If I can convert Jan to 1 to 1/1/2006, then I can use the above formula although VLOOKUP may be more direct. Have I missed any date functions that may be able to do the job? Any ideas? Thanks. Epinn Try incorporating this =MONTH(DATEVALUE(C1& "-2006"))  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com 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 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)) > Hi, > [quoted text clipped - 15 lines] > > 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 =IF(C1="","",ROUNDUP(MONTH(C1)/3,0)) "Epinn" wrote: > Hi, > [quoted text clipped - 15 lines] > > 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. 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 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" <someone@example.com.NO_SPAM> wrote in message news:#fSW#JNEHHA.4620@TK2MSFTNGP04.phx.gbl... 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/microsoft.public.excel.newusers/browse_frm/thread/ 2ecc0597d92592ee/8ec92ccbaf0eca06?lnk=st&q=&rnum=1&hl=en#8ec92ccbaf0eca06 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 >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 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 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 >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 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 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 SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com 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 >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" <someone@example.com.NO_SPAM> wrote in message news:%23MlU$1QEHHA.3396@TK2MSFTNGP02.phx.gbl... 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" <someone@example.com.NO_SPAM> wrote in message news:#fSW#JNEHHA.4620@TK2MSFTNGP04.phx.gbl... 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 >I just tested this on xl2002 and it worked. Strange but good. I am >referring this thread to the L & G lists [quoted text clipped - 142 lines] > > Epinn Another excel list I belong to SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > What's L & G ? > [quoted text clipped - 149 lines] >> >> 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 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 >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" <someone@example.com.NO_SPAM> wrote in message news:%23MlU$1QEHHA.3396@TK2MSFTNGP02.phx.gbl... 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" <someone@example.com.NO_SPAM> wrote in message news:#fSW#JNEHHA.4620@TK2MSFTNGP04.phx.gbl... 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 >=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 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" <biffinpitt@comcast.net> wrote in message news:eVFv0XREHHA.4048@TK2MSFTNGP02.phx.gbl... >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 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 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. 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" <biffinpitt@comcast.net> wrote in message news:e22pHSZEHHA.4668@TK2MSFTNGP03.phx.gbl... >=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" <someone@example.com.NO_SPAM> wrote in message news:%23XfmIMUEHHA.4508@TK2MSFTNGP02.phx.gbl... 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 >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" <someone@example.com.NO_SPAM> wrote in message news:%23MlU$1QEHHA.3396@TK2MSFTNGP02.phx.gbl... 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" <someone@example.com.NO_SPAM> wrote in message news:#fSW#JNEHHA.4620@TK2MSFTNGP04.phx.gbl... 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 "@". SignatureRegards Roger Govier (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" <someone@example.com.NO_SPAM> wrote in message news:OHHoDxZEHHA.992@TK2MSFTNGP03.phx.gbl... 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 >=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" <someone@example.com.NO_SPAM> wrote in message news:%23XfmIMUEHHA.4508@TK2MSFTNGP02.phx.gbl... 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 >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" <someone@example.com.NO_SPAM> wrote in message news:%23MlU$1QEHHA.3396@TK2MSFTNGP02.phx.gbl... 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" <someone@example.com.NO_SPAM> wrote in message news:#fSW#JNEHHA.4620@TK2MSFTNGP04.phx.gbl... 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 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 "@". SignatureRegards Roger Govier (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 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 >=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 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 >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 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 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. SignatureRegards Roger Govier 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 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 "@". SignatureRegards Roger Govier "Epinn" <someone@example.com.NO_SPAM> wrote in message news:O5Z8h%23ZEHHA.348@TK2MSFTNGP06.phx.gbl... (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" <someone@example.com.NO_SPAM> wrote in message news:OHHoDxZEHHA.992@TK2MSFTNGP03.phx.gbl... 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 >=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" <someone@example.com.NO_SPAM> wrote in message news:%23XfmIMUEHHA.4508@TK2MSFTNGP02.phx.gbl... 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 >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" <someone@example.com.NO_SPAM> wrote in message news:%23MlU$1QEHHA.3396@TK2MSFTNGP02.phx.gbl... 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" <someone@example.com.NO_SPAM> wrote in message news:#fSW#JNEHHA.4620@TK2MSFTNGP04.phx.gbl... 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 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. SignatureRegards Roger Govier 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" <roger@technologyNOSPAM4u.co.uk> wrote in message news:OKGkxHgEHHA.3820@TK2MSFTNGP02.phx.gbl... 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 "@". SignatureRegards Roger Govier (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 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 >=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 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 >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 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 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> SignatureRegards Roger Govier 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 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. SignatureRegards Roger Govier "Epinn" <someone@example.com.NO_SPAM> wrote in message news:eRtMoikEHHA.4832@TK2MSFTNGP06.phx.gbl... 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 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 "@". SignatureRegards Roger Govier "Epinn" <someone@example.com.NO_SPAM> wrote in message news:O5Z8h%23ZEHHA.348@TK2MSFTNGP06.phx.gbl... (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" <someone@example.com.NO_SPAM> wrote in message news:OHHoDxZEHHA.992@TK2MSFTNGP03.phx.gbl... 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 >=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" <someone@example.com.NO_SPAM> wrote in message news:%23XfmIMUEHHA.4508@TK2MSFTNGP02.phx.gbl... 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 >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" <someone@example.com.NO_SPAM> wrote in message news:%23MlU$1QEHHA.3396@TK2MSFTNGP02.phx.gbl... 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" <someone@example.com.NO_SPAM> wrote in message news:#fSW#JNEHHA.4620@TK2MSFTNGP04.phx.gbl... 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 > Hi Epinn > [quoted text clipped - 283 lines] > > 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. SignatureRegards Roger Govier > Type ABC in a cell. > [quoted text clipped - 341 lines] >> >> 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) > Hi Biff > [quoted text clipped - 444 lines] > >> > >> 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 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) > Hi Biff > [quoted text clipped - 436 lines] > >> > >> 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 |
Enable EMail Alerts
Start New Thread
Reply to this Message