Formulas

Hi everyone, I have a Start Date(calendar) and End Date(calendar) I want to be able to generate the required ISO date string for all the dates inclusive of the Start and End Dates, such that it can be inserted into the [HOLIDAYS] option of the WORKDAY function. Initially I thought this would be as simple as finding a formula function, something like DateBetween() or DateRange() but alas, all I can find is some seriously huge formulas (1000’s of lines long) which don’t quite work and are impossible to edit without errors. Let’s say my Start Date is 25 July 2022 and End Date is 27 July 2022. What is the simplest way to get a string like this ‘2022-07-25, 2022-07-26, 2022-07-27’ Apologies if this has been asked before, but I couldn’t find the answer so happy to be directed to an already answered question if it exists. Thanks

Is there a way for me to Reference the Summary bar that is built into Airtable in another column with a formula? I know AirTable doesn’t have normal sum function (which makes my job so much harder) but I didn’t know if I’m missing an option or an extension that can reference those Airtable built in summary bars? I want the formula to be something like: If column Part name = xxx-xxx-1315-xxx, then reference summary bar of column QTY used, then if that sum is equal to 1,000, then “Correct” if not “incorrect”. I need to be able to do this with quantities that match 4,6,500, and 1,000. I currently use a switch function to achieve something like this with a very basic formula, but if we receive part numbers with different lots and they don’t total to one of my set numbers, it looks like it’s incorrect (but it’s not-it just has different quantities that all collectively should total to 1,000) Furthermore, rolling up data in to a separate column will require me

