Ask Your Question
0

Calc Formula [closed]

asked 2013-03-27 23:33:02 +0200

Huskey gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-27 20:16:04.076675

Comments

What's in C7 on the 5th April 2013?

PhiJ gravatar imagePhiJ ( 2013-03-27 23:48:11 +0200 )edit

5TH April 0 Dollars; 6th April 70 Dollars; 6 May 140 Dollars; 6 June 210 Dollars ... 6 February 2014 840 Dollars.

Huskey gravatar imageHuskey ( 2013-03-29 07:33:47 +0200 )edit

4 Answers

Sort by » oldest newest most voted
0

answered 2013-03-28 04:20:55 +0200

ROSt52 gravatar image

updated 2013-03-28 04:28:32 +0200

@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?

edit flag offensive delete link more
0

answered 2013-03-29 08:48:38 +0200

ROSt52 gravatar image

@ huskey - maybe you can use the formulas I created.

See attached ods-file

ForHuskey.ods

edit flag offensive delete link more
0

answered 2013-03-29 03:00:46 +0200

ROSt52 gravatar image

@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?

edit flag offensive delete link more

Comments

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.

Huskey gravatar imageHuskey ( 2013-03-29 07:45:58 +0200 )edit

Can you possibly attach a copy of the spreadsheet with all names etc made anonymous?

ROSt52 gravatar imageROSt52 ( 2013-03-29 08:30:35 +0200 )edit
0

answered 2013-03-28 01:12:23 +0200

mahfiaz gravatar image

How about this one? =MONTHS(DATE(2013;3;6);NOW();1)*70

Add necessary lower and upper limit yourself, if needed.

edit flag offensive delete link more

Comments

@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.

mahfiaz gravatar imagemahfiaz ( 2013-03-28 16:23:34 +0200 )edit

@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.

ROSt52 gravatar imageROSt52 ( 2013-03-29 02:51:14 +0200 )edit

1) 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.

mahfiaz gravatar imagemahfiaz ( 2013-03-29 09:06:02 +0200 )edit
1

@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

ROSt52 gravatar imageROSt52 ( 2013-03-29 09:35:53 +0200 )edit

Question Tools

Stats

Asked: 2013-03-27 23:33:02 +0200

Seen: 352 times

Last updated: Mar 29 '13