Convert Month Display
How to convert the month in number to the month in text ?
For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
=TEXT(DATE(YEAR(TODAY()),A1,1),"mmmm yyyy")
If this post helps click Yes --------------- Jacob Skaria "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
This works with regional date settings of U.S. English:
=TEXT(A1&"-1","mmmm yyyy") -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... =TEXT(DATE(YEAR(TODAY()),A1,1),"mmmm yyyy") If this post helps click Yes --------------- Jacob Skaria "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
This works with regional date settings of U.S. English:
=TEXT(A1&"-1","mmmm yyyy") If you use regional date settings of the format: d/m/y then it would stand to reason that reversing the cell ref and the 1 should work: =TEXT("1-"&A1,"mmmm yyyy") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... This works with regional date settings of U.S. English: =TEXT(A1&"-1","mmmm yyyy") -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... =TEXT(DATE(YEAR(TODAY()),A1,1),"mmmm yyyy") If this post helps click Yes --------------- Jacob Skaria "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
Well Check out this formula......
I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
Hi, K****ij & Valki,
Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
Well in may case it was simple that i had formated the cell to just show the
month and Year..... Remember we had done-- right click on cell... Format and then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it to text, and the Date formula is the regular date formula..... nothing else..... So chill..... If you found the answer useful, Please press yes at bottom.... Ok.... "yclhk" wrote: Hi, K****ij & Valki, Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
Hi, K****ij,
Actually, i wish to know how does the Date & Text function work in the formula ? Why "A1+1" in your formulae and "1-"&A1 in Valko formulae ? Thanks, "K****ij" wrote: Well in may case it was simple that i had formated the cell to just show the month and Year..... Remember we had done-- right click on cell... Format and then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it to text, and the Date formula is the regular date formula..... nothing else..... So chill..... If you found the answer useful, Please press yes at bottom.... Ok.... "yclhk" wrote: Hi, K****ij & Valki, Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
=TEXT(A1&"-1","mmmm yyyy")
=TEXT("1-"&A1,"mmmm yyyy") Depending on your regional date settings Excel will interpret an entry like 1-2 or 2-1 as a valid date of the current year. For examle, with my regional date settings as U.S. English, if I type in cell A1 2-1 Excel automatically evaaluates that as a date and displays 1-Feb. Basically, in the formula we're just taking advantage of Excel's eagerness to identify dates! -- Biff Microsoft Excel MVP "yclhk" wrote in message ... Hi, K****ij, Actually, i wish to know how does the Date & Text function work in the formula ? Why "A1+1" in your formulae and "1-"&A1 in Valko formulae ? Thanks, "K****ij" wrote: Well in may case it was simple that i had formated the cell to just show the month and Year..... Remember we had done-- right click on cell... Format and then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it to text, and the Date formula is the regular date formula..... nothing else..... So chill..... If you found the answer useful, Please press yes at bottom.... Ok.... "yclhk" wrote: Hi, K****ij & Valki, Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
Hi Valko....
Thanks for the explanation, Also I have another querry if You could slove the same for me... Yesterday in one of the querry asked the reply answer to that was the following formula... =SUMPRODUCT(--($A$1:$A$1000=D1),--($B$1:$B$1000=E1),$C$1:$C$1000) Here can you explain the use of ' -- ' what does that do......? If that is possible....? Thanks "T. Valko" wrote: =TEXT(A1&"-1","mmmm yyyy") =TEXT("1-"&A1,"mmmm yyyy") Depending on your regional date settings Excel will interpret an entry like 1-2 or 2-1 as a valid date of the current year. For examle, with my regional date settings as U.S. English, if I type in cell A1 2-1 Excel automatically evaaluates that as a date and displays 1-Feb. Basically, in the formula we're just taking advantage of Excel's eagerness to identify dates! -- Biff Microsoft Excel MVP "yclhk" wrote in message ... Hi, K****ij, Actually, i wish to know how does the Date & Text function work in the formula ? Why "A1+1" in your formulae and "1-"&A1 in Valko formulae ? Thanks, "K****ij" wrote: Well in may case it was simple that i had formated the cell to just show the month and Year..... Remember we had done-- right click on cell... Format and then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it to text, and the Date formula is the regular date formula..... nothing else..... So chill..... If you found the answer useful, Please press yes at bottom.... Ok.... "yclhk" wrote: Hi, K****ij & Valki, Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
Let's see how the SUMPRODUCT formula works using this sample data:
...........A..........B..........C 1.......Fe.........Fi.........10 2.......Fo........Fum......22 3.......Foo......Bar.......17 4.......Foo......Gee......42 5.......Bar.......Fly.......19 You want to sum column C where column A = Foo and column B = Bar on the same row. =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5)) Result = 17 Here's how it works... SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So, we're going to multiply some things and then get the SUM of that multiplication. Each of these expressions will return an array of either TRUE (T) or FALSE (F): (A1:A5="Foo") (B1:B5="Bar") Fe = Foo = F Fo = Foo = F Foo = Foo = T Foo = Foo = T Bar = Foo = F Fi = Bar = F Fum = Bar = F Bar = Bar = T Gee = Bar = F Fly = Bar = F SUMPRODUCT works with numbers so we need to convert those logical values, TRUE and FALSE, to numbers. One way to do that is to use the double unary "--". It coerces TRUE to numeric 1 and FALSE to numeric 0: --TRUE = 1 --FALSE = 0 --(A1:A5="Foo") --(B1:B5="Bar") --(Fe = Foo) = F = 0 --(Fo = Foo) = F = 0 --(Foo = Foo) = T = 1 --(Foo = Foo) = T = 1 --(Bar = Foo) = F = 0 --(Fi = Bar) = F = 0 --(Fum = Bar) = F = 0 --(Bar = Bar) = T = 1 --(Gee = Bar) = F = 0 --(Fly = Bar) = F = 0 Now, here's where the multiplication takes place. We coerced the logical test arrays to numbers and the data in col C is already numbers so now these 3 arrays are multiplied together: 0 * 0 * 10 = 0 0 * 0 * 22 = 0 1 * 1 * 17 = 17 1 * 0 * 42 = 0 0 * 0 * 19 = 0 We have the results of the multiplication (PRODUCTS) so we just add (SUM) them up: =SUMPRODUCT({0;0;17;0;0}) = 17 So: =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5)) Result = 17 -- Biff Microsoft Excel MVP "K****ij" wrote in message ... Hi Valko.... Thanks for the explanation, Also I have another querry if You could slove the same for me... Yesterday in one of the querry asked the reply answer to that was the following formula... =SUMPRODUCT(--($A$1:$A$1000=D1),--($B$1:$B$1000=E1),$C$1:$C$1000) Here can you explain the use of ' -- ' what does that do......? If that is possible....? Thanks "T. Valko" wrote: =TEXT(A1&"-1","mmmm yyyy") =TEXT("1-"&A1,"mmmm yyyy") Depending on your regional date settings Excel will interpret an entry like 1-2 or 2-1 as a valid date of the current year. For examle, with my regional date settings as U.S. English, if I type in cell A1 2-1 Excel automatically evaaluates that as a date and displays 1-Feb. Basically, in the formula we're just taking advantage of Excel's eagerness to identify dates! -- Biff Microsoft Excel MVP "yclhk" wrote in message ... Hi, K****ij, Actually, i wish to know how does the Date & Text function work in the formula ? Why "A1+1" in your formulae and "1-"&A1 in Valko formulae ? Thanks, "K****ij" wrote: Well in may case it was simple that i had formated the cell to just show the month and Year..... Remember we had done-- right click on cell... Format and then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it to text, and the Date formula is the regular date formula..... nothing else..... So chill..... If you found the answer useful, Please press yes at bottom.... Ok.... "yclhk" wrote: Hi, K****ij & Valki, Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
Thanks Valko.....
Really Appreciated..... MAny A times it happens that I know the formula but i don't know how it works... this time i would know how that worked...... Thanks Again "T. Valko" wrote: Let's see how the SUMPRODUCT formula works using this sample data: ...........A..........B..........C 1.......Fe.........Fi.........10 2.......Fo........Fum......22 3.......Foo......Bar.......17 4.......Foo......Gee......42 5.......Bar.......Fly.......19 You want to sum column C where column A = Foo and column B = Bar on the same row. =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5)) Result = 17 Here's how it works... SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So, we're going to multiply some things and then get the SUM of that multiplication. Each of these expressions will return an array of either TRUE (T) or FALSE (F): (A1:A5="Foo") (B1:B5="Bar") Fe = Foo = F Fo = Foo = F Foo = Foo = T Foo = Foo = T Bar = Foo = F Fi = Bar = F Fum = Bar = F Bar = Bar = T Gee = Bar = F Fly = Bar = F SUMPRODUCT works with numbers so we need to convert those logical values, TRUE and FALSE, to numbers. One way to do that is to use the double unary "--". It coerces TRUE to numeric 1 and FALSE to numeric 0: --TRUE = 1 --FALSE = 0 --(A1:A5="Foo") --(B1:B5="Bar") --(Fe = Foo) = F = 0 --(Fo = Foo) = F = 0 --(Foo = Foo) = T = 1 --(Foo = Foo) = T = 1 --(Bar = Foo) = F = 0 --(Fi = Bar) = F = 0 --(Fum = Bar) = F = 0 --(Bar = Bar) = T = 1 --(Gee = Bar) = F = 0 --(Fly = Bar) = F = 0 Now, here's where the multiplication takes place. We coerced the logical test arrays to numbers and the data in col C is already numbers so now these 3 arrays are multiplied together: 0 * 0 * 10 = 0 0 * 0 * 22 = 0 1 * 1 * 17 = 17 1 * 0 * 42 = 0 0 * 0 * 19 = 0 We have the results of the multiplication (PRODUCTS) so we just add (SUM) them up: =SUMPRODUCT({0;0;17;0;0}) = 17 So: =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5)) Result = 17 -- Biff Microsoft Excel MVP "K****ij" wrote in message ... Hi Valko.... Thanks for the explanation, Also I have another querry if You could slove the same for me... Yesterday in one of the querry asked the reply answer to that was the following formula... =SUMPRODUCT(--($A$1:$A$1000=D1),--($B$1:$B$1000=E1),$C$1:$C$1000) Here can you explain the use of ' -- ' what does that do......? If that is possible....? Thanks "T. Valko" wrote: =TEXT(A1&"-1","mmmm yyyy") =TEXT("1-"&A1,"mmmm yyyy") Depending on your regional date settings Excel will interpret an entry like 1-2 or 2-1 as a valid date of the current year. For examle, with my regional date settings as U.S. English, if I type in cell A1 2-1 Excel automatically evaaluates that as a date and displays 1-Feb. Basically, in the formula we're just taking advantage of Excel's eagerness to identify dates! -- Biff Microsoft Excel MVP "yclhk" wrote in message ... Hi, K****ij, Actually, i wish to know how does the Date & Text function work in the formula ? Why "A1+1" in your formulae and "1-"&A1 in Valko formulae ? Thanks, "K****ij" wrote: Well in may case it was simple that i had formated the cell to just show the month and Year..... Remember we had done-- right click on cell... Format and then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it to text, and the Date formula is the regular date formula..... nothing else..... So chill..... If you found the answer useful, Please press yes at bottom.... Ok.... "yclhk" wrote: Hi, K****ij & Valki, Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
Convert Month Display
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "K****ij" wrote in message ... Thanks Valko..... Really Appreciated..... MAny A times it happens that I know the formula but i don't know how it works... this time i would know how that worked...... Thanks Again "T. Valko" wrote: Let's see how the SUMPRODUCT formula works using this sample data: ...........A..........B..........C 1.......Fe.........Fi.........10 2.......Fo........Fum......22 3.......Foo......Bar.......17 4.......Foo......Gee......42 5.......Bar.......Fly.......19 You want to sum column C where column A = Foo and column B = Bar on the same row. =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5)) Result = 17 Here's how it works... SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So, we're going to multiply some things and then get the SUM of that multiplication. Each of these expressions will return an array of either TRUE (T) or FALSE (F): (A1:A5="Foo") (B1:B5="Bar") Fe = Foo = F Fo = Foo = F Foo = Foo = T Foo = Foo = T Bar = Foo = F Fi = Bar = F Fum = Bar = F Bar = Bar = T Gee = Bar = F Fly = Bar = F SUMPRODUCT works with numbers so we need to convert those logical values, TRUE and FALSE, to numbers. One way to do that is to use the double unary "--". It coerces TRUE to numeric 1 and FALSE to numeric 0: --TRUE = 1 --FALSE = 0 --(A1:A5="Foo") --(B1:B5="Bar") --(Fe = Foo) = F = 0 --(Fo = Foo) = F = 0 --(Foo = Foo) = T = 1 --(Foo = Foo) = T = 1 --(Bar = Foo) = F = 0 --(Fi = Bar) = F = 0 --(Fum = Bar) = F = 0 --(Bar = Bar) = T = 1 --(Gee = Bar) = F = 0 --(Fly = Bar) = F = 0 Now, here's where the multiplication takes place. We coerced the logical test arrays to numbers and the data in col C is already numbers so now these 3 arrays are multiplied together: 0 * 0 * 10 = 0 0 * 0 * 22 = 0 1 * 1 * 17 = 17 1 * 0 * 42 = 0 0 * 0 * 19 = 0 We have the results of the multiplication (PRODUCTS) so we just add (SUM) them up: =SUMPRODUCT({0;0;17;0;0}) = 17 So: =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5)) Result = 17 -- Biff Microsoft Excel MVP "K****ij" wrote in message ... Hi Valko.... Thanks for the explanation, Also I have another querry if You could slove the same for me... Yesterday in one of the querry asked the reply answer to that was the following formula... =SUMPRODUCT(--($A$1:$A$1000=D1),--($B$1:$B$1000=E1),$C$1:$C$1000) Here can you explain the use of ' -- ' what does that do......? If that is possible....? Thanks "T. Valko" wrote: =TEXT(A1&"-1","mmmm yyyy") =TEXT("1-"&A1,"mmmm yyyy") Depending on your regional date settings Excel will interpret an entry like 1-2 or 2-1 as a valid date of the current year. For examle, with my regional date settings as U.S. English, if I type in cell A1 2-1 Excel automatically evaaluates that as a date and displays 1-Feb. Basically, in the formula we're just taking advantage of Excel's eagerness to identify dates! -- Biff Microsoft Excel MVP "yclhk" wrote in message ... Hi, K****ij, Actually, i wish to know how does the Date & Text function work in the formula ? Why "A1+1" in your formulae and "1-"&A1 in Valko formulae ? Thanks, "K****ij" wrote: Well in may case it was simple that i had formated the cell to just show the month and Year..... Remember we had done-- right click on cell... Format and then Number and Custom...... and TYpe..... "MMMM YYYY", this mad it to text, and the Date formula is the regular date formula..... nothing else..... So chill..... If you found the answer useful, Please press yes at bottom.... Ok.... "yclhk" wrote: Hi, K****ij & Valki, Thanks for your formula. Pls be kind to explain how the Date & Text worked in this formula. Thanks, "K****ij" wrote: Well Check out this formula...... I belive, it should be sufficient...... First custmosize the B1 to format(custom) in that have it as mmmm yyyy Then put the formula in B1 as =Date(2009,A1+1,0) Well it will return as Feburary 2009, Now I hope that is helpful and simple.... And if you ask me why I have taken "A1+1" the i am not aware but it works that way..... "yclhk" wrote: How to convert the month in number to the month in text ? For example : in cell A1 enter 2 in cell B1 display February 2009 Thanks, |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com