Formulas

Hello, I need to create an aging formula with an If statement to track when a record is open with a count of days until it gets closed and ultimately stops counting.  I then want to be left with the difference of days between the date opened and the date completed,  in the days-old column.   Currently, the Days old column counts days open from the date the record was created.  DATETIME_DIFF(TODAY(), DATETIME_PARSE({Opened date}, 'MM-DD-YY'), 'days') and just keeps counting.  Any help is appreciated!   

Hi everybodyI am having trouble with an IF formula.As background – I have a table that includes three multiselect fields. I am trying to count the number of instances of a particular keyword (could be a single word, or multiple) in each field. Below is the formula I am trying to use:  IF({KW Taxonomy L1}=Natural Sciences,"Yes","No")Which isn’t working.The fields are not linked to another table. Should they be? Am I barking up the wrong tree and should be using another formula entirely?Many thanks

Hi everyone,I'm having trouble with this formula. Can you tell me what I'm missing?It comes up blank.Thank you!SWITCH({Recurring Frequency},"daily", DATEADD({Date to work on it}, 1,'days'),"weekly", DATEADD({Date to work on it}, 1,'weeks'),"monthly", DATEADD({Date to work on it}, 1,'months'),"quarterly", DATEADD({Date to work on it}, 3,'months')) 

Hey guys,New to AT, and needed some guidance on what I'm trying to accomplish, and if AT can do this via the forms. I'm designing a form and was curious if I was able to select an entry (via drop down), and then it'll auto populate another field with whatever said user selected in the first field (drop down).I tried this with conditional statements, but maybe I'm lost.Ex #1: John Doe selects "Timecard" as an entry in the drop down field > The next field is populated with whatever is associated with "Timecard".Ex #2: Jane Doe selects "Building 1" as an entry in the drop down field > The next field is populated with whatever is associated with "Building 1".I wanted to make this form pretty simple for whoever's entering information. I can prefill those entries out in AT and possibly have it hidden - upon selection. So if John selects "Timecard" the next field will bring up options for him to select, rather than John manually entering into the field and possibly entering a typo.

This seems like something I should have been able to figure out, but I've been struggling to come up with a solution. Duration fields are handy for inputting data, displaying data, and manipulating it in Airtable. But the underlying data is the number of seconds in a text string. For example, one hour, seven minutes, and seven seconds will show as 1:07:07 but the underlying data is "4027". When I use the output via automation, the field output is 4027. I want to use 1:07:07. Does anyone know a formula or automation that I can set up to convert seconds into a HH:mm:ss text output?

Hello everyonePlease I need help with a formulaI have a field of start date, a duration field and an end date field.I need an airtable formula that once I put a start date and the duration of the project (2days) the end date automatically populates.That is I have a project starting 2/1/2023 and my duration is 3 I.e 3days..I want the end date to automatically populate as 5/1/2023

In my CRM, I am trying to compare two date values in a formula, in order to figure out which "Next step" value is the most recent. In my Interactions table, {Last contact lookup} looks up a value in my Clients table which is the output of a rollup that outputs the MAX date value from interactions for a given Client record. {Actual interaction date} is the result of a formula that compares the Interaction record's creation timestamp to an optional user-entered value, and outputs the user-entered value if it's non-zero.My formula is simple: IF({Last contact lookup}={Actual interaction date},{Next step}). Unfortunately, that formula fails to produce results. The fourth row should have "Find information" in the Last next step column. Any ideas why this doesn't work?Just looking at the screenshot above, it looks like a problem with formatting, given how the timezone appears. But Airtable understands the lookup value as a date:My only other thought is that the problem is that Next step is a

Hi Airtable Community,I am creating a form to be filled out by a team of developers. I would like to share a form that team can fill out with their full name - select a sprint (2 week date range) - leave feedback on working hours day to day (which days they are free to work) - prefered time to work (daytime, midday, latenight).I am having trouble creating the sprint 2 week date range as a question in the form and the feedback for working hours. Would like to create something similar to this link: linkWould love and appreciate any help here.Thank you team!

