Formulas

Hello everyone,I've been racking my brain on this question for over a week and have tried countless attempts to solve this, but am still spinning my wheels. Basically I'm aggregating total work history of all our staff across all our events this year, so I need to calculate how many shifts someone has worked. The issue is that it's possible to have multiple records of the same worker per event because different supervisors may submit the same record with different ratings.In my example, here are all the reports (records) that we received on 1 worker for the year (Table 1):Here is the summary record (Table 2):In the summary record (Table 2), Total shifts should be "9" and successful shifts should be "6". The current formula in the ROL

Hi!So I'm trying to set up a table for our home school group to house everyone's birthdays. I've scoured the forums and can't work out why I'm getting an error on all dates on or after 13/05/2019. All dates prior to this work fine and I just can't figure it out! I also wanted to see if I can incorporate a set_timezone formula to ensure it's based on my Australian timezone. Here's what I have set up:DOB field (members will enter their DOB via a form) and a formula field "Birthday (this year)" with the following formula: DATETIME_PARSE(DATETIME_FORMAT({DOB},"DD/MM")&"/"&YEAR(NOW())) Questions:1. I'm getting an ERROR for any DOBs that are from 13/05/2019 onwards. 

I have the following formula to set the entry for "Current Month": DATETIME_FORMAT({🟢 Start Time}, 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM') It seems to be working just fine, however the last entry from last month is setting the formula to 1.If I change the start time (right now) to 6:30pm or anything older, the formula gets set to 0. My first thought was a timezone issue, but I confirmed on the Start Time field that it is set to my timezone (CDT).Am I missing something obvious?

I have a date & time field.For example, below is the data. I will have a different date and time and 100 rows with data.1 Jan 2024 1:00 PMAt 1:00 PM on 1 Jan 2024, I want to send a Slack message using airtable automation.How can we make this trigger?My ideas: using formulas and views

Hi there!I am trying to create a formula that will pull the Last Modified date and combine it with my initials and my Pending Items column. The formula I'm using right now works, but the Last Modified date is pulling tomorrow's date. I'm assuming this has something to do with time zones because it doesn't switch the date until 6pm CST, but I'm not sure how to fix it! Please help 😊Table:Formula:CONCATENATE(DATETIME_FORMAT({Last Modified},'M/D/YY')," ","RE: ",{Pending Items}) 

I'm working on my graduation project, using Airtable as an API. In the API documentation, specifically in the "create records" section, I'm having an issue. When I try to copy the 'YOUR_SECRET_API_TOKEN' for authentication, it stays as plain text and doesn't convert into the expected key format.Has anyone else run into this problem? Any ideas on how to fix it?Thanks for your help!

I am trying to put a formula I pulled together in the primary field, but get the following error "Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference".  Back story, I am using one tab to house all the event details. I am using the nesting feature to pull through things I need for all the sperate views (agenda, session intake, speaker details, ect.), where I would usually use separate tabs for each of these things. Here is the formula that I have pulled together. Depending on what Is selected in the Content Track field it will pull a particular reference field. I want to use this formula in the primary field, but even if I remove the "for column 1" field I still get the circular reference error.  Any one have any suggestions? 

Hi Airtable Community,I am having an issue with a formula that I was hoping to get some help on. Here is the issue:We do our invoicing on Airtable. We have a few different types / categories of invoices - they are as follows:Quotes: This is what we use when we have to submit a price 1st before any approvals to begin have been granted.Suppleental: This is like a change order - we use this when the job has changed and or scope was added.Estimate: This is used  when we have submitted a price but it has not been approved - what we call "settled"Invoice: This is what we use when we have submitted an estimate and it has been approved. We have the primary field be a concatenation field that takes the name  of the customer, the type of work it is, and then looks to the "(MIT) Settled Date" field and if it is empty, uses the word "ESTIMATE" as the last word of the primary name. The thinking here is that when there is no date in

I got a table with DOB in date format dd/mm/yyyBest way to filter those records whose anniversaries (mm/dd) are within 5 days ahead?

Can someone help me figure out why the ‘else’ part of my IF statement isn’t working? I'm trying to create a friendly date/time/timezone field IF a certain date field is not blank. That part is working. But if the date field is blank, I want it to put “N/A” in the new field. That part is returning an error. Here is my formula.IF( {REQUESTED GO LIVE DATE} !=BLANK(),  DATETIME_FORMAT({REQUESTED GO LIVE DATE}, 'M/D/YY')

Hi,I have a table which is sort of a Bill Of Materials containing the all the required parts to assemble a single product, including the required quantity of each part. It's basically looks like:|Product|Part|QuantityRequired|There is another Parts table containing the cost of each part.I'm trying to calculate the total cost for each part in a single field. So far I was able to do it through two fields, one is lookup for the cost of the part and another field with formula {qty}*{cost from lookup field}My question is can this be done directly in a single formula field, without needing the extra aux lookup field to fetch the price of a single part? Feels a bit redundant and was wondering if there's a way to fetch that lookup directly through the formula. (The final calculation of of the product's cost is done in the Products table through a sum rollup on said result)Thanks.

Hi all, I’m a social media manager. I have a base which links together editorial content with associated social posts. It makes amazing things like this possible: Now, I’m trying to create a fully automated UTM parameter system which collects data from various linked records — such as linked web content, associated campaign, etc — and spits out a single URL with appended UTM parameters for tracking in Google Analytics. I’ve got most of it figured out. One step remains: how to extract part of a string. In this case, everything found AFTER the final “/” in a given field. Here’s what I mean. I’d like a formula which scans this text, finds the final “/” in the string, then spits out everything AFTER that:

