Formulas

I have a base that I’m using to track projects, which have a start date and an end date. Sometimes those projects get modified for a variety of reasons with one of those reasons sometimes being that the end date gets extended. We track all modifications in a separate modifications table that gets linked to the original project. We want to be able to dynamically display the new project end date by referencing all mods and returning the latest date. To do that, I have set up a Rollup Field linked to the Modifications table that returns the maximum value: MAX(values) But if there are no modifications, that Rollup Field will be empty, which won’t work. So, after reading the forums, I created a formula field date with the following logic: IF({Rollup Project End Date}>{End Date}, {Rollup Project End Date},{End Date}) It appears to work at first, but upon closer inspection, the date getting returned in that formula field is one day earlier than it should be. I need it to be exact bec

Hello! Hoping I could get some advice on a formula field that accepts my formula but the cells won’t return any data (not even an #error message). Maybe it’s a bug…? Since I’m dealing with multiple formula fields, lookups and a Rollup result that is being fed back to the original table, I’m thinking it’s an issue with Airtable recognizing the data…Any workarounds or syntax advice? Problem formula is in table 2: IF({Legal Wraps After}=‘Episode’, {Episode Legal Wrap Date}, IF({Legal Wraps After}=“Season”, {Season Legal Wrap Date}, BLANK())) Table 1: SEASONS {Legal Wraps After} - Single Select Field (episode / season) {Last Shoot Date} - Rollup Field (MAX {Shoot Date} of all Episodes in a Season) Table 2: EPISODES {Shoot Date} - Date Field {Episode Legal Wrap Date} - Formula Field: ({Shoot Date} + 7 days) {Season Legal Wrap Date} -Lookup Field: (Lookup the {Last Shoot Date} Rollup Field) Any help here would be much appreciated! Thanks!

Hello everyone! I’m working on a project that involves air tables and was wondering if there was a way I could use the formula function and if the cell is blank, then type in it. My current code is this IF(OR({Implementation outcomes}=“No”, {Implementation outcomes}=“No”), “N/A”, IF({Implementation outcomes}=“Yes”, “”)) It works fine, but when I click on a blank cell to type the tool used it won’t allow me to write. Thanks!

Hi! I’m trying to create a formula that would display a “week of” for a given date where Monday is the start date of the week. The below is the formula I came across on Airtable’s blog - it works great except it displays the start of the week’s date beginning with Sunday. My formula expertise is very limited so there might be a very simple solution to this - I just can’t seem to find one. Any help would be greatly appreciated! CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},‘e’),‘days’),‘M/D’))

Hi everyone, I’m a new airtable user who needs some advice how to perform the following task efficiently. I want to track some subscriptions like this: The renewal date column should auto increase under this circumstances: If interval monthly: And {Renewal Date} <= Today() {Renewal Date} = DATEADD({Renewal Date}, 1, ‘months’) If interval yearly: And {Renewal Date} <= Today() {Renewal Date} = DATEADD({Renewal Date}, 1, ‘year’) I have already tried several ways, but I am not very satisfied with them. Thank you :slightly_smiling_face:

I’m currently using the following formula for tracking Project Health: IF({FOC (Current)}<TODAY(),“LATE!”,“ON TARGET”) however there are times when the “FOC (Current)” date field is appropriately blank. Right now it’s labeling it as “Late” but I need it to show “No FOC” instead. If FOC (Current) is < today = “LATE” If FOC (Current) is =/> today = “On Target” If FOC (Current) is blank = “No FOC” I can’t seem to make the multi-IF’s to work. Thanks in advance!

I was wondering if someone could help me come up with a formula for if there is a certain status you want, that it will automatically put approved/not approved. We have a status that says Approved – so when someone has that associated with their name, we would like for them to get an automatic approval to show up. Any ideas what the formula would look like for this? Thanks in advance!!

Hello, I have a column that contains a bit of data separated by commas. For example: ROW 1: Teeth Cleaning, $150, John Smith ROW 2: X-Ray, $400, Jane Doe All this data is in one column because that is the way it is imported using zapier. Now that the data is imported, I’d like to break the data up by the comma bracket. I’d figure this would involve creating 3 new columns that are formula generated: Service Name Price Patient Name Does anyone know of a formula that will pull data before the first comma for the service name and then for the second column have a formula that pulls data after the first comma and before the second comma? Thanks for your help!

