Re: [libreoffice-users] Calculating MONTHNAME
Think the issue is that with "MMM", it treats the number as a date value instead of as being a month, so that 1 would result in December, and 2-12 result in January. So, the text option with month() doesn't seem to work at all, since the values don't match. Here is using choose to get what I think. Longer formula, but it seems to work, and would allow you do choose what exact format you want for each month. Number '=CHOOSE(MONTH(A4),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") 1 Dec 2 Jan 3 Jan 4 Jan 5 Jan 6 Jan 7 Jan 8 Jan 9 Jan 10 Jan 11 Jan 12 Jan Dates '=CHOOSE(MONTH(A4),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") 01/01/17 Jan 02/02/17 Feb 03/06/17 Mar 04/07/17 Apr 05/09/17 May 06/10/17 Jun 07/12/17 Jul 08/13/17 Aug 09/14/17 Sep 10/16/17 Oct 11/17/17 Nov 12/19/17 Dec On 13 Jan 2017 at 18:12, Remy Gauthier wrote: Subject:Re: [libreoffice-users] Calculating MONTHNAME From: Remy Gauthier To: Tanstaafl , users@global.libreoffice.org Date sent: Fri, 13 Jan 2017 18:12:39 -0500 > Hello, > > Day "1" is December 31, 1899 (at least, this is what I get when I > display "1" with a -MM-DD format): this is why > 'TEXT(MONTH(NOW()),"MMM")"' gives "December". To get something like > this to come out consistently, I always use a formula like this: > > =TEXT(DATE(1900;MONTH(NOW());1);"MMM") [basically asking for the month > of Jan 1, 1900] > > In this case, the formula is not influenced by the date encoding scheme > and will yield the desired result. > > I hope this helps. > > Rémy Gauthier. > > Le vendredi 13 janvier 2017 à 15:17 -0500, Tanstaafl a écrit : > > > If it is its been there a long time, because I first encountered this > a > > lng time ago (I finally decided to ask about it). > > > > Would appreciate someone confirming I'm not just crazy, and it should > > work as I'm expecting. > > > > > On Fri Jan 13 2017 15:12:50 GMT-0500 (Eastern Standard Time), Joe > Conner > > > wrote: > > > Bug??? > > > > > > On 01/13/2017 12:05 PM, Tanstaafl wrote: > > > > Ok, this is really driving me nuts... > > > > > > > > Given: > > > > > > > > =MONTH(NOW()) > > > > results in the number of the current month (1, for January) > > > > > > > > I want to simply translate this to the monthname, so I used: > > > > > > > > =TEXT(MONTH(NOW()),"MMM") > > > > > > > this almost gives me what I want, but it results in "Dec', > instead of > > > > "Jan" - WTF??? > > > > > > > > Changing it to: > > > > > > > > =TEXT(MONTH(NOW())+1,"MMM") > > > > gives me "Jan", which is what I want. > > > > > > > > Why do I have to add a '1' to it??? > > > > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.libreoffice.org/global/users/ > All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and c
[libreoffice-users] Calc - blank cell not treated as 0 in formula when referenced from external file/sheet
Hello, Before I go open a bug for this, would appreciate if someone could confirm. Using 5.2.3 on Windows 7 x64... I have a spreadsheet - call it 'A' - with 9 sheets in it, and lots of formulas/references between the sheets. These all work fine and have for a very long time. I am working on a new spreadsheet - call it 'B' - that will have a very similar master sheet that will pull from spreadsheet 'A'. Since the only sheet in this new spreadsheet will be very similar to the main sheet on spreadsheet 'A', I started with a copy of 'A', then deleted all of the sheets I wouldn't need except the one I will be pulling data from. the plan was to get the formulas (math formulas) working with sheet references first, then once they were working, change the sheet reference to the external reference. Everything went as planned, with one exception. Some of the cells are blank on the sheet being pulled from. The formulas correctly interpreted the blank cells as zeros while I was using sheet references. But when I changed these to reference the exact same cell, but in the other spreadsheet 'A', I got the '#VALUE!' error. Is this an expected difference when referencing an external file/sheet? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calculating MONTHNAME
Hello, Day "1" is December 31, 1899 (at least, this is what I get when I display "1" with a -MM-DD format): this is why 'TEXT(MONTH(NOW()),"MMM")"' gives "December". To get something like this to come out consistently, I always use a formula like this: =TEXT(DATE(1900;MONTH(NOW());1);"MMM") [basically asking for the month of Jan 1, 1900] In this case, the formula is not influenced by the date encoding scheme and will yield the desired result. I hope this helps. Rémy Gauthier. Le vendredi 13 janvier 2017 à 15:17 -0500, Tanstaafl a écrit : > > If it is its been there a long time, because I first encountered this a > lng time ago (I finally decided to ask about it). > > Would appreciate someone confirming I'm not just crazy, and it should > work as I'm expecting. > > > On Fri Jan 13 2017 15:12:50 GMT-0500 (Eastern Standard Time), Joe Conner > > wrote: > > Bug??? > > > > On 01/13/2017 12:05 PM, Tanstaafl wrote: > > > Ok, this is really driving me nuts... > > > > > > Given: > > > > > > =MONTH(NOW()) > > > results in the number of the current month (1, for January) > > > > > > I want to simply translate this to the monthname, so I used: > > > > > > =TEXT(MONTH(NOW()),"MMM") > > > > > > this almost gives me what I want, but it results in "Dec', instead of > > > "Jan" - WTF??? > > > > > > Changing it to: > > > > > > =TEXT(MONTH(NOW())+1,"MMM") > > > gives me "Jan", which is what I want. > > > > > > Why do I have to add a '1' to it??? > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calculating MONTHNAME
13.01.2017 u 21:28, Kruno je napisao/la: 13.01.2017 u 21:17, Tanstaafl je napisao/la: If it is its been there a long time, because I first encountered this a lng time ago (I finally decided to ask about it). Would appreciate someone confirming I'm not just crazy, and it should work as I'm expecting. On Fri Jan 13 2017 15:12:50 GMT-0500 (Eastern Standard Time), Joe Conner wrote: Bug??? On 01/13/2017 12:05 PM, Tanstaafl wrote: Ok, this is really driving me nuts... Given: =MONTH(NOW()) results in the number of the current month (1, for January) I want to simply translate this to the monthname, so I used: =TEXT(MONTH(NOW()),"MMM") this almost gives me what I want, but it results in "Dec', instead of "Jan" - WTF??? Changing it to: =TEXT(MONTH(NOW())+1,"MMM") gives me "Jan", which is what I want. And =TEXT(MONTH(NOW()) + 3; "MMM") gives january Oops, I got it, it does days, sorry for trolling :( Why do I have to add a '1' to it??? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calculating MONTHNAME
Try =text(now(),"MMM") Tony. On Fri, 2017-01-13 at 15:05 -0500, Tanstaafl wrote: Ok, this is really driving me nuts... Given: =MONTH(NOW()) results in the number of the current month (1, for January) I want to simply translate this to the monthname, so I used: =TEXT(MONTH(NOW()),"MMM") this almost gives me what I want, but it results in "Dec', instead of "Jan" - WTF??? Changing it to: =TEXT(MONTH(NOW())+1,"MMM") gives me "Jan", which is what I want. Why do I have to add a '1' to it??? -- Tony Arnold MBCS, CITP | Senior IT Security Analyst | Directorate of IT Services | G64, Kilburn Building | The University of Manchester | Manchester M13 9PL | T: +44 161 275 6093 | M: +44 773 330 0039 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calculating MONTHNAME
13.01.2017 u 21:17, Tanstaafl je napisao/la: If it is its been there a long time, because I first encountered this a lng time ago (I finally decided to ask about it). Would appreciate someone confirming I'm not just crazy, and it should work as I'm expecting. On Fri Jan 13 2017 15:12:50 GMT-0500 (Eastern Standard Time), Joe Conner wrote: Bug??? On 01/13/2017 12:05 PM, Tanstaafl wrote: Ok, this is really driving me nuts... Given: =MONTH(NOW()) results in the number of the current month (1, for January) I want to simply translate this to the monthname, so I used: =TEXT(MONTH(NOW()),"MMM") this almost gives me what I want, but it results in "Dec', instead of "Jan" - WTF??? Changing it to: =TEXT(MONTH(NOW())+1,"MMM") gives me "Jan", which is what I want. And =TEXT(MONTH(NOW()) + 3; "MMM") gives january Why do I have to add a '1' to it??? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calculating MONTHNAME
If it is its been there a long time, because I first encountered this a lng time ago (I finally decided to ask about it). Would appreciate someone confirming I'm not just crazy, and it should work as I'm expecting. On Fri Jan 13 2017 15:12:50 GMT-0500 (Eastern Standard Time), Joe Conner wrote: > Bug??? > > On 01/13/2017 12:05 PM, Tanstaafl wrote: >> Ok, this is really driving me nuts... >> >> Given: >> >> =MONTH(NOW()) >> results in the number of the current month (1, for January) >> >> I want to simply translate this to the monthname, so I used: >> >> =TEXT(MONTH(NOW()),"MMM") >> this almost gives me what I want, but it results in "Dec', instead of >> "Jan" - WTF??? >> >> Changing it to: >> >> =TEXT(MONTH(NOW())+1,"MMM") >> gives me "Jan", which is what I want. >> >> Why do I have to add a '1' to it??? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calculating MONTHNAME
Bug??? On 01/13/2017 12:05 PM, Tanstaafl wrote: Ok, this is really driving me nuts... Given: =MONTH(NOW()) results in the number of the current month (1, for January) I want to simply translate this to the monthname, so I used: =TEXT(MONTH(NOW()),"MMM") this almost gives me what I want, but it results in "Dec', instead of "Jan" - WTF??? Changing it to: =TEXT(MONTH(NOW())+1,"MMM") gives me "Jan", which is what I want. Why do I have to add a '1' to it??? -- Blessings, Joe Conner Joshua 24:15 "...as for me and my house, we will serve the LORD." -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Calculating MONTHNAME
Ok, this is really driving me nuts... Given: =MONTH(NOW()) results in the number of the current month (1, for January) I want to simply translate this to the monthname, so I used: =TEXT(MONTH(NOW()),"MMM") this almost gives me what I want, but it results in "Dec', instead of "Jan" - WTF??? Changing it to: =TEXT(MONTH(NOW())+1,"MMM") gives me "Jan", which is what I want. Why do I have to add a '1' to it??? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Keyboard shortcut or button to update links to external file(s)?
Ok, this really shouldn't be so hard. Is there an easy way to just update the links to an external file? A keyboard shortcut, or a toolbar button, or both? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Opening mdb jet db files under Linux
Thanks Alex! Dave, On Fri, Jan 13, 2017, at 06:13 AM, Alexander Thurgood wrote: > Le 12/01/2017 à 22:34, dave boland a écrit : > > Hi Dave, > > > > Will Base do this? If so, how? I tried to open one, and nothing > > happened. > > > > > Yes, via this : > > > http://ucanaccess.sourceforge.net/site.html > > > Alex > > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.libreoffice.org/global/users/ > All messages sent to this list will be publicly archived and cannot be > deleted -- dave boland dbola...@fastmail.fm -- http://www.fastmail.com - Accessible with your email software or over the web -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Opening mdb jet db files under Linux
Le 12/01/2017 à 22:34, dave boland a écrit : Hi Dave, > Will Base do this? If so, how? I tried to open one, and nothing > happened. > Yes, via this : http://ucanaccess.sourceforge.net/site.html Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
