Formulas

I am creating a budget and have amounts incoming at various frequencies. I want to create a field that calculates the monthly amount based on a formula. This is the formula I have got but I get an Invalid Formula error message:IF({Frequency} = "Daily", {Amount} * 30,IF({Frequency} = "Weekly", {Amount} * 4.33,IF({Frequency} = "Bi-weekly", {Amount} * 2.167,IF({Frequency} = "Monthly", {Amount},IF({Frequency} = "Quarterly", {Amount} / 3,IF({Frequency} = "Twice-Annually", {Amount} / 6,IF({Frequency} = "Annually", {Amount} / 12,0 // Default case, if none of the above matches)))))))How do I need to correct it?

Hi All I’m creating a budget database and need some help trying to figure out how to create a formula in one table that pulls values from another (not even sure I can do this). I have 3 tables: Income table where horizontally the months are listed with a value and two wage types are listed vertically and it is auto-summing at the bottom of the table. I need the combined monthly income to pull into Bill table where each month is listed horizontally and each bill vertically…and the amount of each bill for a given month is listed. Budget to Income table…here’s where it gets messy for me. I need the combined income from the Income table for each month to drop to the appropriate month field… I need the combined value of each month from the Bill table to drop to an expense line by month Is there a way to do this? Emma

Hi, I don't know if it is possible and I don't how to do it, or if it is a feature that is still not available in airtable.I have 2 tables: Reports and Users.I divided the reports by categories (1,2,3 etc), and I created different views for each category.I would like to lookup or rollup the category in the user table based on the different views that I created in the report table. In this way, I will filter the users by the categories, and I can apply some automation. Without this feature, I have, for example a person that has this: I would like, for example, that this person has only the code "5" and filter all the other data based on that.Is there any way that I can do this? Thank you 🙏

I want to store the total number of employees in the programs table to calculate the participation rate. Additionally, I'd like to break down the number of employees by position to calculate the participation rate for each position. I have three tables: Programs, Employees, and Tasks. While I can easily count the number of employees who complete each program or task, I am struggling with creating a field that sums the number of employees from the employee's table and places that total in the program's table to calculate the participation percentage.

I need to evaluate whether or not an attachment field is blank with an IF statement. Variations of double quotes and the BLANK() function don't do the trick. Any ideas?

Hi, can Airtable auto fill with alphanumeric sequence? For example, my sequence goes UF-3101, UF-3102, UF-3103, etc. When I highlight the first two cells and drag it down, it just repeated the UF-3101, UF-3102 over and over instead of adding +1. Anyway airtable can do this? Thanks

Hi there, I’m looking to create a formula column based off a column which has multiple fields.The Column has one or multiple of SD/HD, UHD, HDR or SDR.I want to create a new column called “Available in UHD?” where if one of UHD, HDR or SDR is there then show Yes. If it has only SD/HD then show no. Any help would be appreciated.

can you please help me convert this to the proper duration format? OR maybe into Seconds.I have the formula below:VALUE(LEFT(SUBSTITUTE({Fulfillment Time}, ' minutes and ', ':'), FIND(' seconds', SUBSTITUTE({Fulfillment Time}, ' minutes and ', ':')) - 1)) But it will return an incorrect number or 0 seconds if the Fulfillment Time would have "1 minute" or "1 second" since the formula above only captures "minutes" and "seconds". I've been trying to find solution for days now.Temporarily, I'm cleaning the data thru Google sheet changing the minute to minutes and second to seconds. Also adding 0 seconds if the data only says (sample) "3 minutes" and adding 0 minutes if the data only says (sample) "45 seconds". PLEASE HELP

Hi all, apologies if this question has been asked and answered already. [EDIT: attached a screenshot since the embedding images don't seem to work.]I am creating a table of contacts for relationship management. For each person, I have the fields "Introduced by" (whoever introduced me to them) and "Introduced me to" (whoever they've suggested I talk to). I want these fields to mutually autopopulate. For example, if John Doe suggests I talk to Johnny Day and Jane Smith, and I've put Johnny and Jane in John's "Introduced me to" field, I want "John Doe" to automatically appear in their "Introduced by" fields, instead of having to do it manually:Ideally, it should work the other way around as well. If I put "John Doe" in Jane and Johnny's "Introduced by" field, their names should automatically appear in John Doe's "Introduced me to" field. The logic is something like: if {introduced me to}(N) contains {contact name}(M), add {contact name}(N) to {introduced by}(M) an

I want to know when an approved campaign goes back in to revisions so that I can review it again. I have multiple other reasons to want to know when a campaign status changes and what the previous status was; e.g., trigger subtasks for team members, notify leadership etc.I use the following statuses: planning, assigned, in progress, completed, approved, scheduled and launched.and two nonsequential values (revisions, missing inputs) that can be used at any point in the process.What I currently set up:The status field is a single-selectAn automation that captures every change in campaign status and populates another tableThat status tracker table is linked to the campaigns tableThe status tracker table has a 'status field' last modified date fieldWhat I want is a way to compare the previous status field to the current status field so I can trigger actions based off of the new status. What I cannot figure out is how to connect the most previous status to the current status. Any help

I am trying to figure out the formula to populate the date of a specific day in relation to a designated date.Below are some examples of what I mean.I have a date field titled EVENT DATE. I want the column titled PAYMENT DUE to populate the date of five Fridays prior to the event's date aka EVENT DATE.It seems like this should be so simple and/or already a question asked. But I am having a hard time finding a question similar to this.

I have a “Campaign Name” table that contains a “Product” field (which uses abbreviations).The “Campaign Name” table has a formula field which uses the “Product” values (along with other fields) to generate a campaign name.Our request form (on the “Campaign Name” table) uses the “Product” field, which shows the abbreviations in a picklist. You can only choose one Product per campaign.I have created a new “Products" table which maps the Product Codes (abbreviations) with full Product Names.For example:Product CodeProduct NameWDGWidgetWhat I would like to do is have the form display the Product Name to the user but still use the Product Code when generating the campaign name. Is this done just with Lookup fields, or is another formula required to do the mapping? And then what product field am I referencing in the existing formula that generates the campaign name in the "Campaign Name" table?

Hi,Let's say I have a People table with Name A, Name B, Name C.Another Table "Sports", which links Name A with Football, Name A with Football (again), Name A with Basketball, Name B with Basketball, Name B with Basketball, etc.In the People table I want to create a (rollup?) field that returns e.g. for Name A: "Football (2), Basketball (1)" as a string.Haven't been able to figure it out playing with COUNT, ARRAY, etc in Rollup field and searching this forum. Any ideas?

I am trying to create a formula to calculate the number of days a specific status is active. For example, how many days the ticket is "On Hold". The need is to calculate the number of days the ticket was actually active, in other words the difference between the {Closed Date} and the {Created Date}, minus the number of days the ticket was in a "On Hold" status. However, I am not finding a way to keep the counter, when the status changes. Currently, the counter resets to 0 when the status changes. If you could advise me on how to do this, that would be great!

HiIs it possible to count the number of changes or modifications that have been made to a specific record?Rgds

Hi there and Happy Friday!I am trying to calculate the time it takes an individual to get a specific type of number at the time they enroll in our program.The formula I have is essentially, "If the individual does not have a preexisting number, calculate the time difference between the date they got the number and the date they enrolled in the program." The formula is: IF({LO has FTN at time of Enrollment}="No",WORKDAY_DIFF({Enrollment Date},{Turned in FTN})) That works but if “Turned in FTN” is blank, the calculation results in "#ERROR" and I hate "#ERROR". So, that means I need to add something like, “If “Turned in FTN” = Blank, then “””. I tried adding that a few different ways but am still getting #ERROR. Any ideas how to get rid of the #ERROR? Thanks in advance!  

Ok, I have been trying to do this for a while on my own with no luck.I have a list of serial numbers. These serial numbers have more than one entry. I group the view by serial number so I can see all entries for any particular serial number. What I would like to do is figure out a way that I can see only the last entry for every serial number. My thought is to have a third field for a conditional formula. Something that just posts the number 1 if it is the most recent date. Then I can use a view to only see everything with the number 1 in that field. The problem is that I need to pull the most recent date for EVRY serial number group. Not just the most recent date over all. Hope that I am explaining this right. Here is an example of the a sample base: Please help! Thank you.

Hi guys!I'm using Ubiqod for attendance tracking in our company. When you scan code, you click on the correct button (arrival, break, going home). After you submit it, the data comes to google sheet and with automation it gets to the Airtable. Now the problem occured. The timestamp from the google sheets it's not the same in the airtable. My guess is that this has got to do something with the different timezones. In googlesheet it's correct timestamp, in airtable it's 2 hours later.This is timestamp from google sheet:This is timestamp from AT:How can I transform this date in AT so that the hour will be correct (it was supposed to be "14:07" not 16:07). Thanks! 

Hello!I have two fields that I want to meet certain criteria to return a value in another field.Return Value "Late" if field 1: Advanced Record contains "ADVANCED" and Field 2: If Date/Timestamp is after 10p EST otherwise return "On Time" if Date field is empty have this field stay empty Thanks!

Hey I am trying to set up a formula for the below problem. I am provided with a list of names in the 'home' column and certain entries I need to change to end up like the 'bot name'. I have tried switch/If functions and I just get errors.  

Hi!I'm using an airtable base to calculate my working hours as a freelancer who works in live entertainment and I'm trying to figure out the best way to have a flexible system for different employers' rules for minimum paid hours of a shift, after how many hours before OT and DT, and also how some allow for DT after midnight. There is also the possibility with some for Sundays to be considered "premium days" regular time=time and a half all day. Right now there is a tick box per "shift" record that allows me to say this shift is "premium".Right now I have a table of "billing rules" with lookup values for each parameter.Each shift is linked to a "billing rule" record, and these values are lookups. The lookup values are then used to determine how many hours fall under each.The formulas to determine how many hours fall into each bucket involve a bunch of IF functions. If the billing rule is "flat rate" these hours are set to 0 and the total is overridden. Here is each:The total billing fo

DG mw me nahi hai na to be a great day hoga to be a

im accautn is Block support of the day is the best way to get

I would like to create a script that checks if my employee has already taken an advance on the same day. If it has already been taken, the new record should be deleted. I do this in the form. How can I get the current record ID from the form and delete it or add it if it was the first time. let table = base.getTable("Zaliczki"); // Zamień "Nazwa_Tabeli" na nazwę swojej tabeli// Pobierz dzisiejszą datę w formacie ISOlet today = new Date().toISOString().split('T')[0]; // Pobierz rekordy, które mają dzisiejszą datęlet existingRecords = await table.selectRecordsAsync({    filterByFormula: `IS_SAME({Data}, DATESTR(TODAY()), 'day')`});// Sprawdź, czy istnieje już rekord z dzisiejszą datąif (existingRecords.records.length > 0) { &nbsp

Hi all,I have a base table with the transaction history which lists each profile's contribution amount, date, and the fund invested. Each profile is allowed to invest in many funds and can also invest in the same fund multiple times. How do I create a view that would sum the investment total and sort by funds at the same time?Here is the transaction history table (base):I created a "List" from Airtable with Level 1 as Funds and Level 2 as Profiles, but I was not able to sum the values from the same profile:As you can see below, there were multiple transactions from the profile that starts with "2...." within the same fund