Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 29 Mar 2013 09:35:53 +0100Calc Formulahttps://ask.libreoffice.org/en/question/15312/calc-formula/Hi. Is it possible to create a formula for a Calc cell covering the following criteria: 70 Dollars are added to cell C7 each month on the 6th day between 1 April 2013 and 31 March 2014. Many thanks for any help.Wed, 27 Mar 2013 23:33:02 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/Comment by Huskey for <p>Hi. Is it possible to create a formula for a Calc cell covering the following criteria: 70 Dollars are added to cell C7 each month on the 6th day between 1 April 2013 and 31 March 2014. Many thanks for any help.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15364#post-id-153645TH April 0 Dollars; 6th April 70 Dollars; 6 May 140 Dollars; 6 June 210 Dollars ... 6 February 2014 840 Dollars.Fri, 29 Mar 2013 07:33:47 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15364#post-id-15364Comment by PhiJ for <p>Hi. Is it possible to create a formula for a Calc cell covering the following criteria: 70 Dollars are added to cell C7 each month on the 6th day between 1 April 2013 and 31 March 2014. Many thanks for any help.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15313#post-id-15313What's in C7 on the 5th April 2013?Wed, 27 Mar 2013 23:48:11 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15313#post-id-15313Answer by ROSt52 for <p>Hi. Is it possible to create a formula for a Calc cell covering the following criteria: 70 Dollars are added to cell C7 each month on the 6th day between 1 April 2013 and 31 March 2014. Many thanks for any help.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15320#post-id-15320@mahfiaz - I looked at the very interesting formula you proposed but what is the result of your formula?
I am getting an error message and feel the the argument of =month(xxx) is the cause.
I would use
> =IF(MONTH(DATE(2013,3,30))=MONTH(NOW()),1,0)*70
or more simplified
> =IF(3=MONTH(NOW()),1,0)*70
where the first "3" represents the month.
My formulas would result in 70 for every day in March in every year.
However, I also would like to understand your formula because there are often several ways. (Actually your formula helped me to develop mine; thus thanks for your hint.)
(I am using comma as a separator.)
@Huskey - I am aware that this is not yet the answer to your question, but can use the sketches of @mahfiaz and me to develop the formula you needed?Thu, 28 Mar 2013 04:20:55 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15320#post-id-15320Answer by ROSt52 for <p>Hi. Is it possible to create a formula for a Calc cell covering the following criteria: 70 Dollars are added to cell C7 each month on the 6th day between 1 April 2013 and 31 March 2014. Many thanks for any help.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15358#post-id-15358@Huskey - I thought again about the answer to your question and found that it depends very much on how you want to use the formula. Could you possible attach a Calc file with some dummy data and explanations?
Fri, 29 Mar 2013 03:00:46 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15358#post-id-15358Comment by ROSt52 for <p><a href="/en/users/2273/huskey/">@Huskey</a> - I thought again about the answer to your question and found that it depends very much on how you want to use the formula. Could you possible attach a Calc file with some dummy data and explanations?</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15369#post-id-15369Can you possibly attach a copy of the spreadsheet with all names etc made anonymous?Fri, 29 Mar 2013 08:30:35 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15369#post-id-15369Comment by Huskey for <p><a href="/en/users/2273/huskey/">@Huskey</a> - I thought again about the answer to your question and found that it depends very much on how you want to use the formula. Could you possible attach a Calc file with some dummy data and explanations?</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15365#post-id-15365Somebody is entitled to receive a fixed amount, e.g. 70 Dollars, on the 6th day of each month for one year. After each year this amount is revised and adjusted higher or lower. But actual payments are usually higher or lower than 70 Dollars per months which results in the need of this spreadsheet: actual payments minus entitlement = balance. Hope this helps.Fri, 29 Mar 2013 07:45:58 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15365#post-id-15365Answer by mahfiaz for <p>Hi. Is it possible to create a formula for a Calc cell covering the following criteria: 70 Dollars are added to cell C7 each month on the 6th day between 1 April 2013 and 31 March 2014. Many thanks for any help.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15314#post-id-15314How about this one? `=MONTHS(DATE(2013;3;6);NOW();1)*70`
Add necessary lower and upper limit yourself, if needed.Thu, 28 Mar 2013 01:12:23 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15314#post-id-15314Comment by mahfiaz for <p>How about this one? <code>=MONTHS(DATE(2013;3;6);NOW();1)*70</code></p>
<p>Add necessary lower and upper limit yourself, if needed.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15371#post-id-153711) MONTH and MONTHS are different functions.
2) I use localized LibO, replace ; with , and it should run fine.
3) for explanation of arguments the easiest lookup is function dialog: press purple fx icon on the input line or from menu choose Insert → Function or press Ctrl+F2.Fri, 29 Mar 2013 09:06:02 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15371#post-id-15371Comment by ROSt52 for <p>How about this one? <code>=MONTHS(DATE(2013;3;6);NOW();1)*70</code></p>
<p>Add necessary lower and upper limit yourself, if needed.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15356#post-id-15356@mahfiaz - Thanks for your comment and explanations. I see the combination of DATE and NOW to filter the month of March. I still need to understand the syntax of the argument of the MONTH function better.Fri, 29 Mar 2013 02:51:14 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15356#post-id-15356Comment by mahfiaz for <p>How about this one? <code>=MONTHS(DATE(2013;3;6);NOW();1)*70</code></p>
<p>Add necessary lower and upper limit yourself, if needed.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15341#post-id-15341@ROSt52 A little explanation here, what it does. It finds difference on the given date (has to be one month in the past) with now() in months (according to calendar months). So the very same cell will show you different results depending on when you open it.Thu, 28 Mar 2013 16:23:34 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15341#post-id-15341Comment by ROSt52 for <p>How about this one? <code>=MONTHS(DATE(2013;3;6);NOW();1)*70</code></p>
<p>Add necessary lower and upper limit yourself, if needed.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15373#post-id-15373@mahfiaz - shame on me - i apologize for not having seen the "s" at the end of month, now the arguments used make a lot of sense. I hope this is additional information for @Huskey to assemble the formula he needs. Thanks mahfiaz!!!
@Huskey - Please give feedback hereFri, 29 Mar 2013 09:35:53 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?comment=15373#post-id-15373Answer by ROSt52 for <p>Hi. Is it possible to create a formula for a Calc cell covering the following criteria: 70 Dollars are added to cell C7 each month on the 6th day between 1 April 2013 and 31 March 2014. Many thanks for any help.</p>
https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15370#post-id-15370@ huskey - maybe you can use the formulas I created.
See attached ods-file
[ForHuskey.ods](/upfiles/13645432666167001.ods)
Fri, 29 Mar 2013 08:48:38 +0100https://ask.libreoffice.org/en/question/15312/calc-formula/?answer=15370#post-id-15370