Hello, I would greatly appreciate your help finding a solution to concatenate each of the following fields while ignoring fields which contain (just) the letter “X”. (I am using “X” to denote empty instead of leaving the filed empty). FYI. This formula works for concatenating all fields while ignoring the blank ones. IF({Login Companies}, " " & {Login Companies}) & IF({Login Titles}, " " & {Login Titles}) & IF({Login Persons}, " " & {Login Persons}) & IF({Login Places}, " " & {Login Places}) & IF({Login Differentiators}, " " & {Login Differentiators}) Many thanks!!!

I am building a gym membership system. People pay for X visits which they can use in a 30 day period. When they arrive, they check in (which adds a record). I want to count the number of records for a member (COUNTALL)… …but I only want to see how many records in the last 30 days. Blaggin my head trying to work out how I can do it. Any ideas?

Hi I would like to create a formula in Google Sheets, in one whole column. I have a currency field that I would like to place the decimal in cents i.e: $0.05 I would also like to include: $0.01 Right now the format is: 05 (with no decimal place) I tried to add the decimal as an option in both Airtable and Google Sheets, but it formats it into dollars. I want it in cents, and also dollars, too. I have over 200 cells/records, so I don’t want to have to do this manually, but rather automatically, either by a formula or another method. I appreciate any help. Thank you, Mary

Looking for help creating a formula that can determine how good a certain price is compared to an average price calculated in Airtable. For example, let’s say the average price of something in a record is $100. If my price is within 5% (+/-) of $100 (i.e. $95-$105) it should conditionally show “Average”. If between 5%-15% higher (i.e. $105-$115) it should conditionally show “Fair”. If more than 15% higher (i.e. $115+) it should conditionally show “Poor”. Alternatively, if between 5%-15% lower (i.e. $95-$85) it should conditionally show “Good”. And if more than 15% lower (i.e. $85-) it should conditionally show “Excellent”. Alternatively, a rating system (e.g. 1 star being the worse and 5 stars being the best) would work too. Anyone know if something like this is possible and if so how to do it? Thanks in advance!

Hello, I have this formula below: IF( AND( {Course Payment Duration}, DATETIME_DIFF( TODAY(), {Course Joining Date}, ‘months’ ) > {Course Payment Duration} ), ‘Payment Complete’ ) this formula is meant to tell me when as user has paid a subscription based on the amount of months it been since they joined but it seems to only show payment complete when the next complete month has passed. An example is if the Course payment duration is 3 months the payment complete only changes when it it’s 3 months + the days of that month have passed. I need the payment complete to change when the 3 months have been reached. Hope someone can help Chris

What I am trying to do is use 2 nested if statements to check if 2 variables are blank (A and B). If A isn’t blank it will post A, but if A is blank it will then check to see if if B is blank and if B isn’t blank it will post B. But if B is blank as well it will post the safety string C. I thought below would work but its not working. A and B are cells and C is a string that I have as a safety net. IF({A} =BLANK(),IF({B}=BLANK(),“C”,B),A) Any help would be great thanks!

Hi! My formula to flag an older blog post as in need of an update isn’t working and I’m not sure why. Here’s the formula: IF(DATETIME_DIFF({Date Last Updated},TODAY(), ‘months’) > 12, “Update”, " ") The logic I want is this: If more than 12 months have elapsed between the date indicated in the {Date Last Updated} field and today, I would like the cell to say “Update.” Else, blank. Can anyone advise?

I want to use airtable so bad to solve problems, but I’m struggling to justify the pro plan when I can’t just simply use summary values in a formula or be able to have a column of totals to then auto sort. Here I am every couple months trying to reverse engineer this crazy multiple table, linked records (which I can’t even auto add, I have to manually enter for new records), rollups, with formulas on top of that… and each time I have a variation in the problem I’m trying to solve, it means cracking open a new can of worms to get it done, often spending hours doing something that google sheets can do in seconds. If I hear one more person say “It’s not a spreadsheet, it’s a relational database” again … or if someone casually says that zappier can fix your problem without mentioning the high monthly cost and added work of integrating it all together with some other form software … Let’s take Airtable to the next level and address so many people’s issues by allowing a table to act like a s