Hi there,First question on the community 🙂 We are moving our social media calendar to Airtable, and it will be very useful if I create a formula or automation for a certain need.As in the attachment, we use a loop of BG colors for each post on Instagram, and it's repeating every 4 posts as: Blue, Cyan, Other, Cyan. What I need here is when a new record is added to the view (Instagram), if the previous field in the column is Blue, it should print Cyan, and if it's Cyan, it should print Other. I can use Cyan2 for the second Cyan to avoid confusion. Is there any way to do it in a simple way with formulas or automation?Thanks in advance!

Hi everybody,During my continued transition from Google Sheets I encountered a problem that I can't really find a solution for (or I am not understanding the other posts on this).Here's he situation: In Google Sheets I have three sheets - orders, customers and overview. In the latter I could change variables to see different information from the other two, such as the total number of orders for a customer in a given time period. To display this, I used an Arrayformular with SUMIFS, so I could check for multiple conditions based on my entries (e.g. "from" date & "to" date, order sum larger / smaller than, etc.). I understand that in Airtable "Rollup" seems to be the feature to replace this. BUT: When setting the conditions for these rollup fields, I can not reference to other sheets or fields and have to enter static values. So I cannot say "display all orders after <field A> in same sheet" but have to enter 01/01/2022. This is a serious issue for me. Does anybo

Hi guys, I am using a concancente function along with autonumber to get something like this X-2022-1  as a unique reference field for a record.However, I am wondering how we can do something similar (sequential numbering) that loops back to 1 at certain intervals such as year. 

Hello,I am creating a time to value metric reporting system. I created a formula that calculates the days in between the student's enrollment date and the date they submitted their first milestone in our course. I would like to find the average (total average days for all students) for all the days in the "Number of Days to Submit Goals Worksheet" Formula field (please see below).

If I have these values in a formula field, 0_99_123_456_abc_wantedtext1_ppp_876_333_wantedtext2hhh_kol_ggb_hhh_sss_wantedtext3can someone please tell me how to extract the following text into a new field please?wantedtext1wantedtext2wantedtext3Thanks in advance.

Evening! I am trying to create a table with multiple purposes: 1. Share and store meeting notes2. View date for next meeting 3. See who attends each meeting - this is the one I need help with. _______ So I have created this table:Each Record has its own form link. The form currently contains a Single selection option with the names of the users and the name of the meeting. What I need now is to somehow include the date of the meeting so that when my colleagues receives the form, they'll be able to also see the date of the meeting; w/o the date it will be difficult for them to answer yes or no to the invitation. In the shown table, I have created a formula that automatically generates the next meeting date based on a rule (eg. 14 days from now). Can I in any way include that date field into the form? Thanks in advance for your assistance!  

Hi all, so I have this situation in which I would need to use a double IF.- Chechbox Field- Formula field where: IF checkbox is selected > Value1; if not selected > Value2- Then I have a subsequent Single Line Text field in which I need to have another IF in the formula field above where: IF this field is NOT empty > Value1.Attached are a couple of screenshots. I have the first IF formula down already, I can't come up with the other one. In the second screenshot, where "DANIELA" is typed on the last field, the IF formula should bring me back to "OK" (Value1).Hope I made myself clear enough, would be super helpful if anybody could help me!Thanks

Hello all my first post and i'll probably be embarrassed if answered but, I have a formula in excel that tells me how many total years and then months and then days since someone has been hired and I'm not quite there with Airtable as I am getting how many total years and then total months and then total days. 

Helle everyone! I am trying to make a table where I'll have an overview of different kind of meetings. Let's take meeting A as an example. This meeting happens every 14 days. When the meeting has happened (the date is yesterday) I would like a new Record to be made automatically with the updated Date (14 days later). The reason that I would like a new Record to be made is that each Record will have a document attached with a resume from the meeting.I've tried different approaches but yet to hit the mark. Can someone help me make this happen? Thanks a lot! 

