Formulas

I have a product the changes cost frequently and I want to track this cost change in one field. This original formula has been working for me but now I am trying to figure out how to calculate the cost between two dates. For example: Cost before 1/1/2020 Cost between 1/1/2020 - 1/1/2021 Cost after 1/1/2021 IF({Date}, IF(IS_BEFORE({Date}, DATETIME_PARSE( ‘1/1/2020’,’’)), VALUE({Product}) * 0.62)) + IF({Date}, IF(IS_AFTER({Date}, DATETIME_PARSE(‘12/31/2019’,’’)), VALUE({Product}) * 0.53)) + IF({Date}, IF(IS_BEFORE({Date}, DATETIME_PARSE(‘1/1/2021’,’’)), VALUE({Product}) * 0.53)) + IF({Date}, IF(IS_AFTER({Date}, DATETIME_PARSE(‘12/31/2020’,’’)), VALUE({Product}) * 0.48))

Hello! I’m new to airtable and trying to figure out what formula will take a date field and then subtract approximately 14 days to the next closest specific weekday. Details - Current Formula only calculates date 2 weeks prior: DATEADD({Event Date}, -2,‘week’) I need to back up from the “Event Date” approximately 2 weeks to pick-up product for the event, but pickups always need to be on a Wednesday. Any help is much appreciated!

Hi, I hope I’m posting in the right place. Brand new to Airtable. My goal is to set up a CRM for networking that will remind me when to reach out. My plan: Each person (record) gets a numeric rating to indicate how often I want to be in touch with them. I will have a field where I manually enter the date of most recent contact. I want another field that will calculate the next date needed, based on the rating. So, if the rating field has a 1, the field with the next contact needed will be 30 days, if the rating is 2, 60 days, etc. I’d like conditional formatting to turn the record a different color if I’m overdue. I am really not well versed with formulas or functions so I have no idea how to set this up. Can anyone help me? Thank you!

I have created a random number generator string {RNGS} using this formula: VALUE(DATETIME_FORMAT(AddDate, ‘mm’)) # Autonumber # Autonumber. (Note: I have purposefully substituted # for an asterisk/mutliply function in this post) A sample result is 238425 (33 # 85 # 85). I’d like to use just the first 4 digits for my random number field I’ve tried both RIGHT(VALUE(DATETIME_FORMAT(AddDate, ‘mm’)) # Autonumber # Autonumber,4) and RIGHT({RNGS},4) Both of these generate the #ERROR! message. Please help!

I had a functional formula formerly from a template, and then I went and copied tables between bases, and lost the actual formula in the field. What I’m trying to recreate is a conditional formula that returns a status on character count based on the social channel selected in another field. Basically: If CHANNEL is Twitter and CHARACTER COUNT is between 71 and 100, then :ok_hand: , otherwise :exclamation: :exclamation: If CHANNEL is Facebook and CHARACTER COUNT is between 40 and 80, then :ok_hand: , otherwise :exclamation: :exclamation: If CHANNEL is Instagram and CHARACTER COUNT is between 138 and 150, then :ok_hand: , otherwise :exclamation: :exclamation: If CHANNEL is Linked In and CHARACTER COUNT is between 25 and 140, then :ok_hand: , otherwise :exclamation: :exclamation: Thanks in advance!

When trying to do the very simple formula of CONCATENATE({First Name}& “ “&{Last Name}) it won’t submit, simply saying invalid formula. I have read another thread that said this is what to input to have a space between the two values, and that I also shouldn’t need to use the concatenate prefix, I’ve tried many things and nothing works. Any help?

Hi all, Can you please help me make a formula? I have records with the following (relevant) columns: Commission Processing Date (date format) Income ($ format) 1099 ($ format) After a certain date, my Income needs to be adjust by -15% and that result should be listed under my 1099 column Thus, I would like to do the following formula for my 1099 column: If Commission Processing is on or after 01/01/2020, then Income should be multiplied by 0.85. Otherwise, 1099 remains the same number as Income. Can’t figure out how to do the date part of the IF formula. Thank you!

Hi everyone, happy new year :hugs: I have a question regarding using LAST_MODIFIED_TIME() in a formula. Is there a way to use LAST_MODIFIED_TIME() on another formula field type? I have formula field “Stage”, and I want to write a formula that recognise that there is a change in “Stage”. Can you please help me achieve this? Thank you, Pjero

I’ve built a simple tracking for my recurring expenses and I want one of the fields to show the current month with the information in the field next to it. I can’t seem to find how to do that.

I have a database of clients who need to pass an annual certification exam. I have a Client table, that is linked to a Certification table, with multiple Certification rows for each attempt at the exam, with a unique date & result for each row. For each Client on the Client Table, I’d like to select the row On the Certification Table with the most current Test Date, so I can easily see their current Certification status. The Client table key is Client Name and the Certification table Key is Client Name + Testing Date. I can change these if needed. An example of the Certification Table below. On my Client Table, I’m creating a view with lookups to several other Tables in my base to get a complete view of Clients Status. When finding info on the Certification Table, I’d like to select the row that matches each Client on the Client table, with the most current date on the Certification Table, so I can easily see their current Certification status. CERTIFIC

Hi I’m using an integration via Integromat to pull in some records for a report function. I have lots of records in my base for different names (Name) and have formatted a field called Period which is the date created in the format “YYYY/MM”. So I may have: Dave / 2020/11 / Some Data Dave / 2020/12 / Some Data I need to return all records for Dave in a given period. I can use FIND(“Dave”, Name) to return all records for Dave which works great. What I really want is WHERE period = “2020/12” How can I create a formula to search the base by Name and Period? Thanks

