Skip to main content

Hello. My first post here, so please be gentle 🙂


I have the following scenario


The price for renting Video Equipment is different based on how long you rent it …


1-6 days, price A

7-27 days, price B

28 days +, price C


Each of the prices is rental for ONE DAY (24 hours)

So the Price is multiplied with the number of days … so far so good,


The Prices are calculated in the Equipment Tab

The amount for the Rental is calculated in the Rentals Tab


I have managed to come up with a nesten IF statement, that prints to a STATUS field, and that works just fine … is prints something like “Singel day Price”, “Weekly Price” and “Monthly Price”

It goes like …


IF(DATETIME_DIFF({End},{Start},“d”)<7,“Daily Price”,IF(DATETIME_DIFF({End},{Start},“d”)<=27,“Week Price”,IF(DATETIME_DIFF({End},{Start},“d”)<=90,“Month Price”,"")))


pardon my translation 🙂


Now I try to do the same for the actual price.


In the Equipment database all the prices are created by a calculation

as an example … the price for an single day is calculated as (({PriceOfPurchase}/10)/7)*1.25 … formatted as Integer


I use lookup logic to bring these values into the Rental database Tab

I have also calculated the DAYS for the rental


So …


IF(Days<7,{Daily Price},IF(Days<28,{Week Price},IF(Days<90,{Month Price},"")))


and I end up with something like …


356.3686745875872

The 356 is supposed to be the price … and the number of digits after the point/comma is not constant


With my current knowledge about AirTable … I am not able to get rid of all digits after the comma … and believe me … I have tried …


I am also not able to use the result in any calculation, as AirTable does not recognize it as a number.


Please help 🙂


Kind regards Nils

Welcome to the community, Nils! :grinning_face_with_big_eyes: My gut says that your problem can be solved by removing the empty string at the end of your price calculator, like so:


IF(Days<7,{Daily Price},IF(Days<28,{Week Price},IF(Days<90,{Month Price})))

Whenever you omit the final item in an IF function, Airtable automatically inserts the appropriate null value based on the rest of the function. By using an empty string, though, it forces the result to be a string, which is why you’re having problems with other things not seeing it as a number.


With that done, you can wrap the appropriate math function around the IF collection to nix the values after the decimal point. Here’s an example with ROUND:


ROUND(IF(Days<7,{Daily Price},IF(Days<28,{Week Price},IF(Days<90,{Month Price}))), 0)

Thanks a lot, Justin. You solved my problem 😀


I did not have do use the ROUND statement, as I could set this directly in the FORMATTING of the cells.


Again … Thanks a lot


Kind regards Nils

Reply


Terms & Conditions