Formulas

OK, all you smart people out there. (I know there are a lot of you!) I need a formula for my database and I need some help, (because I’m horrible with math and algebra.) I have different churches with different services and studies through the week. The services are stored in the database with an unspecified date/day/time. (e.g. Bible study is every Tuesday night at 7:00pm) On an Airtable calendar I want to display an actual date to the user. (i.e. Tuesday, March 10th.) In order to do this I have to calculate what the next actual future date will be from whatever is the current date. It has to dynamically adjust with any given day the user might be looking at the calendar. So… Get the current date (e.g. March 4th) Get the stored generic day (i.e. Tuesday) This is where I get stuck !

Is it possible to override a cell with a formula? For the most part, one of my columns should be a formula, but other times I want to hard-code it. This has kept me from using AirTable to track my bookings/commissions because it’s not as flexible as Excel in this way. Would love to be able to use the system for what I need. Thanks!

Hello, I have a date field and a checkbox field. Can I write a formula that says if there is a date in the date field, check the box in the other field? Eg “Date Commission Paid” has a date, so “Commission Paid” is ticked?

SWITCH(PageID, "18479402361", "NKH", "8532248414", “WWP”, "8271670852", “LLS”, "342294162453301", “PP”, "27817332304", “AFSP”, "55701226138", “AHA”, "15687409793", “WWF”, "12692786774", “ALS, "184404096774", “LUPUS”, "105229062888132", “SSS”, "25372476424", “BFAS”, "12596759677", “RAICES”, "14084625156", “SGK”, “13793312555", “MAW”, "1562713090633117", “OUR”, "20876805343", “MOD”, "100438444321", “CCA”, "8492293163", “EDF”, "111541054636", “BTM”, "10331123781", “SO”, "371566686229476", “ADAA”, "342091725962792", “M22”, "8047221596", “STC”, "11791104453", “NRDC”, "81517275796", “UNICEF”, "512423278811960", “DS”, "122531741163825", “ALF”, "22902828717", “BB”, "332378771195", “CF”, "97646191545", “HA”, "44224524016", “AKF”, "332077995968", “LCRF”, "84618074710", “CCC”, "95155519857", “BLISS”, "24470472733", “TP”, "7271534916", “FFB”, "N/A") Here is my switch formula currently. I have genuinely been looking at this for 45 minutes now and I cannot seem to figure out why it is erroring

Hello, I’m creating a spreadsheet which has multiple tables for different events (event 1, 2, 3, 4) we put on, and within those tables is dollar amounts. I then have a separate table with rows for each event, and I want the table to calculate the total dollar amounts across all sheets? Is this possible?

I am wondering if I can take the column that’s titled “Client Lifetime Value” which is a numbers column… If the “Client Lifetime Value” is set at $37 I want it to print out “$37 Package Purchased” but if the value is $127 I want it to say “$127 Package Purchased”. I would like these to show up in a Single Select column at the end of it so that the options are colored and easily distinguished. We use the single select options to ensure that the filtering for the rest of the spreadsheet works correctly.

Hello, I’m trying to do a formula that calculates commision due. I have the commission field, and I also have a ‘commision paid’ checkbox field. Basically I want the commission owing field to calculate the commission if the paid field isn’t ticked yes. Help please!

Hi guys. Trying to figure out the best way to overcome the limitations of the Page Designer and hoping a formula might be the answer. I’m collecting a lot of data in a table called “Rooms”. Each room record has linked products, materials, and concepts, and then I use a bunch of lookups to retrieve the information I’m hoping to display on my Page Designer. The problem is how the Lookup field combines my information. Here is my Product Name Lookup field for one room as an example: Aqua 2 Complete Shower Rail, Lapiana Towel Bar, Lapiana Towel Ring, Lapiana Robe Hook, Lapiana Paper Holder, Square Tubular Pull Handles for Back to Back Mounting, Square Trim for 1/2" Valve, Caso Single Hole Faucet It obviously collects the information in a list, but that’s not what I want. I would like to be able to hand arrange each name in my page design so that each text string can be under their respective images. For that, each name has to be in its own field. Is there a formula that would separate my

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