I am currently working on setting up a time sheet on one of my bases. I am having an issue with a formula to ID my entries. I am trying to Concatenate the date, name of person, and total worked time all in one. I have the date and name figured out but I can’t get the total worked time to format in anything but seconds. CONCATENATE(DATETIME_FORMAT({Start Time},‘l’), " - ", Who, " - ",{Total Hrs}) {Total Hrs} is currently this formula, DATETIME_DIFF({End Time},{Start Time}), which is fine because I can change the field to be a duration. I have explored the community but haven’t been able to find a solution. Thanks in advance for your help!

Could someone help me create a formula for the following scenario? We run an accreditation system, and have a policy that went into effect on January 1, 2018 saying that programs have five years from the date of their application to submit their report to us or their eligibility will expire, but programs that applied before that policy went into effect were grandfathered in and have five years from January 1, 2018. If it’s helpful, my application date field is in MM/DD/YYYY format, and I’d like the expiration date to generate in the same format. If (application date field) is before 01/01/2018, (expiration date field) is 12/31/2022. If (application date field) is after 01/01/2018, (expiration date field) is 5 years after (application date field). If (application date field) is empty, (expiration date field) is blank Thank you!

Hello, I’m trying to figure out a way to calculate my “Price without Tax”. I wrote something like this but it’s not working. IF({VAT}=20, {Price with tax}/1.2), IF({VAT}=5.5, {Price with tax}/1.055)) VAT is a Percent As you can probably see i’m a true beginner. Any help would be appreciated. Thx

Hi there. I am working with a Rollup up field linking between a Series table and ID table. The ID table has a field called Episodes. What I am trying to work out is how do I display the number of episodes linked from the linked ID table and the count of each. Example: Episode Roll Up 9, 5, 3, 3 (so there are four ID records linked to the Series table and from the ID table’s Episode field there is 1 x ID from Ep 9, 1 x ID from Ep 5 and 2 x IDs from Ep 3) What I would like to display either directly in the RollUp field or in a companion Calculation field is the following: 3: 2 5: 1 9: 1 (so I’d like to be able to order the Episode numbers as well to make it cleaner to view). I have searched thru the Community posts but can’t find anything that addresses this particular query. Any help would be very much appreciated!

Hi, Im trying to create a table in Airtable that would auto-populate some totals from other tables in the same base. Basically, I want to see what the sum of a total is in a specific view in a separate table. I have a field labeled “Total Words” and the sum of those numbers changes depending on the view. I know how to do this in Excel, I would create a SUMIF formula. I want the cell to display the sum based upon if a record is categorized as “Ready to Post”, “In Progress”, or “Needs Assigned”. I can’t figure out if this is possible. Please help!

Hello, here is my formula below IF(DATETIME_DIFF(TODAY(), {Course Joining Date}, ‘months’) > {Course Payment Duration}, ‘Payment Complete’, ‘’) What I need is to ignore the course payment duration field if it is blank, at the minute this is still getting marked as payment complete. Thanks in advance Chris

Hi! Very new to formulas and slogging my way through thanks to all the great advice in this forum! I’m trying to get the formula to return nothing if {days b/w successions} is blank, and to calculate the next date if there is a number in that field. I wrote this, based off what I gleaned from other posts, but it isn’t working. IF({Days b/w successions}=BLANK()," ", DATEADD({Field Date 1},{Days b/w successions}, ‘days’)

Hi there, I’m hoping someone may be able to help with a formula to update a status, based on the date being entered in another field I’m going along the lines of: IF: DATETIME_DIFF({Date Cleared},NOW(),‘days’)<30,“ :open_book: Recently Closed” DATETIME_DIFF({Date Cleared},NOW(),‘days’)>30),“ :closed_book: Closed” “ :exclamation: Open” Thank you

Dear All, I’m trying to build an app to generate the YouTube timeline text that should show the running time of a segment based on the episode start time. I was able to manage everything, but the time, that should be presented as 0:05 is being presented as 5. Is there any way to concatenate a time field on a text field with all numbers? Thanks in advance. Best regards, Joao Coutinho