Formulas

Hi!I have a countdown for our warranty cases, which needs to be completed in the 45 days or less. Our customer fills out the Form, and the countdown stars when the Form is submitted. So the first field is "Form date" DATETIME_FORMAT(CREATED_TIME(), 'D. M. YYYY')The second field is "Days" DATETIME_DIFF(TODAY(), {Form date}, 'days') And the third field is "Countdown"45-{Days} This works fine, but the problem is, that when the status is "sent" or "refund" the countdown don't stop. So the point is, that I want to know how long one case was open. I can also use just one status, let's say "complete" but now I stumble upon one problem.I don't have a specific date field for the end of the case (it depends on the case - some wants refund, some wants exchange or repair). So in this case I have two date fields (date

I have a string of text in one column that i'd like to separate into individual columns. For example: Row 1: K, 1st, 2nd, 3rd, 4th, 5th, 6thRow 2: preschool (ages 4+), K, 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8thRow 3: ages 3+, preschool (ages 4+), K, 1st, 2nd, 3rd, 4th, 5thFor my existing Row 1, I want a formula that will return each in its own column:K1st2nd3rd4th5th6thAs you can see, the length of characters isn't consistent, but the comma consistently separates the fields I want. How can I use the comma to separate out the text? I've found a way to extract the text before the first comma (LEFT({selection 4}, FIND(",", {selection 4}) -1), but can't figure out the re

Hello All,I wish to create an if formula which will return a Month Year result based on if a date falls on or between two dates.For example, if date A is on or between date X and date Y then return Month YearThink along the lines of budgeting and expenses:if Paid Date is 01/02/2024 (Date A)and because Date A is on/or between 15/01/2024 (Date X) and 14/02/2024 (Date Y) (Budget/Expense Period)Then return January 2024 (because January period is 15/01 to 14/02)

Hello everyon, so I have created this project management based on the template. I have got around 10 people in the group and how can I do that when each person logs into that base it will show tasks by name individually? Cuz for now you have to do it in condtions 

Hi everyone, I'm trying to reproduce the following Excel formulas in Airtable, which returns the third column from the "Tabella gest" table if the match is satisfied. INDEX('Tabella gest'!$B:$D;MATCH(1;('Tabella gest'!$B:$B=G2)*('Tabella gest'!$C:$C=BX2);0);3)I've two tables:- "Tabella gest", from which I have to retrieve the third column if the match is satisfied and use the columns B and C in the match, called respectively "Canale commerciale" and "Classe ABC" - "Input", which has the columns G and BX for the match function, respectively called "Tipo codice materiale" and "ABC ordinato" I don't have any idea if this is possibile and how I could do it, so I would appreciate anyone's help. 

Hello! I want to add something to the formula below. I want to add the condition that IF the column 'Fixed exchange rate' is not empty (there is a number filled in) THAN that number should be used BEFORE using the Forecasted exchange rate. IF the Column 'Fixed exchange rate' is EMPTY, than use forecasted exchange rate. Current Formula: IF(AND(   {(Hide) Weighted Historical Exchange Rate ⚙}=0, {Bankbook historical exchange rate}=0),{Forecast exchange rate ⚙},IF(AND({(Hide) Weighted Historical Exchange Rate ⚙}=0, {Bankbook historical exchange rate}>0),{Bankbook historical exchange rate},{(Hide) Weighted Historical Exchange Rate ⚙})) Thank you!

I’m pulling in 10 urls from Zapier in one field. (I can’t seem to split them despite trying the split text formatter) I have seen some similar formulas to split text but can’t get them to work either. I need some help so I can separate the URLs out.

Hi, Could you please help me with a formula I have 3 values: Estimated start dateEstimated end dateEffective end dateToday's dateI want to display "in progress", if I have no value in my effective end date cell and if today is inferior than estimated end dateI've tried the following formula but it doesn't work: IF(AND(({Effective end date})=BLANK(), {Estimated end date (Deadline)} > TODAY()), "In progress")Could you help me please? Thanks,

Is it possible to identify if there are any duplicates in a lookup array? I have 3 tables, all linked together: Entities (companies)MeetingsMonthsEvery Meeting is linked to a month (for various uses) and to an Entity. From the Entities table, I have data as such - the linked meetings and a lookup of their linked months. What I'd like to do is identify if a month appears twice in any row. I don't want to eliminate duplicates (i.e. ARRAYUNIQUE). Instead I need to see if 2 or more meetings occurred in the same month for any company. Is there any way to do that? 

I have a field that includes:NameEmailPayment linkPayment_made (checkbox)Created at (Jan 14)Send_reminder_1 (checkbox)Send_reminder_2 (checkbox)If the payment_made is not "check" even after 3 days.I want a send_reminder_1 to be auto-check or something with a formula that will say true.-------If the payment made is not "check" even after 6 days.I want a send_reminder_2 to be auto-check or something with a formula that will say true.-------How can I do this using formula?

