Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Calculate Rates based on duration variables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

1
2257
1

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 21, 2023 06:45 AM

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

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 24, 2023 08:24 AM - edited Oct 24, 2023 08:25 AM

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.

Reply

9 Replies 9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 22, 2023 05:56 AM

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.

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)})))`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 24, 2023 04:36 AM

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!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 24, 2023 06:42 AM

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 24, 2023 08:24 AM - edited Oct 24, 2023 08:25 AM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 24, 2023 12:48 PM

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...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 25, 2023 03:46 AM

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?

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 04, 2023 07:49 AM

@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.

- Checkbox for "Civic Use" checked (this would
- Checkbox for "Parking Lot Only" checked
- 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06, 2023 12:26 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 12, 2024 08:50 AM

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

- Checkbox for "Parking Lot Only" checked
- 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