Formulas

I have a column with a value in seconds which I can easily set up as a mirror showing duration using the formatting option. I now want to concantate that with an integer so I end up with something like “234 - 01:04:32” where the formula is CONCATENATE({integar field}, " - ",{duration field}) trouble is that the latter field converts itself back to seconds! How I can I stop it doing that? Many thanks

I am trying to create one record for each Monday of the year. The following formula is one that I found on this forum. It works, of course, but I have to enter the date {Date} which defeats the purpose. Is there a way for it to point to the previous record’s date field? (Like, last week’s Monday plus 7?) IF(1-WEEKDAY({Date})<=0,DATEADD({Date},1-(WEEKDAY({Date}))+7,‘days’),DATEADD({Date},1-(WEEKDAY({Date})),‘days’))

Hey there! I have to make a very simple formula but having problems on how to write it correctly I have two date fields (arrival, depature) I have a Total days field ( calculates the date difference) -working I have a price field that shows price for the spesific amount of days I have the formula: IF({Dager} = 1, "590, ") & IF({Dager} = 2, "690, ") & IF({Dager} = 3, "840, ") & and so on all the way to 21 days ( everything here is working fine) BUT, after day number 21, the price is gonna add 20 for each day. It’s going to be something like this: If({dager)>21, ({dager},-21*(20),1490, ") Example with 29 days - If({dager)>21, ({29},-21*(20),1490, ") Here the days over 21 is 8. Then multiply 8 by 20, then + 1490 (price for 21 days) And the total is then 1650, which is correct. When I do this in the calculator I get the correct price. When I did this in Caspio Cloud, it also worked. So my problem is just how to put in all the () and , and “” or {} at the right places

We have multiple remodel projects running at the same time with similar tasks for each project. Is there a formula that would let us know that is time to order a certain item (flooring) or execute a specific task? Example: flooring takes 5 weeks from time order is placed and it would be great to get a notification/alert reminding us to place the order 6-7 weeks prior to deadline. We are currently using free version, is upgrade to blocks needed? Thank you.

Hi everyone! I’m trying to make a formula where I can only see the date of the project is about to expire. I don’t know if that’s something it can be done, but it would really help me because I don’t want to be seen always all the dates, but only the ones I’m about to work. I hope someone can help me. Thank you in advance!

I am simply trying to divide two numbers but am receiving an Error for a result. The numerator is a # column were the denominator is a formula. Numerator: {{Lease Total}} Denominator: IF(AND({Leasing Co.}=“Ascentium”,Credit=“E”),.0499,IF(AND({Leasing Co.}=“Ascentium”,Credit=“D”),.045,IF(AND({Leasing Co.}=“Ascentium”,Credit=“C”),.0325,IF(AND({Leasing Co.}=“Ascentium”,Credit=“B”),.0295,IF(AND({Leasing Co.}=“Ascentium”,Credit=“A”),.0285,IF(AND({Leasing Co.}=“Ascentium”,Credit=“P”),.0279,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“E”),.0405,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“D”),.0405,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“C”),.0305,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“B”),.0291,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“A”),.0281,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“P”),.0271,IF(AND({Leasing Co.}=“FDGL”,Credit=“E”),.0399,IF(AND({Leasing Co.}=“FDGL”,Credit=“P”),.0265,IF(AND({Leasing Co.}=“FDGL”,Credit=“A”),.0275,IF(AND({Leasing Co.}=“FDGL”,Credit=“B”),.0

I am trying to make a grocery list that would take the price of an item ($0.88) and multiply it by the quantity needed (2) so that it outputs the total amount ($1.76) I have tried the formula Price * Quantity which hasn’t worked. How would i create a formula to output the total price as stated above? Screenshots

Hi! I’m trying to create a formula where my Primary Field is a number that I need to pull from a URL link: Currently, I am using the formula RIGHT() to tell the formula to find the last three characters from the right of the URL link, which won’t work once we get to 4 digits, or if for some reason the URL structure changes adding an extra character at the end. Is there a way to find only a number from a URL and return it as such? Thank you.

I have a table “Ethnicity”. For the purpose of this post, I’ll use Asian ethnicities as an example, but my Ethnicity table will also include ethnicities of other racial groups. On this table are entries like “Asian Indian”, “Bangladeshi”, “Bhutanese”, “Taiwanese”, “Chinese”, etc. I have another table “Contacts” which contains a column that links to Ethnicity. Each record in Contacts can be linked to multiple ethnicities. I now want a column that marks whether a record’s ethnicities fall under larger racial groups. For example, if the record is linked to “Taiwanese” or “Chinese” or both, I want the record to be marked “Yes” under the column, “Asian” What’s the best way to accomplish this?

Hello! I need some help of this community because I lack the knowledge to develop formulas. I trying to create an automated financial system using some linked tables inside a base. I have expenses categorized in a field with the options ‘‘Credit’’ and ‘‘Debit’’, which I use to separate the payment methods. The expenses also have a ‘‘Date’’ field and a ‘‘Cost’’ field. I want to create a field using a formula that brings me the data from the ‘‘Cost’’ field if the expense is payed in ‘‘Credit’’ within a specific month (actual month) in order to separate this values so I can link them to another table and use a simple SUM formula to calculate my total expenses in Credit for the actual month. So its something like: If method of payment = Credit, If date = actual month, ‘‘cost’’ I know I could simply use a filter view to see only ‘‘Credit’’ records, but as the data will be linked to another table for other operations, I cant. I would be extremely grateful if you could help me in this!

I have two tables, and I want one to display a sum of a column if it meets Table 1 House # | Free Nights Total | Total Nights Used| House 1 | 5 | 4 | House 2 | 3 | 2 | House 3 | 9 | Table Two Guest Name | House # Used | Free Nights Used Guest 1 | House 1 | 2 Guest 2 | House 3 | 5 Guest 3 | House 1 | 4 What Im looking for is the “Total Nights Used” column in table 1 to total up each Houses used Free Nights Hopefully that makes sense??? And apologies if this question has already been answered!

I’m trying to insert a timestamp into a field when a value on a record changes. I’ve got this but it’s not really updating like I was expecting. IF(Stage=“3. Fruiting”, NOW(),0) It seems to be updating randomly. Any ideas?

Hi so I’m trying to write a formula to concatenate a name and a date but the date keeps coming back in long format. Is there anyway I can just have the short date (i.e. 09/05/2019) shown?

Hello! I don’t know how to do this. To simplify, let’s say I have 2 tables, one called topics with a list of article topics, and one called content with a list of articles, with a single select field categorizing the articles by country. In the topics table, I need a look up field of the articles per country - for now I just have one lookup field that shows all the articles, in all countries. So I need some sort of formula that would say “only show me the articles from the articles table which have the single select field of [country]=Belgium” Hope this makes sense - and hope it’s possible! Thank you :slightly_smiling_face:

Hi I keep getting an error when I use this formula: DATETIME_FORMAT({DATE ISSUED},‘MM-DD-YYYY’) This is what I am trying to do: Make a second formula field using DATETIME_FORMAT({DATE ISSUED},‘MM-DD-YYYY’)^ and then copy the values for all your imported CSV entries into your original “Date Issued” field. The formula field you’re using now will then produce the result you’re after. After you do this you can delete your second formula field, and convert “Date Issued” into a date field." Basically, I want to convert a text field with this date format: 1955-02-21 To this date format: Fri, November 19, 1976 Here is a screenshot of my base setup, the first DATE ISSUED is a different formula. It’s the second DATE ISSUED formula that I am trying to get working: I would appreciate any help. Thank you, Mary

Hi there! I’m trying to determine the count and order date of specific items needed for students at many classroom sites, each site completing multiple projects over a season of classes. This calculation would be based on these inputs: units per each of a standard list of materials needed, per individual project, e.g. wheels for three different projects that use anywhere from two to six wheels. (Projects Table) List of sites, with number of enrolled students (Sites Table) Program Start and End Dates (Sites Table) Current inventory of materials, e.g. wheels in stock (Inventory Table) Lead time needed for delivery of materials from order date (Inventory Table) And result in three outputs (destinations): total units needed per item, subtracting items in stock (Sites Table) Total units needed, per item (Inventory table) Dates Needed (Sites Table) This may be bigger than asking the community, happy to work with a pro, lemme know! …and then turn this into a report, which isn’t critical

I regularly pull reports from multiple systems that include product IDs. These IDs are always a series of letters followed by numbers (e.g., ABC 123, WXYZ 9876). The number of letters can vary between 3 and 6 characters and the same goes for the numbers. The problem I’m having is that in one system, there is no space within the ID (e.g., ABC123) while in the other system, there is always a space between the letters and numbers (e.g., ABC 123). Since the number of characters can vary, I’m having trouble finding a formula that basically looks at the cell and adds a space when it finds a number. Any help is appreciated! Update: If it helps, this formula is what does what I’m looking for in Excel =TRIM(REPLACE(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&“1234567890”)),0," ")) TRIM, REPLACE, and FIND all exist in Airtable but I’m not sure how to use FIND to search for any combination of items (e.g., 1 OR 2 OR 3…) like you can in the above Excel formula.

