Formulas

Hello, I’m looking for a way to track whether projects completed by team members were done so early, on time or late. I have a column that uses a single select drop down option for them to change their status when they proceed to the next portion of their project (On Deck, In progress, etc). Is there a way that I can set up a formula to track when a task goes from one status to the other, compare the date it was changed to the due date (also set up in a column using the date field) and return a value of either early, on time or late?

See if someone can help out with a couple formulas here. Thanks. See video below. Loom Loom | Send a video. Not a thousand words. Get your message across with instantly shareable videos for work. Capture your screen, record your front-facing camera, and narrate it all at once, then instantly share with a simple link.

Hello everyone! I have a need for a field that determines next “share time” for an event based on specific criteria. I have the following columns: {Start date} = date field {End date} = date field {Select dates} = checkbox field that indicates when an event concists of only select dates between the {Start date} and {End date} and not the whole span. I also have a date field for {Last shared} = date field What I want to achieve is a new field, using formula, that gives me the date the event should be shared next called {Next share}. If the difference between {Start date} and {End date} is less than 14 days AND the {Select dates} is unchecked AND if today is before {End date} I want it to add 7 days to the {Last shared} date. If the difference between {Start date} and {End date} is greater than 14 days AND the {Select dates} is unchecked AND if today is before {End date} I want it to add 14 days to the {Last shared} date Idealy I would like it to additionally return the following: If

