Re: [users] Calc -- monthname() function
At 17:24 16/02/2008 -0500, Kenn Goutal wrote: It appears that, in my version at least, there is no function that, given a number 1..12, returns the name of the corresponding month. Try =TEXT(A1*30;). Failing to find one, I tried to fetch the i-th element of an 12-row column of month names, but I'm apparently not clever enough to code that. o Select just your twelve cells containing the month names. o Go to Insert | Names | Define... and give the block the name monthname (no quotes). o Now use =INDEX(monthname;A1). I trust this helps. Brian Barker - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Calc -- monthname() function
Kenn Goutal wrote: It appears that, in my version at least, there is no function that, given a number 1..12, returns the name of the corresponding month. Am I missing something? Failing to find one, I tried to fetch the i-th element of an 12-row column of month names, but I'm apparently not clever enough to code that. Ideas? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] As an alternative to Brian's neat trick, I made my own function which is in a module of My Macros Dialogs.Standard: function MonthName(MonthNumber as integer) select case MonthNumber case 1 MonthName = January case 2 MonthName = February case 3 MonthName = March case 4 MonthName = April case 5 MonthName = May case 6 MonthName = June case 7 MonthName = July case 8 MonthName = August case 9 MonthName = September case 10 MonthName = October case 11 MonthName = November case 12 MonthName = December end select end function I have used this in Excel at work. For me it was quick and simple at the time. I am still amazed at the tricks and shortcuts I find on websites that would never of occurred to me. TomW - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Calc -- monthname() function
On 16/02/2008 22:56, Brian Barker wrote: At 17:24 16/02/2008 -0500, Kenn Goutal wrote: It appears that, in my version at least, there is no function that, given a number 1..12, returns the name of the corresponding month. Try =TEXT(A1*30;). Please, why the *30? It works, but why? -- Harold Fuchs London, England Please reply *only* to users@openoffice.org - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Calc -- monthname() function
On 16/02/2008 22:56, Brian Barker wrote: At 17:24 16/02/2008 -0500, Kenn Goutal wrote: It appears that, in my version at least, there is no function that, given a number 1..12, returns the name of the corresponding month. Try =TEXT(A1*30;). Please, why the *30? It works, but why? -- Harold Fuchs London, England Please reply *only* to users@openoffice.org - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Calc -- monthname() function
At 00:07 17/02/2008 +, Harold Fuchs wrote: On 16/02/2008 22:56, Brian Barker wrote: At 17:24 16/02/2008 -0500, Kenn Goutal wrote: It appears that, in my version at least, there is no function that, given a number 1..12, returns the name of the corresponding month. Try =TEXT(A1*30;). Please, why the *30? It works, but why? We need to create a date in the appropriate month in order for the TEXT() function to be able to work on it. And you will know that dates are stored internally as the number of days after 30 December 1899. If we multiply by 30, 1 maps to 30, i.e. 29 January 1900, 2 maps to 60, i.e. 28 February 1900, and so on up to 12, which gives 360, or 25 December 1900. I fact, we could use 29 or 28 instead: 28 gives dates from 27 January 1900 to 1 December 1900. Anything greater than 30 spills over into the wrong months after January, and 27 doesn't make it into September properly (or beyond). It occurs to me now (though I confess it didn't before) that the correct functioning of this technique might depend on the setting at Tools | Options... | OpenOffice.org Calc | Calculate | Date. But I find now that it happens that it works similarly (with multipliers from 27 to 30) for all three choices of the reference date. Brian Barker - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Calc -- monthname() function
On 17/02/2008 01:10, Brian Barker wrote: At 00:07 17/02/2008 +, Harold Fuchs wrote: On 16/02/2008 22:56, Brian Barker wrote: At 17:24 16/02/2008 -0500, Kenn Goutal wrote: It appears that, in my version at least, there is no function that, given a number 1..12, returns the name of the corresponding month. Try =TEXT(A1*30;). Please, why the *30? It works, but why? We need to create a date in the appropriate month in order for the TEXT() function to be able to work on it. And you will know that dates are stored internally as the number of days after 30 December 1899. If we multiply by 30, 1 maps to 30, i.e. 29 January 1900, 2 maps to 60, i.e. 28 February 1900, and so on up to 12, which gives 360, or 25 December 1900. I fact, we could use 29 or 28 instead: 28 gives dates from 27 January 1900 to 1 December 1900. Anything greater than 30 spills over into the wrong months after January, and 27 doesn't make it into September properly (or beyond). It occurs to me now (though I confess it didn't before) that the correct functioning of this technique might depend on the setting at Tools | Options... | OpenOffice.org Calc | Calculate | Date. But I find now that it happens that it works similarly (with multipliers from 27 to 30) for all three choices of the reference date. Brian Barker Yes, of course; thank you. -- Harold Fuchs London, England Please reply *only* to users@openoffice.org - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