I’m trying to come up with a formula where if {Date Time} matches {Date}, then {abc} is displayed. Pls advise thank u

IF({Unit Type} = “Bins”, {Units Complete}/32, “Feet", {Units Complete}/53) Proper formatting so it works?

I have a pretty simple list. It has a main category with a list of items. A Second column that is dates. The list is approximately 200 items. I need the dates to start on a specified date and populate +1 day from the date of the item before. Not too complicated. The wrinkle is that I need it to skip the last day of the month. I don’t know if this is possible. I repeat the process and the start date varies. Excel works, but I really like the connectivity of airtable and loading into my calendar. Any help would be greatly appreciated. Thanks in advance.

I have Total pay (Currency) earned in one column and duration field in another. How do I calculate my hourly rate per record? I have only seen how to calculate total pay if you already know the hourly but in this case, I already have the duration and total payout but just need to find out what that means per hour.

Hello, I have one table that has both insurance and investment products. The Product field splits these out. They share other common fields such as “Payment/Investment_Amount” and “Payment_Frequency” Payment frequency holds single select options of weekly,biweekly, bimonthly, and monthly. I am trying to create a statement specifically to calculate annual premium paid for insurance products. This is a conditional IF statement as the product has to equal insurance, then if it does, premium amount * frequency to equal annual payments. Can someone assist? Much appreciated! Thank you, Ben

Hello, I am using a single select column with options as predefined numbers, 1 to 6 - representing hours (time sessions). I would then like to multiply the selected option with X (day rate) and output the result in a currency column (total cost). I can’t get it to work. Is it possible? I would like to use a single select instead of a number field to input hours.

My records are created from automation, representing tasks that were started. I have a created date field that represents the start date of the task. I also have a single-select field that staff use to mark a task completed, among other statuses. I want to be able to know when the status has been marked completed and show that date as a record field so I can then compare and check if tasks are completed the same day they were started, and if not, how long they took to complete. I’m not even sure this can be done or if there are other ways to get to what I want but I appreciate any advice! Thank you!

Hello, how do i create a formula to select records that have a specific value in a linked field. Note: The linked field allows multiple linked records. My guess is that it is something like: IF field has value in array.

I have 2 switch formulas that work off different fields and they are combined by a 3rd if statement that just says if this one isn’t blank return this one, if it is return the other. is it possible to put them in one field? if so how might i go about parsing it? REGISTRATION STATUS FORMULA: SWITCH({SERVICE STATUS}, ‘INQUIRE’, “NEEDS ATTENTION”, ‘UNKNOWN’, “NEEDS ATTENTION”, ‘OUT OF SERVICE’, “OUT OF SERVICE”, IF(AND(MONTH(TODAY())= MONTH({REG MO}),DATETIME_DIFF({LAST INSP},TODAY(),‘DAYS’)<-150),“DUE FOR INSPECTION”, IF(MONTH(TODAY())!= MONTH({REG MO}), “NOT DUE”,“READY TO RENEW”))) REGISTRATION MOVE FORMULA: SWITCH({REG PIPELINE}, ‘CANT BE INSPECTED’, “CANT BE INSPECTED”, ‘SENT TO TAX OFFICE’, CONCATENATE(“TO TAX OFFICE” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘BACK FROM TAX OFFICE’, CONCATENATE(“RETURNED” & " " & DATETIME_FORMAT({PIPELINE MOVE}, ‘MM/DD/YYYY’)), ‘MAILED TO MANAGER’, CONCATENATE(“MAILED” & " " &

Dear fellow airtable wizards, Today my quest has put me up to a challenge I hope you guys can help me with. Short backstory: We offer courses which people can subscribe to (their record enters the base using forms) The first course starts the 3rd of January. Whenever they register we send them an email (using automations). In this email we confirm that the first course is on the 3rd of January. I came up with the following formula: IF(IS_BEFORE({Created time},{Start date}), DATETIME_FORMAT({Start date},‘DD MMMM’)) Now comes the tricky part… If a person registers a day after the 3rd of January, I want my formula to let them know their first course starts a week later (the 10th of January). If they register after the 10th, they start on the 17th… and so on. I’m looking for a formula that knows how far apart the {Created time} and the {Start date} is, so it can add 7 (or 14 or 21…etc.) days to the {Start date}. I know th

Hello Everyone, I would like to create a formula that defines a range of dates depending on the due date within next Monday: as follows filter content that due date is coming on till next Mondy only then resets again on Tuesday and shows me what is coming till next Monday. Thank you Guys

I’ve got a table lookup step in Zapier that goes through Airtable and tried to locate a record from a dynamic ID that also resides in the Airtable’s rollup. Here’s the formula I have now in Zapier: Here’s the Rollup in Airtable: I’ve tried removing the double quotes from the formula and have confirmed that I’m searching in the right table. Why are the tests coming up without a match?

Hi! I’m trying to see if there’s a way I can do this. I know Airtable is not a “spreadsheet” and that typically you cannot use different formulas per row/cell because of the database situation. I’m a complete newb to airtable, so I’m still learning the intricacies to see if it’s something I can use in my role. I’m putting together a checklist for a large event that has due dates- start and end dates included in the table. In my google form/excel sheet I have formulas created so that all I have to do is put in the first day of the actual event into a column, and then another column has the # of months, days or weeks out that we need to start working on that particular task for the event. I had to go in each cell to change the formula for the specific task for those that were 1+ months out vs. those that needed to be start 2 weeks out, etc. I’m using this formula in the column (the DATE and Project Start are the columns I’m having it pull data from): DATEADD(Date,-

Hi all, I am importing a data set that stored some numbers as roman numerals. Is there a formula or other method to convert these to numbers?