Here is the case -
StartDate EndDate Total amount January February March ......
01/27/2007 06/30/2007 $1,875.00
01/01/2007 08/07/2007 $2,195.00
02/10/2007 05/01/2007 $1,500.00
How to calculate amounts for each month ?
The word January should be above 732.22. The format was distorted here. But I wonder why excel screen shot doesnt appear in Sajha?
this might work.
Once more
Every day worths same amount. Lets not look at it as working days ... but as contract.
What I need is a formula in one cell ( e.g. in January column) that holds true for all others cells in all other months.
You can vary no. of days or you can put same no. of day for each month. Check your sajha email. it has screen shot. It may help you.
Nepolean:
Can you send it to me too? I am also interested to know the calculation buddy.
Puruz
ok buddy, check your email. But I have shown only one example and it follows the condition rascal mentioned. If there is different condition, you can put additional logical conditions. For description, follow above writings. Let me paste for you so that you can give a glance at once =>
Considering 7 working days and each day worths equal amount, here is one example.
For the date cell, click format and click date. For amount , click format and click number.
Current Sheet
January
1/12/2007 3/25/2007 3500.00 732.22
1/25/2007 3/20/2007 2500.00 769.11
1/5/2007 2/20/2007 2200.00 744.09
Sheet 2
January February March
1/31/2007 02/31/07 3/31/2007
732.22 is E5 ; 1/31/05 is D5 in sheet 2 and I used this formula
=((B5-C5)/D5)*(Sheet1!D5-B5)
With the reference E5 for current sheet and D5 for sheet 2, all the there is no blank cell between consecutive rows and columns.
hey whats ur email address?
I will send my solution to you.
Let me try this one more time..
Hey Bond 007 and Nepalean
I got the calculations and formulas that you guys are showing but when I try, it gives me totally different result.
2/12/2007 7/17/2007 4475.00 1/6/1900 (for deducting dates)
3/27/2007 8/14/2007 4700.00 1/5/1900
1/15/2007 7/12/2007 9225.00 1/6/1900
I don't know what is the problem.
In nepalean's case, I used exactly the same formula all I get is #########.
Puruz
ye mula purush, tyo lekhekko pada na yar. ######### ayo bhane tyo cell ma right click garera format ma number select gara ( fomat ma date, scentific, etc huncha).
Maile mathi bhanya k ta ? " For the date cell, click format and click date. For amount , click format and click number. "
But mine method is different than Bond. My method includes exact no. of days for all the starting date to ending date ( whether its 1 or 2 or 25)
if you see 1/1/1900..
all u need to do is
1. Rt Click
2. Format cells
3. select General (it will be date by default what why it showed that 1/1/1900)
hope this helps!
I tried ... but got values in decimals ... something really fishy going on!
1875.00 2,195.00 1500.00 Monthly
JAN 60.48 31.00 10.02 310.71 371.19
FEB 338.71 28.00 10.02 280.64 19.00 18.52 351.85 971.20
MAR 375.00 31.00 10.02 310.71 31.00 18.52 574.07 1259.78
APR 362.90 30.00 10.02 300.68 30.00 18.52 555.56 1219.14
MAY 375.00 31.00 10.02 310.71 1.00 18.52 18.52 704.23
JUN 362.90 30.00 10.02 300.68 0.00 663.59
JUL 31.00 10.02 310.71 0.00 310.71
AUG 7.00 10.02 70.16 0.00 70.16
219.00 81.00 5570.00
Who ever you have asked this simple question must be a smarty A .....H..... because you gave your homework to us. You should have uesed a calculator and a calender and got the answer.
Any way here is my math for you.
Months 1875.00 2,195.00 1500.00 5570.00
JAN 60.48 310.71 371.19
FEB 338.71 280.64 351.85 971.20
MAR 375.00 310.71 574.07 1259.78
APR 362.90 300.68 555.56 1219.14
MAY 375.00 310.71 18.52 704.23
JUN 362.90 300.68 - 663.59
JUL -00 310.71 00- 310.71
AUG -00 70.16 00- 70.16
- - - 5570.00
I think it is clearer now.
1875.00 2,195.00 1500.00 5570.00
JAN 60.48 310.71 00.00 371.19
FEB 338.71 280.64 351.85 971.20
MAR 375.00 310.71 574.07 1259.78
APR 362.90 300.68 555.56 1219.14
MAY 375.00 310.71 18.52 704.23
JUN 362.90 300.68 - 00.00 663.59
JUL - 00 310.71 00- 310.71
AUG -00 70.16 00- 70.16
Total 5,570.00
haha .... if I try to do this by using a fking calculator and a fking calendar, it will take me fking years coz the data I have posted above is just a preface of a mahabharat.
thank you!
I love you all.
does our solution work?
Is this what u were looking for?
Please log in to reply to this post
You can also log in using your Facebook