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.
What’s in C7 on the 5th April 2013?
5TH April 0 Dollars; 6th April 70 Dollars; 6 May 140 Dollars; 6 June 210 Dollars … 6 February 2014 840 Dollars.
How about this one? =MONTHS(DATE(2013;3;6);NOW();1)*70
Add necessary lower and upper limit yourself, if needed.
@ROSt53 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.
@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.
-
MONTH and MONTHS are different functions.
-
I use localized LibO, replace ; with , and it should run fine.
-
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.
@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 here
@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?
@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?
Somebody 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.
Can you possibly attach a copy of the spreadsheet with all names etc made anonymous?