Hi, I would like to know how to get the number of Monday per year. I’m searching in the formula date but i don’t find nothing about that… Could you help me. It’s for calculate the number of hour per day in a year. So i must know the number of “Monday” for example.

Hi!I have a single select field with two campaign options: Paid Media and Email Nurture. I am trying to create a name field that would concatenate different values, depending on the selected option. I wrote the following line, which is not accepted.IF({Campaign Type} = "Paid Media", "CONCATENATE{Campaign Tactics}, " — ", {Asset}, " — ", {Webpage}", IF({Campaign Type} = "Email Nurture", "CONCATENATE{Campaign Tactics}, " — ", {Steps}, " — ", {Persona}"))Would appreciate any suggestions on how to write this correctly and make it work. Thank you!

Hi guys,We have a field that lists the publish date + time for a piece of web content. We want to be able to organise all of these dates into 'week commencing', ie. add a field that gives the date of the Monday of that week. So, an item published say on Thurs 22nd December 2022, falls into 'Week Commencing' 19th December 2022. Something published on Friday 23rd December 2022, also the same Week Commencing date. The formula we're using to do this is:DATEADD({CONTENT PUBLISH DATE},1-WEEKDAY({CONTENT PUBLISH DATE}),'days')And it sort of works. But it seems to want to force some content pieces from the end of the week into the next week and I'm unsure why. For example, I have a piece of content dated for the 11th December 2022, 3pm, yet it forces it into week commencing 12/12/2022. Any ideas why this might be?

Hi all,The problem I'm seeing is when I use the formula below, the recurring date shows up properly in the current year, but one day to early.DATETIME_PARSE(DATETIME_FORMAT({Recurring date},'MM/DD')&"/"&YEAR(NOW()))This 3 minute video I made shows the problem:https://www.loom.com/share/913091fb457b424b9836b6369c75bb10 I realize the problem is likely caused by the Kamillionaire calendar block not considering that 12am (midnight) is really the current day vs. the day before. So my work around I'm hoping for is to modify the formula to show a time after 12am, like for example 6am. Kamillionaire site:https://kamillionaireapps.com/ Thanks in advance for any suggestions,Kelly  

Hi all, I’m building a client portal, where tasks would be executed according to their priority.The client will assign priorities to their tasks to designate the execution queue.The task priority should be unique and shouldn’t overlap (doubling).Example:Priority values are: “1st in queue”, “2nd in queue”, “3rd in queue”, and “4th in queue”Three tasks in the pipeline:Task_X has priority “1st in queue”Task_Y has priority “2nd in queue”Task_Z has priority “3rd in queue”A client created Task_AAA and prioritized it to “2nd in queue”The new tasks order would look following:Task_X has priority “1st in queue”Task_AAA has priority “2nd in queue”Task_Y has priority “3rd in queue”Task_Z has priority “4th in queue”Any thoughts on how I can achieve this?

I want to have a unique ID for single and multi-select fields so that I can use it with formulas and automation, without scripts.

Hey friendsI have 2 tables that are divided into 2:1. A table of all the leads.2. A table of people who paid.I want to create a column that will check in table 1 whether a certain email of a record appears in table 2 and if so - to add a V in this column.How can this be done?I tried the option of linking but it only connects the leads for me and doesn't do a check on all of them.Thank you. Attach photos.

Hello,I'm new to logic formulas and need some guidance. I have an AT to track company orientations. I have three columns- {Start Date}, {Orientation Complete Date}, and {Orientation Status}. I would like to create a formula for {Orientation Status} to show "Complete" when {Orientation Complete Date} is filled in, "Almost Due" when {Start Date} is within 14 days of todays date and {Orientation Complete Date} is empty, and "Overdue" when {Start Date} is after 15 days from todays date and {Orientation Complete Date} is empty.Any help or a link in the right direction would be appreciated!