I'm trying to create the following automated options in a project management base:2+ days remaining1 day remainingdue today!1 day overdue2+ days overdueThis is my attempt, but I'm struggling to work out how to write the multiple IF statements required to make it happen: IF((ISERROR((ABS(DATETIME_DIFF(TODAY(), {Milestone Date},'days')) & IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))<0," Days Overdue", " Days Lef

Hello, I'm trying to merge records in my table when 3 criteria match. I've been able to identify which records are duplicates, but I am having trouble merging them. Logically I believe my code should do what I want it do, I am not super familiar with asynch and I think that is what is messing me up. When I run this code it throws: Syntax error: await is only valid in async functions and the top level bodies of modules [script.js:33:9] I've been reading through documentation and discussion posts, but I'm not sure what is happening to cause it to throw an exception. Any help would be appreciated.   var table = base.getTable('Testing_500'); var query = await table.selectRecordsAsync(); //let records = query.records; let upPN; let upEM; let upID; //identify duplicates let duplicates = query.records.filter((record) => { return query.records.find((potentialD

Hi. I have a very basic password system that uses letters to denote numbers. The numbers come from a client's DOB.I have a date (DOB) field and another field using DATETIME_FORMAT to only show the numbers (DDMMYY). That's all fine.I would like to have a new field that displays a particular letter for each number - to create a very simple password. Lets say: 1=a, 2=b, 3=c, 4=d, 5=e, 6=f, 7=g, 8=h, 9=i, 0=jIs there a formula that would do this please? 

My airtable has two fields, for example, 1st field name is "Test" and 2nd field name is "Solution". Records from both fields are come from Lookup of other fields so multiple records appear in "Test" and "Solution". i.e. one record of 'Test' contains 'Test item 1, 2 & 3'. One record of 'Solution' contain 'Solution item 1, 2, & 3'.I use below formula to present Test item 1, Test item 2, Test item 3 in separate lines under one record of  "Test" fields. Solution item 1, Solution item 2, Solution item 3 in separate lines under one record of "Solution" field. SUBSTITUTE( ARRAYJOIN({Your Lookup Field}), ",","\n" )However, I would like to present the information in pair but separate line in NEW FIELD?  e.g. 1st line - Test item 12nd line - Solution item 13rd line - Test item  24th line - Solution item 25th line - Test item 36th line - Solution item 3Would anyone propose how to do it? T

I have a project stage field that I need to convert into generalized phases. I have the below SWITCH formula and I cannot get it to save. All of the articles I have read have examples formatted this way - What is the issue? SWITCH(  {Project Stage},   ’Discovery’, ’In Progress’,   ’Planning’, ’In Progress’,   ’Account Build/Import’, ’In Progress’,   ’Execution/Migration Prepped’, ’In Progress’,   ’Imported’, ’In Progress’,

Hello! I have checked out the the community for an solution to my problem and can't find what I am looking for to help me with my formula and automation. I am trying to set-up an automatic alert announcing someone's birthday on the day of their birthday. It did work but it sent the announcement the day before as well as on the day of the persons birthday.This is my formula: DATETIME_FORMAT({🎂 Birthday}, "MMDD") = DATETIME_FORMAT(TODAY(), "MMDD") My automation is set up for when a record updates, it sends an email on the day of the persons birthday. However, it sent an email the day before and the day of.I onl

Hi there, I am trying to create an If/or formula and got it to work but I can't get the formula to be blank if there's no data in a field. I'm sure that makes no sense. Here's an example: Let's say I have a small list of produce in a column. I want to create a formula in a new column to give me the category of produce either as a fruit or as a vegetable. I got the formula to work but if no produce has been selected I'm still seeing "Vegetable" in the formula column and I want it to return a blank value. Any ideas? Here's the example formula. Thanks in advance!IF(OR({Produce}="Grape",{Produce}="Apple"),"Fruit","Vegetable") 

I have a large multi-select that I then need to turn into a list format. I did the usual substitute the comma for a line break but unfortunately the items in this multi-select (that I can not edit) also use commas so now it's adding commas where I don't want them. Is there a way to get the formula to avoid those commas but not the others?  SUBSTITUTE({Plot Themes},", ","\n")

Please see screenshot below. I have domain names in one field and want a clickable "click here" text in another field that hyperlinks to the domain name, how can I do this? Thank you 

Hi all!I've been scratching my head and couldn't solve the problem quickly, so I'm asking for your help.I need to display a progress bar with the percentage of completion of a specific task, under which there are subtasks. In order to understand the percentage of task completion, we take the subtask as the source. If out of 10 subtasks only one is completed, then we get 10% of the main task completed, a certain run rate.It looks like a similar task can be implemented through a formula, but I was unable to write a correct formula using if, counting and division.How can this case be solved?

I want to create an Project Management Table with the following requirementsI have several project stageseach stage has a start date and an end date (automatically calculated based on duration for each project.)the start date of one stage should be dependent on the end date of the previous task. So if "task A" ends on July 5th, the start date of task B should be the 6th. The end date of task B should be start date of task B + duration,... and so onso whenever something changes, the following dates should change as well. (also reflecting the dedicated duration set for each task)I tried with formulas or dependencies but could not figure out a way to automate this, instead of updating all records manually if one date changes.ThanksHelenelene 

I'm trying to follow this guide, but everytime I reach the "Concatenate step" the link that shows up to me says "The private share link you tried to reach is not available.".That's a copy of the table I'm using to, why it doesn't works and how to fix it?https://airtable.com/appFaino3o1b2cqEG/shrHoTe908izs7JPn

I have rollup field that sum all the records from a table. I want to limit the sum from all to sum for current month only.How can I do it? 

Looking for a solution for a public view where I would like to share information partly. To be exact - I'd like to hide the last characters of the text string by replacing the last letters with stars ***Tried to look up for ready solutions but had no luck.Would appreciate any help.

Hi. I'm sorry if this sounds a bit vague, but what I'm trying to do is this:I conduct sessions with students and each session is a record. At the end of a month I have a table with multiple students and their sessions. Some students will have 4 or 5 sessions per month.What I want to do is find all sessions for each student, then extract the 'date' and 'duration' fields data to make a list of all the sessions the student has had in that month. I will then use an automation to email this information.I'm not sure where to start with this one. Is there a nice, elegant way to do this? 🙂