Formulas

I have a live integration set up from Airtable to Power BI to display sales data. The reporting is shown at a cadence of every two weeks. Certain records may change in status in the gap of the two weeks - and I want to be able to show what has changed in status and $ associated with that record. In order to determine what has changed from initial creation I have created the following fields: Created on (Returns date of creation of record) Modified Time ( Returns date of modification of specific field of status) Changed/No Changed formula field that returns “Changed” or Not Changed based on whether modified time date is greater then created on I am trying to create a activity field and want to write a formula that returns what that status actually changed from. Any help would be greatly appreciated!!

Hi there! Is there a formula for isolating the last available single-select from each group? I’m looking to only show the Shows that are marked as On-Air but also only want to see their currently airing season. I have a single-select for On-Air or Off-Air, and a single-select field for Seasons, but let’s say Jersey Shore has 6, and Teen Mom has 10 seasons, I only want to see Season 6 of Jersey Shore and Season 10 of Teen Mom. Is that possible? Thank you in advance!

I’m trying to figure out how to use an IF statement with DATETIME_FORMAT({Birth Date}, ‘MMMM Do’). Basically, I’m formatting the Birthday Field from the raw Birth Date field to remove the year and to put the date in a more reader-friendly format. However, when a birth date is missing, I get #ERROR. Is there a way to do an IF statement with an IF({Birth Date}=BLANK(),“Birthday Missing” with the DATETIME_FORMAT({Birth Date}, ‘MMMM Do’) as well? I tried just combining the two, but it won’t accept it as a valid format. Here’s screenshots of the fields: Thanks for your help! I’m new to Airtable so not so great yet.

Good day to all! I love how the Airtable works, but restricted by a total lack of understanding to some of the formulas. Would any one be able to help me how to figure this problem of mine please? Apologies if this is a stupid question, but: I’ve made a table of content for tracking of bug reports. What I would like to do is when the Status is marked “Completed”. The Completion Date should show the Now() or Today() formula. Else, it should be black. Here’s the formula I have used: IF( (Status = “Completed”), DATETIME_FORMAT(NOW(),‘MM/DD/YYYY hh:mm’), ‘’ ) However, when I tried doing it. Every time I mark a row with status “Completed”, all the dates in are also being changed. Thank you!