Hello everyone, I have a data base with activities wher date and time are fulfilled but I would like to show only the hour in one column.I'm unable to display what i want. I have for example one column with ISO 14/12/2023 1:52 and i would like two different columns with 14/12/2023 and another one 13:52 Thank you for your help !

Hi, Seeking help here.. I need a formula in the Client name cell where in it will only show the client name if either of the Client or Brand columns has a name on it. Meaning if the Client cell has a name the Client name formula will only show "Path Social" and if the Brand name has a name on it "ThingTesting" it will only show in the Client name cell 

I have a script that has been working for over a year, but now will not update records.  I sync a table from another base to a second tab in main base.  I then use the following script I was given over a year ago (can't remember where I got it).  It has worked great to look at the synced tab to see a unique ID (emp #) and then pull that onto the main table and pull multiple records from the syned tab.  Now when I run it the script runs super fast and doesn't update anything even though I have matching Unique IDs.Here is the script I am using://Substitute "Orders" for table name which contains values//on which you want to run the vlookuplet mainTable = base.getTable("Covid LOA");

Hi, I'm pretty new to Airtable and currently just exploring if it will be viable for our company to use. The set up I have is two tables, one for learners and one for workshop sessions.  The sessions table has names etc and a field linked to the learners table that shows the attendees booked on the session (multiple select allowed). The learners table has a similar field linked to the sessions table showing the sessions that they are booked on to (again multiple selections allowed). What I would like to be able to do is have a new field on the sessions table that for example shows the attendees that did not show up (nothing fancy I would just use a linked field to the learners table and just input the learners manually, again multiple select allowed). But then I want a corresponding field on the learners table showing the names of sessions they have not attended, is there a way to populate this with the

Hello guys,I'm trying to create a formula for the main Name field on my table.I just can't do it, I'll be more than glad to write here the details and if someone can write the formula for this it will be great, and i will also learn that way how to create one in the future.My table is data from the computer inventory list in my office.I want the Name field to first start with the "User" field (the field type is "Link to another record" ) and after that it will have one space and then a Comma and then another space before the next field.Next I want the "Model" field (Single line text) and after that it will have one space and then a Comma and then another space before the next field.The last one is the field "Product ID" (field type

I think I may have gone too complex with a formula as my base won't open now. It's just stuck on "Loading" on every table. How can I reverse what I did?Note: I created a duplicate of the table and it did the same thing

Hi…I have a field with ‘arrival date’, a field with ‘terms’ (which is days prior to arrival payment is due. This is a number and varies from record to record)(Both are lookup configurations) and I want the third field to calculate the date it would be if the terms number (such as -3) is subtracted from the ‘arrival date’. I tried DATEADD({arrival date},terms,‘days’) but it didn’t work. I experimented with DATEADD({arrival date},-3,‘days’) and with the ‘terms’ look up field just being an integer instead of the look up and those did work so I am not sure how to do this. Thank you!!

Hello, In my base, one table has 2 records each. Currnetly, i do a lookup and it shows me both records. I only want the first to show up because it just doubles the name twice. How can I make it so that when i lookup from that table, only the first (or last) record shows vs having both.

Hi can you please help me return the value "qc" from this "inventory tags column"this formula doesn't seem to work. Thank you!

Is there any way to search for emoticons (any of them) in a text field, and replace with something like a dash or space? Is there a code for emoticon that allows a formula to search for this? Emojis and emoticons are often put into titles of blogs, but I am curating titles and creating url’s out of them, but want to remove any trace of these.

Hello guys! I’m new here and I’m still learning about Airtable, but I’m really enjoying it! I have some doubts about formulas, I would be very happy if they work for what I need, here are my doubts: 1 - Is there any way to add numbers in a column automatically every time you turn the month? For example: Column “parcels” on 12/22/2021 = 1 on 01/22/2022 = 2 and so on? This would be very useful because here in Brazil we make purchases in installments, so the number of installments, changing month by month alone, would make it much easier. 2 - Is there any way for the formulas to understand the “unique selection fields”? I would like to create a formula like: If the “unique selection field” has the option “green” the answer is 1, if it has the option “purple” the answer is also 1, but if it has the option "black " the answer is “1/2” Thanks in advance!

hey there,how would i go about creating a formula column where if a timestamp from a column (let's call that column "timestamp") is older than 24 hrs, then the resulting value would be "follow up", otherwise the resulting value would be "do not follow up" Thanks!!

We run a charity vege farm. I'm building a crop planning tool. For planning, a couple of pieces of key information about a crop are:-Number of weeks from Sowing in the nursery to Transplant (Weeks to transplant - WtT)-Number of weeks from Transplant to Harvest (Weeks to Harvest - WtH)I use this info to predict how long a crop will be in a part of the farm for, and planning to ensure we have the right amount to harvest at different times of the year.In summer, a crop like lettuce may have a WtT of 3 weeks and WtH of 6 weeks. In the middle of winter, that may be more like WtT of 5 and WtH of 10.There is a whole spectrum in between those dates!How would one write a formula for WtT and WtH to be adjusted according to the date of the year? There are input fields for "sowing" and "transplant" which would act as the date to adjust WtT and WtH for.