Formulas

What am I doing wrong with this one? Please help! If ({Date Due} IS_BEFORE{Date to work on it}), “ :nauseated_face: ”))

Hello, dear Airtable community. I want to generate a unique payment ID, based on the sum, date, cashier, and the type of transaction. Everything has been good by far, until I decided that I want to try using emojis. So, I assumed it should be a mix of CONCATENATE and IF, because I want the emoji to appear only in one field = meaning that IF it says ‘cash’ then show ‘ :money_with_wings: ’, IF it says ‘card’ then show ‘ :credit_card: ’

Hi, I’d like to auto-assign due dates to our writers. However, there are some conditions: I don’t want due dates to ever occur on weekends Some writers work 3 days a week (M,W,F) and some 5 days a week. Is there a formula I can use to do this and all the while include the start date for the first assigned task?

Is it possible to recreate this formula in Airtable? Where: i = IRR L = Loan amount F = Points and all other lender fees P = Monthly payment n = Month when the balance is paid in full Bn = Balance in month n This equation can be solved for i only through a series of successive approximations, which must be done by computer. Many calculators will also do it provided that all the values of P are the same. I’m trying to solve for i All items except for i would be supplied via columns inside Airtable

I’m a professional writer, and I have a table where I put all the projects I turn in. On the table, I list how long a project took me (duration) and and how many words it was (#) (along with other factors). What’s the easiest way to create a formula that will give me the time it takes per 100 words? I keep messing up each time I try.

I’ve tinkered with a few ways of doing this, but wanted to get some ideas about the most efficient way. I have this full address string: Street address: 1600 Pennsylvania Ave NW # 15 City: Washington State: DC ZIP Code: 20500-0003 I want to extract the State (in this case, DC). I’ve tried LEFT, RIGHT, MID, etc but nothing seems to zero in on it. Appreciate the help :slightly_smiling_face:

Is there a way to utilize a concatenate formula + Link to Another Record to have a lookup work automatically? I currently have to copy/paste the concatenate result from the formula into the Link to Another Record to get the result via lookup.

Can formulas be used in the free trial version ? Tried to use the multiplication * function but not successful. (error) message coming up.

I have used spreadsheets for a while now but certainly not an expert. I would appreciate any help with formula for a ledger. I enter my info by date. Some entries have debits and credits.I would like the table to calculate new balance for each entry.

Hi everyone! I have done several searches and cannot find exactly what I am trying to do. I am relatively new to all of this and my ADHD brain struggles with all of the technical stuff at first, so I apologize if this is very obvious. I want to simply have a “total hours” field that takes the start and end date/time fields and puts it in hours rounded to the nearest quarter. 2/27/2020 8:00AM to 2/27/2020 9:45 would automatically compute to 1.75. This is for a timesheet :slightly_smiling_face: Thanks!

Hi There! I have a series of tasks with deadlines. The field next to “Deadline” is a countdown titled "Days Left’ with the amount of days left until the task is due. However, I don’t yet have/need deadlines for some of the tasks, so the Deadline field is sometimes empty which results in NaN. The formula I used for “Days Left” is DATETIME_DIFF(Deadline, TODAY(), ‘days’) I’ve tried a few different IF formulas to remove NaN but I keep getting errors. Can anyone help me out here?

Hi there, I am trying to figure out a formula to extract max year for a specific company based on the fields below. Max year should pick a max year for that particular company in the field. I tried using MAX function but it didn’t appear to work, is there a way around this?

This is the data in my cell <ANUZ2B2KI2XTG;ANUZ2B2KI2XTG;23.93+0=23.93;Me;26.12;Ig:r#5:><A20AY8YZ1X41HY;A20AY8YZ1X41HY;23.96+0=23.96;Me;26.12;Ig:r#5:><A2FATTUNY7ADJL;A2FATTUNY7ADJL;23.99+0=23.99;Me;26.12;Ig:r#5:> I am trying to extract the first number which is “23.93” from the cell

Form some reason if I try to multiply or divide I get error however addition and subtraction works fine. What am i doing wrong?

Hi all, couldn’t find this question in existing community chats so thought I’d ask y’all. I am trying to allocate different numeric values for different selections as follows. Strength of Connection: “Very Strong” = 3 Strength of Connection: “Strong” = 2 Strength of Connection: “Weak" = 1 No entry in the Strength of Connection field = 0 Below is the formula I have, and I thought I’ve done this successfully before but its telling me there is an error. IF({Strength of Connection}="Very Strong”,”3”,IF({Strength of Connection}="Strong”,”2”,IF({Strength of Connection}=“Weak”,”1”,”0”))) Thanks for your help!

Hi there, I’m trying to create a “ToDo” base with sub-tasks. I have one table with different “project” in every row, and second table with a few tasks linked to each project. Now, I want to create a rollup record to pull out the percent of the task that is already done. (like if I have 2 tasks for one project, and one is checked, so it’s 50% done…) How can I do it? Thanks!

Hi all. Does Airtable track the date when someone enters data or changes data? We are using it to schedule our content calendar, and I’m trying to create a dashboard showing how long it takes to move a project through several stages, which appear as a drop-down menu. does airtable track the dates somewhere invisible or do users have to manually enter the date they’ve moved to the next step? if so, how do I access this data and set up a formula to show the average number of days between steps over a monthly period? (and then display this in a dashboard) Thanks so much for your help! Joanna

Hi everyone! I’m surprising myself by not being able to figure this out. I swear at one point or another I had been able to pre-fill an airtable form with a field that includes an emoji. For example, I want to pre-fill a single-select {Status} field in a form with a status of “ :calendar: Consult Scheduled” I’ve tried it with the actual emoji character as well as with UTF-8 (hex) encoding (which is what Airtable spits out if I wrap the emoji in the ENCODE_URL_COMPONENT() function - see below). https://airtable.com/shrIXXXXXXXXXXXXX?prefill_Status=%F0%9F%93%86%20Consult%20Scheduled https://airtable.com/shrIXXXXXXXXXXXXX?prefill_Status=📆%20Consult%20Scheduled Neither of these work. Am I barking up the wrong tree? Should I just forget about the emoji? Maybe try a different emoji? :slightly_smiling_face:

Hi all, I am formatting my database to show how long a volunteer has been with us (in order to determine when to give them their 1 year of service, 2 years of service etc), however, the cut-off date each year for our volunteers is 30th April. For example, if a volunteer starts with us on 1/5/2018, at 1/5/2019 the years of service is 0, but at 1/5/2020 it will be 1 year. If a volunteer started with us on 29/4/2018, at 1/5/2019 the years of service is 1, and at 1/5/2020 it will be 2 years. I hope this makes sense! I’d love to know how to formulate this so I can get a ‘1’ or ‘2’ etc. Thanks in advance for your assistance.

I have a table of Customers and a table of memos which are related to the Customer by Account number and in the memos table I put a note each time I speak to the customer with basic info of the date the person I spoke to and what was said. In the Customer table I would like to see the Max (Memos:Date) ie the latest date that I spoke to the customer. At the moment it shows me all the dates as I have used it as a lookup since I cannot find a way to get the Max from a related field as a date. Anybody have any ideas please. I usually use Filemaker and the relationships then are quite transparent so am struggling a bit

I have a table that uses a Date field as its primary key. Each date in use happens to be a Monday and there are records for every single Monday for the past 6+ years. This means that for a given row, the row below it is always 7 days before it. Each row also has a Number field that grows from zero (6 years ago) to 7500 (today), but the growth is nonlinear and varies from week to week. What I want to do is compute a weekly growth rate field. This field would be equal to: (Number today - Number last week) / Number last week In order to do this, I need to reference the number field from one week ago. I’m able to compute the date that I should be looking up, but the result is a formula field and I can’t figure out then how to look up the value I need from the appropriate row. Anyone know a simple way to do this?

Hi, I need some help to do something… 1/I have a column with a price 2/ I have a column with a Single Select option, and a list of discounts (10%,15%,20%,30%) I would like to create a 3rd column, with a formula which gives me the total discounted price, based on the discount which has been selected. So if : Column B has a total of $100 Column C has 10% selected The new column D should show me $90 I don’t know how to create a formula which will calculate the discounted amount based on the selected field. I would be great to have some help on that! :slightly_smiling_face: Thanks, Sébastien

Hi. I want to use barcode scan just like PDA device on werhouse managment system. This means scan barcode from a product and than enter the quantity in the field. For example, I scan a barcode on a stack of 12 pcs and enter 12 pcs (meaning 12 pcs loaded). Problem is, next time when I scan same barcode I cannot enter 12 pcs again in the same field and get automatic sum 24 pcs, 36 and so on but I only can manually insert total result. Is there a formula that can make this happen or another way to achieve this?

Hi Guys, So i have this formula above and i want the result in Currency, like “0€” not “0”. Can anyone help me? IF((Tipo2)=BLANK(),“0”,IF((Tipo2)=“Fatura EU”,“0”,Valor*0.23)) Thanks

Hi - I’m trying to link a chronological set of tasks based on the completion date of a proceeding task. I’ve figured out how to project the due date by taking the start date plus Duration (in days) to get a projected due date, however, i’m stuck how to link this to the next record. For example Task 1 is completed on 1/1/20 Task 2 should have a start date of 1/1/20 plus some duration (say 3 days) and have a projected due date of 1/4/20 Task 3 will then have start date of 1/4/20 and so on… any help would be appreciated!