Calculate Rates based on duration variables

[cch-online]

Hello. Still fairly new to Airtable and working to better understand formulas. I'm looking for input on the following.

We have a rental space where rates are set based on duration (up to 4 hours, up to 6 hours, all day). I've set up a table for Rental Rates. Separately, I have a table for rental details, and this includes a duration field ("1st Day Duration") calculated based on rental start and end times.

I'm trying to figure out how to create a formula that calculates the correct rental rate based on the duration and then pulling the correct rental rate. So for example, if a duration is 3 hours, the fee would be $x (since that's the "Up to 4 hours" fee). But if the duration is 7 hours, the fee would be $x since it would be a "Full day" rental.

One other variable. I'm not sure how to plan for future rental rate changes, so that when rates change existing entries do not change (only newer entries). I'm assuming this starts with a date variable tied to rate increases. In the Rental Rates table, do I have separate fields for rate years? If so, do I need to account for this in the above formula? Or is there some other strategy for accounting for rate changes?

Thanks very much,

Mike

[Databaser]

I'm trying to figure out how to create a formula that calculates the correct rental rate based on the duration and then pulling the correct rental rate. So for example, if a duration is 3 hours, the fee would be $x (since that's the "Up to 4 hours" fee). But if the duration is 7 hours, the fee would be $x since it would be a "Full day" rental.

Try:

VALUE(IF(Duration, IF(Duration>21600, "1000", IF(Duration>14400, "500", "300"))))

Duration field stores it data in seconds, so 6h = 21600 ($1000), 4h = 14400 (=$500); rest is less than 4h and lowest rate ($300). You can change the duration and rates of course. 

One other variable. I'm not sure how to plan for future rental rate changes, so that when rates change existing entries do not change (only newer entries). I'm assuming this starts with a date variable tied to rate increases. In the Rental Rates table, do I have separate fields for rate years? If so, do I need to account for this in the above formula? Or is there some other strategy for accounting for rate changes?

You could have another table with the rates. Eg: date from, date until, rate full day, rate >4h, rate <4h. Then link your rentals to the correct rating field, lookup the 3 rates and refer your formula to those field. That way, you don't change old rentals. 

Try:

IF(Duration, IF(Duration>21600, {rate full day (from current rate)}, IF(Duration>14400, {rate >4h (from current rate)}, {rate <4h (from current rate)})))

[Jean_Francois_B]

The calculation could be managed with a SWITCH or IF formulas

For the price change you would need the "rates" table with automations to link spaces if you change your price weekly/monthly etc then the automation will link records based on dates and return the correct price.

Good luck! 

[cch-online]

Thanks very much for your suggestions, Jean Francois. Much appreciated. I'll have to do some additional research to see if I can figure that all out.

[Databaser · Accepted solution]

I thought I already answered this one, but apparently it vanished 🤷‍♂️

I understand that you have a table with your rental rates and a table with rental details. You'll no doubt have linked those 2 so you can lookup the current rates in your rental details record. For new rates (for new rentals) I would suggest creating new records in the rental rates table (maybe use start and end dates). 

In the rental details table, try this formula:

IF(Duration, IF(Duration>21600, {rate full day (from current rate)}, IF(Duration>14400, {rate >4h (from current rate)}, {rate <4h (from current rate)})))

Note that duration stores it value in seconds. Replace "Duration" with your duration field (so between { } ). The {... (from current rate)} are lookup fields from your rental rates table. 

[cch-online]

Thanks for your input @Databaser. Trying to wrap my head around your recommendation, I'm wondering if I have my rates table set up properly (with variations as records and date variables (years) as fields. Or do I need to swap that around so that years are records and rates are fields?

 

And then in the rental table...

[Databaser]

I would create a new record for every year and put the rates in fields. In the rental table, you can use a linked record to tie a rental to a correct rate period and then lookup the different rates. You can then do all the calculations in the rental table. Adjust the formula's to the different duration fields. 

I also wonder: for how many days do you have separate fields? Could it be easier to have duration for 1st and last day duration and the other days just as full days?

[cch-online]

@Databaser Sorry for the delay in responding to thank you for your input on this. I was able to make that formula work for the base rates. I've since been trying to see if I could figure out on my own the next step, which is to apply logic to the formula to apply rates based on certain other parameters. And I've not been successful. Might you be able to assist further?

There are three variables in the Rental table that would mean different rates (beyond the base rates) would apply.

1. Checkbox for "Civic Use" checked (this would 
2. Checkbox for "Parking Lot Only" checked
3. Checkbox for "Fundraising/Revenue Event" checked

Any one of these would then mean I'd need to reference different rate fields in the Rental Rates table.

Is this something that's best accomplished using nested IF statements? Or is a SWITCH formula a better option? I tried going down the IF path, but my beginner skills haven't been enough to figure it out.

[Databaser]

Could you walk me through what the price setting (can be fictional) is with those options? I think I would separate the calculation for the base rates and the one for the 3 check boxes, and then add them up.

[cch-online]

I've got the first of the three variables mentioned above figured out. It's the second and third I'm struggling with:

1. Checkbox for "Parking Lot Only" checked
2. Checkbox for "Fundraising/Revenue Event" checked

Here's our rates table...

Rows 2 and 3 are referenced in a separate table based on certain conditions.

Then, there are multiple columns in the main table I'm working with that have formulas pulling the correct amounts from that rates table. Each day booked has a total...

Then there is a Fees Total column that adds up each of daily fees that exist.

I'm struggling with figuring how to pull in rates for Fundraising or Parking Lot Only if they exist, as those would override the time-based rates per day.

Thanks again for any insight/direction