Formulas

I want to automate an email that goes to staff two weeks before their leave year renews on the anniversary of their start date each year.Is it possible to create a formula which will automatically show the next anniversary in a date format? For example: If my start date is 28/02/2019 and today is 07/02/2024 then the next anniversary date should read 28/02/2024Or if my start date is 28/02/2019 and today is 01/03/2024 then the next anniversary date would read 28/02/2025I'm UK based so using DD-MM-YYYY formatting. I'm struggling to generate anything that works - any help appreciated.

I am trying to have the person who submitted the form on the left followed by a dash - and then another field but cannot seem to fix this. Any help would be appreciated.

Hi everyone. I have a table "receipts" that collects receipts linked to different projects. Every receipt can only be linked to one project. The Receipts table has a Number Field "Amount in Euro". Now I the Project table, I want to have a Rollup field with the sum of all Amount field. But only those who are linked with the Project im currently in. What I want to do is add the condition "Where Project is exactly current project". So for Project 1 it filters by that, for Project 2 by that one. But I can only choose one particular project. Am I missing something or is there another way to do this? Basically what im trying to do is see the Sum of all incoming and outgoing receipts for every project right within the Project. Any helps would be greatly appreciated 🙂 

It appears the URL for a sidesheet within an Airtable Interface does not contain the Record ID. Hoping to create a formula link to records displayed in a sidesheet, however cannot figure out how to do this with the structure of the sidesheet URL.The structure is either:https://airtable.com/appXXXXXXXXXXXXXX/pagXXXXXXXXXXXXXX?xxxxx=sfsXXXXXXXXXXXXXX&detail=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXor:https://airtable.com/appXXXXXXXXXXXXXX/pagXXXXXXXXXXXXXX?detail=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXThe 106 characters after "detail=" is consistent across the various interfaces accessing the same sidesheet, so assume that is the unique identifier I need to generate via an Airtable formula, however have not been able to find a way to do this (similar to using RECORD_ID() in a formula).Can anyone please offer advice?Thanks,Tanya

Hey everyone! How do I create the statement to leave cell blank if a value is missing instead of #ERROR? I’ve read you should not use the BLANK() code? Code for Days Left: WORKDAY_DIFF(TODAY(),Starts) Code for Urgency: IF( {Days Left} < 0, "🔥OVERDUE", IF( {Days Left} <= 20, "🔴 ASAP", IF({Days Left} < 40, "🟡 Medium", IF({Days Left} < 100, "🟢 Low" ) ) ) ) Thank you in advance!

Good morning all! I'm hoping someone can help me with turning a pretty straightforward concat formula into something with more if/then logic to help when empty fields are encountered.Right now I have a primary field on my sheet that uses a formula to build a unique ID for logged payments. That formula is below:  CONCATENATE({Vendor Name}, " - ", IF( {Remit to AP}, DATETIME_FORMAT({Remit to AP}, 'MM-DD-YYYY')) )  The trouble is that with a lot of records there may not be a "Remit to AP" date for a number of reasons. I'm hoping there's a way I can use alternative fields as backups so the primary field doesn't just show something along the lines of "Vendor - " which isn't helpful.There's another field called "Invoice Date" that is much more frequently populated, and a "Year" field (which is a single select, not open text) as the last resort. Would it be possible to have a formula that builds the concat as above and says if the "Remit to AP" field isn't blank to use

What's up guys,I'm struggling to implement Vega-Lite chart extension on my base, it's my first time using it. I'll show you an example of the base I'm working on in which I'll need to create a bar chart. It seems that I can't add multiple fields on the x axis.If anyone can help it'd be greatly appreciated.

Hi, So I’m using PHP which has this functionality built in (basically I give it a number and it converts it to how I would like it…) specifically I’m in Australia but the format I’ve chosen to go with is like this: +61415932797 so let’s break this down +61 = country code 415932797 < number obviuosly In Australia though when you call someone on mobile you dial 04… so it would be like 0415932797 if that makes sense (since you don’t use country codes when dialling within the country)… so I’ve got a bunch of these mobiles like this in my AirTable: 0402 930 888 - like this which I want to convert to +61402930888 - as you can see no spaces (random spacing in my original) - ideally also skip any fields that have text in them but that’s an easy thing… so… yeh long story short how do I regex 04 01 933 833 to +61401933833 also keep in mind a number could all ready be country code… but then again I could just filter those with + and fix manually or something but would be good if for example +6

Hi. I’m looking for a way to calculate an average time of day for an average start time and average end time. Airtable has the date and time field, and the time duration field, butI need the average start time and end time, as separate values.I need to accommodate for a start time being before midnight or after midnight.This is to get an average time a person fell asleep (potentially before 11:59 pm or after 12:00 am) and their average wake up time. Based on my pretty thorough knowledge of Airtable, I can’t figure out a way to do this because Airtable’s fields don’t offer you a time of day without a specific date.A more traditional business purpose of this calculation would be calculating a person’s average clock in time and clock out time, as separate values, but still accommodating for before or after midnight, which could be applicable for a business with night/3rd shift workers.It's my understanding that there is no technical workaround and that this simply a limitation of the type

i have a sync to jira.. i want to assign an airtable user based on the jira user fieldFor example: if  CM2 field contains John Smith, Mary Jones or Kate Day.. Can i create a formula that assigns John Smith, Mary Jones or Kate Day as an airtable user vs just a text box.. so that they get a notification that work has been assigned.  or does this need to be an automation with a user table?Thank you