Hallo I am trying to apply a multiplier to a lookup field based on a checkbox or multiple selection. In short, if the checkbox is checked, a lookup would be multiplied by a certain amount (for example 200), else it would not. Similar to: IF ((checkbox)=1, LOOKUP*200, LOOKUP) Another possibility would be: IF ((multiple select)=A, LOOKUPx200, (multiple select)=B, LOOKUPx300,(multiple select)=C, LOOKUPx400) Hope this is clear - strong text is this feasible? best Luca

Hi @Justin_Barrett I woundered if you could give me some advice on this post. It was suggested that I use Parse Date, but I could not get it to work: Issue With Date Formula Formulas Hi I keep getting an error when I use this formula: DATETIME_FORMAT({DATE ISSUED},‘MM-DD-YYYY’) This is what I am trying to do: Make a second formula field using DATETIME_FORMAT({DATE ISSUED},‘MM-DD-YYYY’)^ and then copy the values for all your imported CSV entries into your original “Date Issued” field. The formula field you’re using now will then produce the result you’re after. After you do this you can delete your second formula field, and convert “Date Issued” into a date field." Basic… Thank you, Mary

Hi, I’m using the Recipe Organizer template. I would like to add another view that calculates the total number for each ingredients. For example: Recipe 1 - Big Salad - consists of:6 Cucumbers and 8 Tomatoes. Recipe 2 - Small Salads - consists of:3 Cucumbers and 4 Tomatoes. The user needs to prepare 10 Big Salads and 4 Small Salads. The output should be 72 Cucumbers and 96 Tomatoes. Is it possible? Thank you, Sam

