Microsoft Excel - Sum with Period of Date (sum during a date period)

canteen.zip
please find attached file.

From 22/12/2012 to 23/01/2012 (3) Persons were on the mess John,Subash,Chacko
From 23/01/2012 Chacko left the Mess (Resigned).24/01/2012 to 05/02/2012 (2) person were on the mess (John & Subash)
From 05/02/2012 to 25/02/2012 John was on Vacation so he was not in the mess. Only Subash on the mess.
From 25/03/2012 to 14/05/2012 Vijay was on Mess (Resigned & go away)
From 18/04/2012 to 14/04/2012 Girish came to mess so (4) Persons were on the mess
18/04/2012 to 26/04/2012 cash purchase so it should divide and add to 4 persons (John,Subash,Vijay & Girish
Total for Each :Person
John =
Chacko =
Subash =
Girish =
Vijay =

Hi John,

I am not sure that I understand you well. any way, I want to input something to be starting point for next tuning..

I assume that this the the time schedule, and want to share money,,,flat share...
I input 1 and 0 to indicate the on / off time attendant.
Then calculate in the table below..

here is sample for easy understand..-->canteen_Ans.zip

Hope this help.

Pichart Y

Thank you so much for your answer. Sir, Vijay came to to the mess on 25/03/2012. So his calculation should me make from the said date. ie  calculation should make from the 25/03/2012  to 14/05/2012. Also Girish came to the mess on 18/04/2012 so his calculation should make on the said period. also cash payment should do the following way as given below:

18/04/2012 to 26/04/2012 cash purchase so it should divide and add to 4 persons (John,Subash,Vijay & Girish

sir please help me to do the same way

Hi John,

I haave question about, please clarify me!!  the date range...it is not related to what they say...the period is differen...how to determine the number of date. Or we need to divide by the number of date in the period, then multiply with the number date of them.

and please help input manually the answer, so that I know exactly the way you make calculation.

pichart Y.

Sir, I don,t kow how can do the calcultion. Plase do the calculation as per the note given on the excel sheet .PLEASE HELP ME TO GIVE  A SOLUTTION AS PER THE NOTE GIVEN ON THE EXCEL SHEET. Sir per the note on the excel sheet how we can do the calculation. Plse help meeeeeeee. If you want to change the input data to solve the problem, then you can change the input. How we can do the calcultion
Thanks and Regards

Hi John,

• Now I count the number of day, they worked, exact number of day.
• then, I divide the total amount paid by the total effort in each period.
• Finally multiply back with the number of day of each person.
• So now, each will get only what they each worked.
  • Sample attachment -->canteen_Ans2.zip

Hope this help.

Pichart Y.

John,

I have looked and looked at your request and the one answer supplied, and I am still not 100% sure what you are trying to do. However I suspect that you are going to have to change the layout inorder to have excel do the calculations the correct way.

Are you able to change the layout?  Perhaps excel is not the right software to use for tracking this have you tried any others?

Thankyou  so much

You are welcome, by the way did you get  the answer, is this what you expect?

pichart Y.