As a pianist, I have a base with all the works of the composers I work on. I mark the date each time when I work on a score, so I can see later how much time (in days) has passed since I worked on it (using DATEDIFF). When I work on it again, I change the date. Is there a way to count the date-changes automatically, so that I can see how many times I work on a score? Each time I change the date, the number should go one higher. I tried with (IF(Uitvoeringsdatum<TODAY(),“”,Totaal+1) but that only works for the present day. (the next day the condition doen’t apply anymore) Any ideas?

Hi AirTable community, I’m wondering if it’s possible to get the name of the field within a formula as a variable? Something like TODAY() but get the name of the column/field itself within the formula. Thank you!

I figured out how to make a histogram inline with the table and I thought I’d share: It was horribly tedious for me but it won’t be for you! This formula expects whole-number values between 0 and 100. Here’s the formula: CONCATENATE( SWITCH( ROUND({1}/100,2), 0.00,"_.", 0.01,"▁",0.02,"▁",0.03,"▁",0.04,"▁",0.05,"▁",0.06,"▁", 0.07,"▁",0.08,"▁",0.09,"▁",0.10,"▁",0.11,"▁",0.12,"▁", 0.13,"▂",0.14,"▂",0.15,"▂",0.16,"▂",0.17,"▂",0.18,"▂", 0.19,"▂",0.20,"▂",0.21,"▂",0.22,"▂",0.23,"▂",0.24,"▂", 0.25,"▃",0.26,"▃",0.27,"▃",0.28,"▃",0.29,"▃",0.30,"▃", 0.31,"▃",0.32,"▃",0.33,"▃",0.34,"▃",0.35,"▃",0.36,"▃", 0.37,"▄",0.38,"▄",0.39,"▄",0.40,"▄",0.41,"▄",0.42,"▄", 0.43,"▄",0.44,"▄",0.45,"▄",0.46,"▄",0.47,"▄",0.48,"▄",0.49,"▄", 0.50,"▅",0.51,"▅",0.52,"▅",0.53,"▅",0.54,"▅",0.55,"▅", 0.56,"▅",0.57,"▅",

I’m trying to write a formula that says. If {Inspection Date} is not blank and the number is greater than 730, then “No” or If {Inspection Date} is not blank and less than 730 then “Yes”. I keep getting hung up with the blank fields. This is my current formula: IF({Inspection Date Formula}!= BLANK(),AND({Inspection Date Formula}>730,“No”,“Yes”)). The output is now a 1 for NO and a 0 for Yes. Blank fields are still blank which is what I want. How do I get the 1 and 0 to say No and Yes?

Hello ! I’m looking for a solution to find the different strings in one fret and erase them in another. For the moment it looks like this: REGEX_REPLACE(Concat, Del,"")) But it doesn’t work if in “Del” I put: 8hKitchen 9hRoom And in Concat 9hRoom 10hDiner 8hKitchen 11hBathroom I would like to delete: “8hKitchen” and “9hRoom” (but it can also be of other occurrence, more or less.) Someone would have any idea ? Thanks in advance

When I create a lookup field of a multi select field I get the new updated version seen here However after reading recommendations in this forum when I try and create and arrayunique() from the look up it is not successful. (See Pictures) I have found that If I convert the lookup to a rollup and back again the I get the result that I am looking for. (See Pictures) I have gone back and forth from Lookup to rollup and I get the same result every time. I get the feeling that this is some kind of glitch or bug. Please give me any feed back that you

I’ve made this datetimediff formula with two lookup values that are both formatted in the way of date with the same time zones. Lots of the values are accurate, but some values are very off… I’ve attached screenshots here for reference. The most_recent_sold_date is a lookup from a rollup value, but I would think it would still work in the formula.

I’d LOVE to see the formula that Airtable used in their What’s New in Two July 2022 video. Specifically how they handled percentages over 100%. Very cool. https://www.youtube.com/watch?v=ktkizKuN5bU

I have 2 date columns that are only completed on some projects. I need this rolled up to show the MIN of each of these dates on another table. Arrayunique shows the value, but its not a date, its text. Any other formula like if value then min will only show 0. Essentially any time I add Min to the formula it is showing 0 because of the blank cells.

I am creating a base to keep track of a collection for insurance purposes. I have the list of items in one table and then I have a checklist of what I need for each item in another table all in the same base. I want to copy the items that are in the main table over to the checklist table’s main field dynamically. The main table is already a mile long column-wise, so I didn’t want to put the checklist in the same table. I thought about just using another view, but…it junks up my table, even though you can hide fields. Unfortunately, the type of field the primary field can be is severely limited, but it is able to be a formula field. So I was wondering if there was a formula that could use to dynamically copy/update the primary field with the items in my main table. For example, you can display items from another column with a formula field by simply using the formula: {FieldNameHere} Is it possible to “call” on another table? I only get one automati

Hello Airtable Community, I have two Long-Text Fields associated with the same record. I would like to make a comparison between both Long-Text Fields, which allows me to: Extract in a field the identical text Extract the differential text in a field I appreciate any leads on this, and thank you in advance for your help.

Hello, I’m currently copying the formula from our excel file to airtable. But some of the formulas are having errors in Airtable. Can someone help me what are the correct formula of these in Airtable to make it work? Correct Formula for: 6mo Avg =iferror(GPM*(if(Months#>6,6,“”)),“”) 6mo Avg% {WIP: 6mon Ave$}/{Purchase Price} ROI Growth =iferror(Actual Growth on Purchase / Cash Required,) COC Return =iferror(Cashflow / Cash Required) Total ROI =iferror((Actual Growth on Purchase + Cashflow) / Cash required) 6MA =iferror(PCM$ * (if(Month>6,6,“”)),“”) Thank you

What I need: For a field to update automatically with a status change if there is 21 days remaining before the clients renewal date. For example, if the next renewal date is 21 days away I would want a “Status of Renewal” field to say ‘Close To Renewal’, but I would also want to know if the member is still ‘Active’ or if they are past the ‘Next Renewal’ date then it has to say ‘Needs Renewal’.

Hello! I have a formula for a “status field” that keeps tripping me up. I have tried multiple ways and can get parts of it to work, but not all. Undoubtedly I’m a comma or similar away from the solution, but by now I’ve tangled myself so far in my own head that I have to admit defeat and humbly ask for assistance. I have the following columns that should be considered in the formula: {End Date} = Formula field {BiU} = Checkbox field {Last Shared} = Date field {Next Share} = Formula field What I want to achieve is: If {BiU} is NOT checked = ‘-’ If {BiU} is checked AND TODAY() is after {End Date} = :white_check_mark: If {BiU} is checked AND TODAY() is BEFORE {Last Shared} and {End Date} = :ballot_box_with_check: If {BiU} is checked AND TODAY() is AFTER {Last Shared}, but BEFORE {End Date} = :link: Any and all help would be greatly appreciated.

I need to make a automation that will reflect the date I update a Status field on my table to ‘Filed, Letter Received’. These are the two ways I tried, but i’m not sure what I’m doing wrong in both situations. Please help?

I know this is trivial and been answered before, however looking through other topics I still haven’t been able to solve this myself and my business needs it resolved swiftly. Formula designed to generate a date 14 days before “Dates Agreed”, I want the outputs formatted in the European fashion (DD/MM/YYYY) and without the time tacked on. Current formula; IF({Dates agreed},DATEADD({Dates agreed}, -14, “days”)) Thanks

I have sample db like this here i want to retrieve only Card colum for last 4 digit alone. I have used the below formula but it returns the whole data But i was success with other filterByFomula like searching InActive members from Status col like this Please help me to resolve

I’m trying to link multiple fields together and cap the number depending on another field’s selection. For example: Currently I have this formula working as intended in the a field: {X field} / WORKDAY_DIFF({Y Date}, {Z DATE}) I want to add a quantifier on top of that will determine the number it puts out. So, if field A=B, cap the number at 2 If field A=C, cap the number at 1.

I’m trying to format markdown in a long text field to something I can use in Zapier. The input looks something like this: # This is a heading 1 This is a paragraph - bulleted item 1 - bulleted item 2 - bulleted item 3 And here's a paragraph - with an embedded minus, and some more normal text The output should look like this: # This is a heading 1 This is a paragraph - bulleted item 1 - bulleted item 2 - bulleted item 3 And here's a paragraph - with an embedded minus, and some more normal text In essence, I need to replace the single newline after each paragraph with two newlines, except for the bulleted lists. My first strategy is to use REGEX_REPLACE(source,“\n”,“\n\n”) which adds the 2 newlines everywhere. So far so good. But for the life of me I can’t find a way to remove the (now) extra newline between the bulleted items (lines starting with a “-”). It seems th

Hello community, I’m using an Airtable form and Make’s (formerly Integromat) Airtable module “Watch responses”. Every submission trigger the hook twice within 5-6s. Has anyone experienced this or an idea of how to avoid the duplication? Thanks!

I want to make formula that tells you in what Season we are. I have the seasons divided as follows: Summer = July-Oct Spring = Mar-June Winter = Nov-Feb I have a column which shows the number of the month we’re in with help of the formula MONTH(TODAY()) Then in the column next to it I made a nested IF statement which should determine which the season we’re in based on the division I set above, but it’s only showing the season ‘Summer’ when the month column has a number between 7 and 9 (July-September). With the other month numbers it show no result (i.e. a blank cell). Here’s the IF statement I’m using: IF( {Current Month} >= 7, IF({Current Month} <= 10, 'Summer', IF({Current Month} >= 3, IF({Current Month} <= 6, 'Spring', IF({Current Month} >= 10, IF({Current Month} <= 12, IF({Current Month} >= 1,

I have tried to edit a formula in a shared base, my own existing bases and an completely new base and all I get is a spinning circle. I have a deliverable that is due tomorrow and I am dead in the water. The response from airtable is that they’ll get back to me tomorrow. HELP

Greetings Airtable Community, Several weeks ago I received great assistance with a formula quandary involving the combination of two multi-select fields: {College(s)} & IF({Department(s)}, " and " & {Department(s)}) This formula assumed that the {College(s)} field would always be populated. Unfortunately, as we’ve progressed, I’ve discovered this will not always be the case—there will be rare instances were the {College(s)} field will be empty. In these cases, the formula returns: “and Athletics” instead of just “Athletics.” Is it possible to adjust the formula so that it will return just the name of the department (i.e. the " and " will not appear)? Thank you very much!