Hello fellow community members, I have a sheet (Sheet1) which has multiple columns - say ConfigValue1, ConfigValue2. Then I have a column called Result that calculates a result using those values and values (Logic1, Logic2) from Sheet2. In Sheet1:Result, I want Sheet1:ConfigValue1 x Sheet2:Logic1 + Sheet1.ConfigValue2 x Sheet2:Logic2. Note that the logic could be a bit more complicated. I could put the logic right into Sheet1, but the issue is that we want to have a separate interface for entering logic. Thanks!

Hi everyone, Looking for some help on this case … The user enters any one value (ISBN Code or Author Name) in the form and gets a result (Book Details corresponding to the author or ISBN Code) displayed in view link. I have created a MASTER CONTENT table A, with columns naming ‘ISBN Code’, ‘book name’, ‘pages’, ‘author’, with "ISBN code’ as the primary field. From another table B, I have created a USER SEARCH form, in which "ISBN code’ column is linked with table A. IN the form, the user enters the ‘ISBN code’ and in return in VIEW of the table, A gets other values corresponding to ISBN Code like Book name/Author Name etc. from Table A using Look Up. Now I want to create an option in the form in which a User has the option to either fill the ‘code’ or ‘author’ & he can similarly view all the details from table A? How to pull and Display values by entering either - in the same filed or two separate fields in the Form? Thanks for taking time and look forward to some comments.

Hi, I’m trying to create a formula that flags (with a 1 or 0) when a certain 5 digit number code is present in either a linked record field, or a field of arrays (whatever is easiest). For example, I was attempting: ``IF( OR( FIND(‘68100’,{Combined Codes & Tags (all)}), FIND(‘41100’,{Combined Codes & Tags (all)}), ), 1,0)`` But this only works when I have two possible options of codes. I’m actually looking for the presence of 5 different codes. What is the best way to do this?

I am creating a new table, say for example with daily price of gold, I have three fields, Date, Price and Change in Price I want the field “Change is Price” to be the difference in price since yesterday, to be calculated automatically. In spreadsheet, I could do it as =b2-b1 Is it possible to do this in Airtable, Please help. Thanks in Advance.

Hi Community, I set up a form to prefill using the URL. The field it autofilled was a drop down select look up from another table. I am 99% sure I tested the form an it worked fine. I have now tried to do a similar lookup and cannot get it to prefill. So I went back to the place I’d originally done it and now that is not working either! Has something changed in airtable? Or is there another explanation? Please help if you can! Best, Nathan