I am building out a process to make billing time and invoicing easier.  Unfortunately the formula I'm using either results in $1 or a total that's completely incorrect. How in the world is it calculating $216? Should be 7 * $33.33=$233.31  

Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.DATETIME_DIFF({Sell}, {Buy Date} 'days')Well, there may be a problem, but I can't see it... can anyone help with the formula to display the number of days between two dates 🤔 🤔? Thank you.Here's what I'm getting for these 3 fields:Buy Date, Sell, #Days1/12/2020, (No End Date), NaN7/10/2023, 1/31/2024, 177120001/1/2020, (No End Date), NaN9/19/2023, 1/31/2024, 11577600

I would like to extract the months from dates in a multiselect field. If I use Month(Months) I get NaN, If I use DATETIME_FORMAT(Months, 'MMMM') I get #ERROR!.  Anyone know how to do this?  Thanks, Scott

Hi - desperately need help in figuring out what in the world is not working. I am creating a production timeline where the campaign launch is the starting point. From there, I want to count and report certain milestone dates bases on a typical timeline. These milestones are working days (M-F) so not a normal "count backwards". I have to do this for multiple types of campaigns, so instead of making stagnant formulas I'm referencing dates on another table. yes, I looked in to automations and record templates, but they don't allow for relative dates so this is the workaround. Example) Campaign 1Milestone 1: Day -13Milestone 2: Day -10Milestone 3: Day -7Milestone 4: Day -2Milestone 5: Day -1Campaign Launch: Day 0Example) Campaign 2Milestone 1: Day -17Milestone 2: Day -15Milestone 3: Day -12Milestone 4: Day -5Milestone 5: Day -3Campaign Launch: Day 0I've been trying to use the formula workdays to accomplish this while referencing the milestone dates, but every time I use it the fo

Hi there,we try to find solutions for this use-case:We want to create "logic" URL's for our data-sets, what we can do at the moment is creating a formula "www.airtable.com/base-id/table-id/" & recordID().What we would like to define is the part recordID(), because all of our fields have a type-number (for example AA550). So it should be a formula "www.airtable.com/base-id/table-id/" & type-number with the result:www.airtable.com/base-id/table-id/AA550The reason for that is, that in the past often dataset were be deleted by someone or we have to transfer the database. We're using this URL's in an other database and if the recordID is changing, we have to check that again (yes I know ugly).It could be a filter too, if that's possible to save in an url.

I'm using airtable to manage all my seasonal employee hiring. I have one column where from a multiple selection I choose their job title (eg. Camp Ops, Kitchen, or Waterfront). I want another column that show what certification are required based on the job title I selected. I believe this should be a IF or SWITCH formula.Currently I have a working formula that is IF(Job='Camp Ops',"First Aid & CPR" ) But I can't figure out how to get the formula to also be IF(Job='Kitchen',"First Aid & CPR) and IF(Job='Waterfront',"Lifegaurding") and so on. 

Hiya! I spent a lot of time googling and trying to find the answer but not having any luck. I need to find a formula that displays the previous Monday date based on a date in another field. Would also love to know what each element does so I can learn a little too rather than straight copy/paste. Thank you so much!

Hello! We were previously using this formula to calculate a recurring date in the future: DATEADD("Publish Date", 7, "days")  However, we're now getting an #Error Message in our base.  I did some searching and found this blog from Airtable.  I tried using their exact formula in that blog as: DATEADD({Publish Date},7,‘days’) but it is telling me "Sorry there was a problem saving this field. Invalid formula. Please check your formula text"  Can anyone help me with what the correct formula is now? I am so lost....

Can someone assist me with writing this formula? I'd like to basically have a column in which the staff or faculty nominee column is combined with the value. For example: Cameron, Ali - Innovative SpiritI'm running into an issue were if the staff column is not populated the staff one is, and I need the names to pull from either or both- I tried using and "if" statement but am not having any success. Additionally, if there are multiple names linked, I'd like to separate them by a comma. Screenshot attached for reference of the table!

Hi there - I am looking to create a column that will essentially lookup the field values of columns Q1 - Q4 and output the latest number.We input quarterly projections and want to be able to identify the most recent number by looking at 1 field ("latest projections"). *Note - not all fields will have data from Q1 - Q4.I tried using the IF function but that only seems to work with 2 different fields max. ( IF(Q2> " ", Q2, Q1) )If I have data entered in Q1 field and then in Q3 enter in new data in the respective Q3 field, I want the "Latest projections" column to display that Q3 field since it its the most up to date #.I hope this makes sense. I will attach a screenshot for reference where I manually updated the "Latest Projection" field. 

Is there any substitute for INDIRECT in AirTable?

I have a table that generates specific tasks for clients on a frequency that required an interesting set up, but got me the result I was looking for (leveraging multiple formulas, jump tables, and linked records). Currently, the tasks are generated into a table called 'Client Work' that my team works the tasks out of. I have other team members that need to submit tasks to that same table, but are one-off tasks, not auto generated tasks. Unfortunately - the primary field (Task Title) is a formula generated field that combines multiple look up fields to generate the title, so I am not able to have Task Titles. I am looking to add an "OR" function, IF the fields that the formula is referencing is blank (aka: a work around to have another field that acts as the task title that will only be used whenever a task is generated via a form). For the task name issue – I feel like I have a potential solution, but need some guidance on the formula for this… The current formula is IF(

How to make a line break when I do strings concatenation? For example, I have 4 fields and I do a concatenation: "field_1: " & value_1 & ", field_2: " & value_2 & field_with_lots_of_text I’m trying to do smth like that: If I do like thins, it works ok: But I want to add more data and add a line break:

Can I add a table to “long text” field?I want to add table that include columns " code - description - price" example:code - description - price“1167 - bike tire - 75”

Hello,After the customer submits a request form to the client for order fulfilment, if the client has a follow-up question for the customer - Is there a feasibility/workaround to submit a comment/question on the request so that it can be re-directed to the customer for further clarifications/information? Can somebody suggest a workaround? Thanks!