Prorating electricity based on residents move in and out date

[Robhat86]

I have to figure out the prorated amount each resident in a dorm owes for the monthly electricity based on their move in or out date.

Billing cycles do not match up with calendar months so a billing date range might be (7/7/16-8/5/16)

Each dorm has 4 residents and due to turn over rates we might have a resident move out at the beginning of the month and a replacement move in a week later. After calculating the daily rate we must then divide by the number of residents on a given day and then sum what each resident owes for the billing cycle.

Any help would be appreciated.

 

[CalcSux78]

Rob, welcome to the forum! Should be fairly easy, but would you be able to post an example set of data so we can get a better idea of your current layout and data?