Formulas

dear all this is (should be) so simple, but yet cant get it to work New Law regulations in Austria (01.01.2019) :confused: Gig worker MUST be calculated per day If someone workes the nightshift this woud have to be divided onto the 2 days, according to the worked hours, this all per month, of course If “Max” workes on 12.02 18:00 - 13.02 04:00 what would be the solution to this calculation? how many hours “Max” worked on 12.02 and how many on 13.02.? hier is a link to a test base, if any one wants to play with calc und formulas, i have found no solution. yet. Airtable

Hi there I have one field with value subdomain(.)domain(.)com, domain(.)com and etc. I need to get N fields separate source value by (.) The result will be for subdomain(.)domain(.)com field 1 subdomain field 2 domain field 3 com for domain(.)com field 1 (empty) field 2 domain field 3 com Thank you

Hello! Trying to create a formula to notate when 1 of 4 columns has text in it. For a project to be “complete” there needs to be date in either column A, B, C or D. I would love a column that would give me a check mark for it. Thanks, Eileen

Perhaps I’m not searching correctly, but I’m hoping the community can help. I’m looking to have a formula that concatenates multiple columns based on whether or not one cell contains a specific character, such as a question mark ?, but also contains other text. My formula already has a nested IF that is based on whether or not another cell is blank; see below: IF({UTM Content}="","",CONCATENATE({Link To},“string”,{UTM Content})) So, I want 2 results: If cell {Link To} contains a ? somewhere in it, then I want “string” If cell {Link To} does not contain a ? somewhere in it, then I want “string2” Appreciate any help!

Hi Airtable guru’s! Here’s what I’d like to do that I haven’t figured out. I’m teaching a series of classes that will have tests that are being held in Google Forms/Quizzes. I’d like to have these scores come into AirTable in one sheet to summarize all of these scores. Like a grade book. Email address | Test 1 | Test 2 | Test 3 I’ve stumped my self. HELP!

Hi - super basic formula question. Much forgiveness please :pray: So I have a bunch of tasks listed in a base as rows, with one column being “status” that has various dropdown options, including one called “Done”. I’d like the formula to return a 1 when the status “Done” is true and a 0 when it’s false. I have this: IF(Status=Done,1,0) But I get angry red text telling me to try again. I’ve tried with spacing, quotation marks, tried to look up more on IF statements, but can’t see what is probably a very simple mistake. Might be the late hour. Lil’ help? Thank you! C

This formula from the blog produces the result with at time: 9/30/19 12;00am What part of the formula needs to change to eliminate the time? DATETIME_PARSE(DATETIME_FORMAT({Period},‘MM/DD’)&"/"&YEAR(NOW())) Thank You

Hi – I’m building a Street Team in one table in Airtable. One view shows “assignments” which plots out what concert each street teamer is working on. Once they are done, they submit an Airtable form with their name, photo proof & the concert they worked on. This goes into a “review” view, which allows us to review to make sure they did the work. Since “Assignments” (generated by us) and “Review” (generated by the worker) need to match, I’m trying to figure out a way to match them. So if we assign Street Teamer A to work Concert X, then we need to know when Street Teamer A submits their proof for Concert X. So I created a match code field with the formula {name}&" - "&{concert}. In the example above, we’d know it’s a match because both OUR assignment AND their submission have the same match code (StreetTeamerA - ConcertX, in this example). But how do I write a formula that then can determine whether that Match Code is unique? Meaning, if there i

Hi! I’m hoping to create a ranking relative to the category an item is in and can’t figure out the best way. My dataset includes items in multiple categories, ranked 1-1000 overall based on sales. I also have a “Rank in Category” field that ranks the items in each category, and category totals are anywhere from 37 to 242. Is there an easy way to make a fractional calculation relative to each category? For example, item 4 overall is actually 1 for its category of 92 items. The output can be 1/92 as a decimal, I’m creating a scoring system from outputs and that will help normalize it. Thanks!

Hi, I have used ARRAYJOIN in rollups many times, but never tried it in a regular formula field, and I’m having some trouble. I was trying to use ARRAYJOIN to create a string I can parse with DATETIME_PARSE. I’ll simplify the formula I wrote here to get rid of some complicating factors. The formula I tried was: ARRAYJOIN([“2019”,“01”,“01”],"-") Airtable didn’t seem to understand square braces, curly braces or brackets, but I also couldn’t figure out any other wrapper for the array that would get it to work. All of the help articles I found are intended for rollups, where Airtable generates the array automatically. Anyone know how to do this? Thanks!

Question: I have a collaborator field with numerous collaborators per record, and I was hoping to add a field that would add up how many collaborators there are listed in that record. How would I do this? Thank you!

This formula is not working to pull data from the two formula columns (Days Actually Out and Backdata days out) I want. It seems so simple. If the DATE OUT is before or is today, pull this number. If the date is AFTER today, pull another. IF({Date Out} <= TODAY(), {Days Actually Out}, IF({Date Out} > TODAY(), {Backdata days out})) It currently only pulls the ‘Backdata days out’ data for this column. . . Can anyone tell me if this is a no-no in AirTable or if I’m doing something wrong?

Hello, I would like to send daily recurring reminder emails to my team members. I would like the email to be formatted to include the task due and also include specific fields that I have created including multiple select fields and due date. For example, within a table, “tasks”, I have a view which is filtered to include a single team members tasks grouped by due date and a multiple select field identifying the “importance” of a task, either “low, moderate, and high”. I have a formula column which prints the text “send reminder” if the due date field is today’s date: “IF({Due Date}=TODAY(), “send reminder”, “”)” I have set up a Zapier trigger using the “scheduler” zap, which is scheduled to trigger every morning at 5am. Next, I set up a Airtable action, “find record”, to search for the value of “send reminder” in the table. I then set up an action for gmail to send an email to the team member. I want to format the contents of

