johnnytominaga
New Member
- Joined
- Apr 27, 2018
- Messages
- 19
Hey there!
I'm working on a project where I need to calculate shared bills among multiple properties. The problem is that occupation varies across them, with people checking in and out, but bills needing to be calculated so that people can pay correct proportional amounts.
I've been trying to implement a solution where the user inputs:
a) Room/Property name (from a drop down list)
b) Date
c) Current bill amount
d) Amount already paid in the month
The database includes:
a) Booking ID
b) Room/Property name
c) Booking Start Date
d) Booking duration
e) Booking End Date
f) Person's Name
I've been trying to run something like:
a) Which rows include the room name inputted
b) Which rows include a start date lower than the date inputted and an end date higher than the date inputted
c) For each row that matches the criteria above, sum number of people at the room/property in each day of the month from the beginning of the month of the date inputted until either the end of the month or the end date of that record (sum(day 1/no. of people on day 1, day 2/no. of people on day 2, day 3/no. of people on day 3, ..., end date/no of people on end date) or sumproduct((range of dates in the month)*(range of no. of people per day)))
d) Output the Current bill amount inputted minus Amount already paid in the month divided by the sum result
So, the idea is to get a list of people staying at the property at Date inputted with the proportion amount each of them should pay based on the Current bill amount minus the Amount already paid in the month, for that specific room/property.
I unfortunately haven't managed to make this work.
Can anyone help me with this?
Thanks,
Johnny
I'm working on a project where I need to calculate shared bills among multiple properties. The problem is that occupation varies across them, with people checking in and out, but bills needing to be calculated so that people can pay correct proportional amounts.
I've been trying to implement a solution where the user inputs:
a) Room/Property name (from a drop down list)
b) Date
c) Current bill amount
d) Amount already paid in the month
The database includes:
a) Booking ID
b) Room/Property name
c) Booking Start Date
d) Booking duration
e) Booking End Date
f) Person's Name
I've been trying to run something like:
a) Which rows include the room name inputted
b) Which rows include a start date lower than the date inputted and an end date higher than the date inputted
c) For each row that matches the criteria above, sum number of people at the room/property in each day of the month from the beginning of the month of the date inputted until either the end of the month or the end date of that record (sum(day 1/no. of people on day 1, day 2/no. of people on day 2, day 3/no. of people on day 3, ..., end date/no of people on end date) or sumproduct((range of dates in the month)*(range of no. of people per day)))
d) Output the Current bill amount inputted minus Amount already paid in the month divided by the sum result
So, the idea is to get a list of people staying at the property at Date inputted with the proportion amount each of them should pay based on the Current bill amount minus the Amount already paid in the month, for that specific room/property.
I unfortunately haven't managed to make this work.
Can anyone help me with this?
Thanks,
Johnny