Hi, I’ve searched around and haven’t found an answer to this, so here’s hoping someone can offer help. I’m using AirTable to record and invoice hourly work. Among my columns I have client (organized in single select dropdown) employee (organized in single select dropdown) employee hours (organized in a # column) date I need to create a view or a tab in my base that adds all the total hours by client in a way that allows me to add them to a Block so I can create an invoice. For example: a) On (date) (Steve) worked (3 hours) on (client 1) b) On (date) (Steve) worked (2 hours) on (client 2) c) On (date) (Bob) worked (4 hours) on (client 1) I need to have this formula calculate Client 1 - 7 hours (sum of records A & C) Client 2 - 2 hours (record B) … ideally while filtering by employee and date, but I can figure that part out. Obviously, organizing a view into “Group By” accomplishes this, but the sums of the Group By fields are not possible to import as a value into a Block. Anythin

Is it possible to display results from 2 different IF Formula fields in one field-I tried using an ARRAYJOIN formula…

Hello, I’m trying to solve a problem that I think may not be all that complicated. I want to automatically add the date to a column if the column is blank (ideally so that dates answered previously don’t change but new entries have the date added). Anyone have any formula suggestions?

Noob here - so bear with me. I have an incoming date I want to convert into an Airtable Date - is there a formula that can do this? Here is the type of incoming information I want to convert: Thu, 14 Feb 2019 19:17:23 +0000 (UTC)

I’m trying to extend a function that I previously got some help with on this forum. My use case is a base for storing details on art exhibitions, with fields for a start and end date for each exhibition. With help, I got the formula working when it was only checking 3 conditions - Has it started? Is it currently running? Has it finished? I’ve now tried to nest another IF to test whether the exhibition will end soon (a certain number of days to the End_date), but I can’t get it to work. I’m wondering whether it’s even possible to do what I want! The closest I’ve got are these two. The following will flag all currently open exhibitions as “Ending soon” even if they’re not. IF(IS_AFTER({Start_date},TODAY()), “Not yet started”, IF(DATETIME_DIFF(TODAY(),{End_date},‘days’)<=3, “Ending soon”, IF(IS_AFTER({End_date},TODAY()), “Currently running”, “Already finished”))) The following will flag all open exhibitions as “Currently running” even if they’re due to finish within 3 days. IF(IS_AF

When pasting the created formula field from Airtable to another application (Chrome or TextEdit), the result adds open and closing quotations "". Formula: ‘’ & Name & '’ & “\n” & {FIELD 1} & “\n” & "FIELD 2: " & FIELD 2 & “\n” & "FIELD 3 " & {FIELD 3} & “\n” & "FIELD 4 " & IF({FIELD 4}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 5 " & {FIELD 5} & “\n” & "FIELD 6 " & IF({FIELD 6}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 7 " & IF({FIELD 7}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 8 " & IF({FIELD 8}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 9 " & IF({FIELD 9}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 10 " & {FIELD 10} & “\n” & "FIELD 11 " & “\n” & {FIELD 12} Result: “Sample Name FIELD 1 FIELD 2: FIELD 2 FIELD 3 FIELD 3 FIELD 4 Yes FIELD 5 FIELD 5 FIELD 6 Yes FIELD 7 Yes FIELD 8 Yes FIELD 9 Yes FIELD 10 FIELD 10 FIELD 11 FIELD 12” Any idea what’s going on? The quotations doesn’t show in the cell

I’ve just imported my library database from librarything. Copy and paste worked as a charm for my 711 books, I was impressed! The only thing is that on the Author field I got “surname, name” records: “Kafka, Franz” When I try to link them to another record in a new base, I get one record for “Franz” and another for “Kafka”. Is there a formula to join text separated by comma within a record?

I’m trying to create a formula that will list multiple fields that are empty, but can only get it to list a single field. It is meant to be a quick reference field to see what info is not filled out in a row. The goal: IF {Field 1}, {Field 2}, {Field 3} are all empty, but {Field 4} is filled out, then list “Field 1, Field 2, Field 3” Below is my attempt. It only ever lists a single field, seemingly overwriting other values that would be returned. If I introduce OR IF it breaks the formula. IF({Film Year}=BLANK(), “Film Year”, IF({Print Source}=BLANK(), “Print Source”,IF({RT (min)}=BLANK(), “RT (min)”)))

I have a roll-up field that rolls up a bunch of warning signs, which I want to display neatly on top of each other instead of separated with commas. I got a formula with the following: SUBSTITUTE({Roll up warning},’,’,’\n’) I’ll expect it to replace all the commas with line breaks - however it just gives me an #ERROR!. What am I doing wrong?

I am in the process of changing everything from excel/sheets over to Airtable and I have run into a strange problem that is quite trivial in Excel/Sheets but seemingly difficult in Airtable. Essentially all I want to do is count how many times an email address has come up in the last 30 days from a DATETIME. Each record has a unique datetime and an email address, all I’m trying to do is count how many times that is happening in Airtable. The function in sheets is =COUNTIFS(A:A,A2919,B:B,">="B2919-30,B:B,"<="B2919)) Where A is the email column, and B is the date of entry. Any help would be appreciated.

I would like to be able to only pull up records in a LOOKUP field if they match specific criteria. For example, in one table I have a list of classes with a corresponding field telling me what “Zone” or location they are in. For example: Class 1 Zone 2 Class 2 Zone 3 Class 3 Zone 4 Class 4 Zone 2 Then in another table I would like to be able to only LOOKUP all the classes that ,meet an existing criteria but only display classes that are also filtered by the Zone in that row. For example: Mondays Class 1, Class 4 Zone 2 Tuesdays Class 2 Zone 3 I want the LOOKUP field in the first row to only pull up the classes in Zone 2. Where as the the second row would pull up classes that are in Zone 3. Is this possible?

I am using a simple form to take orders, with the relevant variables being Buy/Sell, and Price. At the moment we are manually matching orders by creating custom views and sorting by price. As the volume of orders increases, this is becoming far too difficult. How could I create a formula that would allow for basic order matching? It would need to see if there are any Buy orders (single select field) that have a price higher than the lowest Sell order. I’ve been tasked with optimizing the process but I’m having a very tough time with the formulas in general, let alone something this complex. Thanks in advance!

For help writing or troubleshooting formulas. Please include the full text of your formula, plus any helpful screenshots!

I thought this would be quite straight forward, but I’m not having it going for me at all. I have a Roll-up field that is a duration which I need to add to a formula-field as part of a concatenated text. I tried: 'Time: ’ & DATETIME_FORMAT({Total Time}, ‘h:mm’) But can’t get it to work - does anyone know how this could work?

Hey everyone, I’m trying to figure out how to do this formula. I have 3 columns with various dates (day startup is first contacted, day first meeting is set, day second meeting is set). Clearly these dates are going to be in progressive order. There’s also another field called “investment declined.” I’m trying to create a formula that calculates the # of days it took for a startup to either be fully analyzed and declined for investment, or the # of days the startup is currently in the funnel (pulling from the latest date among the 3 columns). Can anyone help me out here? If you’re a Redditor I’ll give you Reddit gold hahaha.

Hi There! I’m looking to find the difference between two fields. Specifically, I have a field for who Registered for a meeting and a field for who actually Attended that meeting. I want to be able to see who Registered but did not attend OR who Attended but did not register. Thanks for any help you can provide!

I’m trying to bring in a spreadsheet into a Base. Each month, we pull a report that tells us how many people, per department, we have. Department 1 = January '19 has 32, December '18 has 28, November '18 has 34 Department 2 = January '19 has 88, December '18 has 64, November '18 had 100 etc. We create a column for each month and put that data in the column 11/2018 32 88 Next column 12/2018 28 64 Next column 1/2019 34 100 We do this every single month, so that means we are adding a new column with the newest data. Issue #1 I need to calculate the difference between the current month - past month. Column 1/2019 - Column 12/18 I then need to calculate the difference between the current month - 2 months prior Column 1/2019 - Column 11/2018 How do I do this, knowing that the column is going to change every single month when we add the newest month column? Issue #2 I need to create filters that show me entries that increase and decrease, based off the formulas created above. Is this even pos

I would like to add a column that displays a number (e.g. “3”) based on the number of times a word (e.g. “YES”) appears in 5 specific columns of my base. How would I do this?

I’ve been reading the board attempting to properly formulate a nested IF statement, but I keep getting an invalid formula error. Could someone please help? This is what I’m trying to do: each record of my database entries are defined by either being Open, Billed or Paid. I use a multiple select drop down to mark the record. What I would like to do is have a field serve as a time stamp for when the condition changes from one to the other. This is the formula I have so far, but it is not working: IF({Status}=“Open”,DATETIME_FORMAT(NOW(), ‘MM-DD-YY’), IF({Status}=“Billed”,DATETIME_FORMAT(NOW(), 'MM-DD-YY’), IF({Status}=“Paid”,DATETIME_FORMAT(NOW(), ‘MM-DD-YY’)))) What am I doing wrong?

I am looking to get the following to work in one column, so the url link still functions. O Beach Ibiza, gps url link

I have a status field that reflects status based on a date field {Finish} being overdue. The status field returns #ERROR! if the Finish date field is null. Can somebody help me extend this formula to suppress displaying #ERROR! if no date value entered? Here’s the start of the formula, which works if there is a value for {Finish} IF({Complete}=BLANK(),IF(IS_BEFORE({Finish},TODAY()),“ :triangular_flag_on_post: Past Due”,“ :heavy_check_mark: On Track”))

Hi, Need some help. DATETIME_FORMAT(DATEADD({Date},{NumberMonths},‘month’),‘YYYY-MM’) example: Date = Jan 1, 2019 NumberMonths = 3 the result is 2019-04 if I have a value in the record table like Day = 25 I want the result to be 2019-04-25 This is for a payment table that reminds me to pay on the 25th of the 3rd month from Jan. 1st. Thanks