Hello, my first post to this community. This formula was entered and accepted with no errors. The issue is the second IF (in this case {Tax Type}='Sales Tax) does not produce a result. Here is the formula: IF({Tax Type}=‘Bank Rec’,IF({Qtr}=1,‘1/31’,IF({Tax Type}=‘Sales Tax’,IF({Qtr}=2,‘3/20’)))) My statement is: Statement 1: if the tax type is ‘Bank Rec’ and the Qtr=1 then the date 1/31 should display. Statement 2: if the tax type is ‘Sales Tax’ and the Qtr=2 then the date 3/20 should display. Here is what I have tried: If I change the tax types for e.g. put sales tax first and bank rec second then bank rec will be blank I have hand typed the formula in the field, I did not copy and paste I have tried changing the single apostrophe’s to double For some reason the second IF does not produce a result even though the formula does NOT error out.I have uploaded the image of the result. Thank you in advance. I ho

I can’t find a similar question so I’m reaching out. My placeholder shows Project - “our tracking number” but I need to have “project” changed to Recall ONLY IF a dropdown menu has the status of Recall. Suggestions on the OR formula to use?

I have a download column with 1 number each day. I want to have the total number of download from the beginning from each day that is incremented. Anyone can help ?

I’m looking to estimate a person’s retirement date based on their career start date, this would be using the assumption that they would work 35 years following their first job.

I have two worksheets where I need to compare numerous data fields. Each table has the same common denominator in column A (invoice number) I want to compare numerous fields in the primary worksheet pulling from worksheet 2. For example: I want to pull the record data from worksheet 2 into worksheet 1 to compare them. Tax amount, total invoice, ship-to address, ship to city, ship-to state. I then need to see via a formula if one doesn’t match the other. So if the ship-to city doesn’t match it returns an “X” I also need to pull in data (ship via) from worksheet 1 into worksheet 2 (where it doesn’t exist I currently do all of this in Excel with vlookup. Is this possible to do in Airtable?

I’d like to show N/A when the field is blank, at the moment I get #Error. Can anyone help with where would I add it into my formula? Here’s what we have so far - IF( (IS_AFTER({Hose Inspection Expiry Date}, DATEADD(TODAY(),14,‘days’))=1), “ :thumbs_up: ”, IF( AND({Hose Inspection Expiry Date}>TODAY(),{Hose Inspection Expiry Date}<(DATEADD(TODAY(),14,‘days’))), “ :rotating_light: ”, IF( {Hose Inspection Expiry Date}<TODAY(), “ :skull_and_crossbones: ”, IF( {Hose Inspection Expiry Date}=BLANK(), “NA”, “NA” ) ) ) )

I am trying to write a function that allows me to add a set of numbers if a specific criteria has been met. EX: SUM (IF {Category 1} = “widget”, value {Cost 1})+(IF{Category 2} = “widget”, value {Cost 2})+(IF{Category 3} = “widget”, value {Cost 3})+(IF{Category 4} = “widget”, value {Cost 4}) (I’m not even sure if an IF function can be used this way.) I also don’t know if I should be using a rollup table or something else, given that this data would be on a separate sheet. Any help is appreciated. Thank you.

Hi, rather a basic question but escaping me at the moment. If I have a table that has columns Date & Amount, how could I get another table that summarises this data into Week number, Total for Week Thanks

Here’s an example of what I’m after. Example Input = “1-4,8,10,15b” Desired Output = “1,2,3a,3b,3c,4,8,10,15b” The idea is there’ll be a range between 1-100. Some of these may have children values (example 3a, 3b, 3c). If “3” is inputed it would capture all children. If “3a” is inputed, only that child would be selected. Ideally the ability to indicate a range (1-4). Any recommendations with this?

Hi Guys, I am working on a formula/solution that gives me the result from a specific field. Maybe you can help me. So my first field is a single select field with the following possible selections: -“Lookup 1 selector” -“Lookup 2 selector” -“Lookup 3 selector” -etc. This is then followed by multiple lookup fields that look up data from different tables: Lookup 1, Lookup 2, Lookup 3, etc. I now want to create an additional final result field that contains the value of the Lookup field that has been selected with the selector. And to make it more complicated: I am trying to avoid a long IF() formula because I might be adding more columns and selectors at a later stage. The goal is that I do not have to touch the formula of the final result field ever again. If I do have to touch it again, it should be a simple one like a sum or multiplication without any nestling. Do you have any good ideas how to do it? Thank you in

Another one for the masses. I have 2 columns that calculate days in arrears and rent arrears based on the days in arrears and taking into account a third column of a part payment. A fourth column is a vacate date. these are the formulas for each column: Days in arrears: (IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)))-(IF({Part Payment},0,0))-(IF({Part Payment},ROUNDUP({Part Payment}/({Weekly Rent Amount}/7),0))) Rent arrears: ({Weekly Rent Amount}/7)*IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)) -{Part Payment} These work fine until you put a vacate date in which stops the days in arrears and arrears amount changing. which is what I want… but… If a tenant pays money and the paid to date is changed, this is not reflected in the new

I have a Date Field and a Checkbox Field I would like Records with Dates in the Past to be unchecked and Dates that are equal to today or in the future to be checked. Date Checkbox 3/16 True 3/15 True 3/14 True 3/13 False 3/12 False 3/11 False 3/10 False Thank you