Hi all, I know that you can reorder linked field values by the Batch Update app, but I was wondering if there was a way to reorder linked values by another field related to that field. Example: We are linking people’s first & last name between two tables (“Jane Doe”, “Amy Smith”, “Harry Barrel.”) Instead of chronologically sorting them, is there a way to sort by their last name ("Jane Doe, Harry Barrel, Amy Smith)? We have a field that has only their last names, if needed, that is related to the linked field value. Thanks so much for your help!

Hi Everyone, I’m trying to set up record emails to send at certain times of the day, however after testing using the NOW() function, I’ve found that this is not entirely accurate, and on the help page it does mention that when the base is closed the formula will only update roughly every hour. Does anyone have a solution I can use to give a more accurate time in my formula? Thanks in advance!

I’ve built a formula that shows a Status in my Task field based on how many Sub-Tasks are complete: IF({Sub-Tasks Complete} = 0, “ :radio_button: Not Yet Started”, IF({Sub-Tasks Complete} = {# of Sub-Tasks}, “ :white_check_mark: Complete”, “ :orange_circle: Working On It”)) For some of my Tasks there is only one Sub-Task. When one of my teammates moves the Sub-Task into the “Working On It” status, I would like the status in the Task field to say, “Working On It.” Not sure how to change my formula to do that. Any suggestions? Thanks, Kevin

Not sure if this is a formula issue but here goes. I have a table with contract info that is linked to a table with contract billing info (i.e.billing period start date, end date and due date and payment status (invoiced, paid, overdue). In the Contract table I have a rollup field that returns for each contract - the start date of the most recent billing period. I would like to now display the payment status of that most recent billing period. Is there a way to do this? I’ve tried the lookup value but I cannot set it to lookup status only for the max billing start date.

Hi guys, I’ve been playing with the prefill form function that I’ve just discovered and am loving it! But I’ve hit a snag. Im trying to prefill a form to link with multiple linked fields. For example customer A has requested products A and B via a form. (products A and B are linked fields). I then create a new prefilled form to send to purchasing with Prefilled sections on customer A, however I cannot figure out how to link to multiple products. I can only get Product A to prefill. Heres the urls I’ve tried: prefill_Equipment=Product+A,+Product+B prefill_Equipment=Product+A++Product+B prefill_Equipment=Product+A&+Product+B prefill_Equipment=Product+A%20+Product+B prefill_Equipment=Product+A/n+Product+B prefill_Equipment=Product+A%0A+Product+B Any ideas on how I can do this?

I can’t seem to figure this one out. I have 3 Writer fields with 3 corresponding Share fields. So far I am only able to combine the WRITER fields with the following formula: {WRITER 1} & IF( AND( {WRITER 1}, OR({WRITER 2}, {WRITER 3} ) ), ', ’ ) & {WRITER 2} & IF( AND( {WRITER 2}, {WRITER 3}), ', ') & {WRITER 3} But I’d like to combine WRITER and corresponding SHARE fields and then combine everything in the ALL NAMES COMBINED formula field. The final combinations should read: John (BMI: 20457, 50%), Michael (BMI: 20691, 50%) John (BMI: 20457, 50%), Michael (BMI: 20691, 25%), Charles (PRS: 30918, 25%) Charles (PRS: 30918, 100%) Yes, the extra “)”

I have been trying to figure out how to write this formula: Where Category is any of “macOS” AND checkbox Universal is checked OR checkbox Intel is checked. Here are two of my approaches: IF(Category = "macOS", AND(Universal = 1), OR(Intel = 1), "true") IF(AND(Category = "macOS", OR(Universal = 1, Intel = 1)), "true") Can anyone tell me what I’m doing wrong?

Hello dear community! I built a nested formula, and Airtable does confirm it and says the field was updated (The “field updated” message did popup). Although when I checked the field again, the formula seemed to not be updated… This is the formula. IF({field}= TRUE(),“value 1”,“value 2”,IF({field 2}= TRUE(), “Value 3”)) Is there something wrong in what I am doing? Is this a bug? Thank you in advance!

Like the example in the screenshot, I’m trying to organize my formulas for easier viewing. How in the bleep do you get formula onto a new line? Or indented? Is using an external editor the only way?!? I was wholly expecting the “Enter” key to do the trick, but not dice. What am I missing?

For context, I am 100% not an excel whiz, so formulas in general are foreign and difficult. I am trying to create a field that creates a “completion rate” so an average of completed / incomplete tasks based on the checkmark field. For context, I am trying to determine if our team is maintaining at least an 80% completion rate on our tasks. Can anyone help me with this formula? Thank you!

I imagine there is a simple way to do this, but I need to convert a decimal number to a text percentage (0.15 > 15%) to incorporate into a concatenate formula.

This is a big ask, but I just can’t figure out how to setup this formula. I’m trying to determine the Step 1 “Initial Trial Balance”. The initial trial balance is the lowest account balance for any given month in a 12 month period. The amount can change based on the following variables (other cells): First Payment date (Month) School Tax Amount School Tax Disbursement Date Property Tax Amount Summer Property Tax Disbursement Date Winter Property Tax Disbursement Date This example scenario/figures can be found here: https://www.consumerfinance.gov/rules-policy/regulations/1024/E/ I can’t figure out how to create the formula so that it calculates correctly given the variables noted above. Anyone care to take a stab at it?

Hi, I am using a formula to create a slug: SUBSTITUTE({Job Title}," “,”-") However, is it possible to add another item in the formula, such as {location}? Many Thanks, Hannah

Hi guys, I have a next due date formula that updates to the next month when it is due but i want to delay the due date to change 5 days after the due date, below is the formula i use. DATEADD({Contract starts}, DATETIME_DIFF(TODAY(), {Contract starts}, “months”) + 1, “months”) Thanks in advance

I know the way I am doing this is unnecessarily complicated and even though it works, I’d be interested in learning how to do the same thing more efficiently. I have a formula field that looks at the content of another field ({WRITER 2: SOCIETY} and is supposed to fill in a corresponding number. {WRITER 2: SOCIETY} can contain either the names ASCAP, SESAC, BMI, or any other name. ASCAP, SESAC, and BMI all have corresponding numbers (with leading zeros) that should be filled in if the statement is true. ASCAP#: 00701163494 SESAC#: 00850346936 BMI#: 00794154022 If {WRITER 2: SOCIETY} contains any other name, fill in the BMI#: 00794154022 This is what I am doing. How can I simplify this formula? Thank you in advance!!! IF( {WRITER 2: SOCIETY}=0, ‘’, IF( {WRITER 2: SOCIETY}=‘ASCAP’, ‘00701163494’, IF( {WRITER 2: SOCIETY}=‘SESAC’, ‘00850346936’, IF( {

Slowly I am getting a better understanding of Airtable :slightly_smiling_face: I have a table called task: And a table for completed task: So far so good. When a user completes a task, a new record of the task is created in “completed task” along with “device.id” to keep track of which user completed the task. Then I have a table called “Stats by device” Where I get the: • The sum of act/belong/commit score (by user) • total completed task (by user) And last, a table called “stats by task” Where I

Hi, community! It`s my first time writing a formula and I need your help! I’d like to create a formula to represent a due date ocurring ‘today’, a due data ocurring ‘this week’ and a due date ocurring this month. I tried to create that formula combining an IF function and a DATEADD function. Airtable keeps informing the formula is invalid. What’s is the correct way to write that? I thought the following formula was ok, but Airtable says it’s wrong IF({Due Date} = TODAY (), “Today”, IF ({Due Date} = DATEADD (TODAY() , 7, ‘days’), “This week”, IF({Due Date} = DATEADD (TODAY(), 30, ‘days’), “This month” ) ) ) The formula is wrong and I don’t know how to fix it. I tried several variations and all of them are wrong as well. Could you please help me solve this? P.S: The table is written in Portuguese

I have 2 check box fields and a cost field. If one checkbox is checked and the other is unchecked I want the amount in the estimated shipping cost field to go into the Unshipped Cost column(this is the formula for the Unshipped Column cost field) I have IF({We Ship}=1 and {Shipped}=0)={Est Ship Cost} Thanks in advance.

Hi, I hope this is an easy to answer question; sorry for the density of my skull! I’m building a content scheduler for my editorial team; some stories are immediately assigned a publication date, but for others that field is still blank/TBD. As a result, when you view the list of stories sorted by PUB DATE, all the undated ones appear first – which is undesirable. To avoid that, I created a new field (PUB-DATE-Fx) with a formula that converts the PUB DATE into an IF statement, thus: IF({PUB DATE} = BLANK(), “UNSCHEDULED”, {PUB DATE}) Now, when I sort the the stories by PUB-DATE-Fx, the undated stories come at the end, under “Unscheduled.” Wonderful! But it also creates ugliness: when I sort the stories by PUB-DATE-Fx, the date now appears as an string of digits (2021-03-09T00:00:00.000Z) instead of something recognizable (March 9, 2021) Is there an easy way to tweak the formula so it renders a date that’s more user-friendly? Thank you!

I’m an Airtable noob with what I hope is an easy-to-answer questions for someone (unlike me) who knows what they’re doing. I’m building a content scheduler for my editorial team; it includes a view of stories sorted by week. To do that, I created a field (WEEK) containing a concatenation formula built around the story’s expected publication date (PUB DATE): CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({PUB DATE},-DATETIME_FORMAT({PUB DATE},‘e’),‘days’),‘M/D’)) That works great for those stories with an assigned PUB DATE, sorting them under “Week of 2/14” or “Week of 3/23” or whatever. But some stories don’t have a pub date assigned yet, and those date fields are blank. Here my formula fails, filing those stories under "Week of “#ERROR” How do I get rid of the #ERROR ? Seems like I need to combine concatenate with some sort of IF statement, but that’s beyond my brainpower. Any ideas? No doubt t

I have an address field. I would like a formula (probably using SWITCH) to say that if the address contains “London” or “Chingford” or “Romford” the Location field should say London or if it contains “Hale” or “Stockport” or “Bury” the Location field should say Manchester. Thanks in advance.