Re: Use date-fields to calculate rent costs

754 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer


Case: I am making a booking form for a hotel that has different rates per night depending on the month.

Problem: I can’t seem to calculate the total costs when the check-in and check-out are in different months.

Possible solution: If I am able to generate the days between the check-in and check-out date, I could link those to a sheet where I have every day of the year with a corresponding price.

Thanks in advance

3 Replies 3

I think the attached demo base does what you want. In pseudocode, it implements the following algorithm:

If (CheckIn and CheckOut are in the same month) {
.... CostOfLodging = Diff(CheckInCheckOut) * Cost[Month]
.... }
else {
.... CostOfLodging =
........ Diff(CheckIn,EndCheckInMonth) * Cost[CheckInMonth] +
........ (Diff(CheckOut,BeginningCheckOutMonth)+1) * Cost[CheckOutMonth]
.... }

I’ve made the assumption prices vary month by month; that is, that you don’t charge premium rates during certain weeks or weekends.

The solution as implemented makes use of two additional tables, Months and RateCard. Months contains 12 records per year, with three data-entry fields per record: Month number (1 - 12), number of days per month (it auto-corrects February, in case you forget a leap-year), and the per-night price charged during that month. Each month also needs to be linked to the appropriate RateCard record, of which there is one per year. In the demo, records 1 through 12 are linked to RateCard record ‘2017’; next year, you would create 12 new month records, also numbered 1 through 12, and link them to RateCard entry ‘2018’.

To use the base, you simply enter CheckIn and CheckOut dates plus the RateCard. The system extracts the appropriate per-day cost for CheckIn month and CheckOut month; if the months are the same, it calculates the number of elapsed days and multiplies that times the per-day cost. If CheckIn and CheckOut are different months, it extracts the number of days in CheckIn month, calculates the elapsed days in CheckIn month times the cost for that month, does the same for the number of days in CheckOut month (plus 1, to capture the day between CheckIn month and the first day of CheckOut month), and sums the two subtotals.

The base as implemented does not support 1) stays of greater than a month, 2) stays that straddle a RateCard (that is, from December to January), or 3) error-checking that CheckOut is later than CheckIn. All three could be added relatively easily.

I hope this gets you started, at least!

Wow that sounds fantastic, I will try to implement it asap. Thank you for
your excellent support.

This is exactly what I am looking for. Could I ask, how would you go about it if you have certain months such as January, where 1st to 9th of January, I charge higher than 10th to end of January?