Formulas

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!

Hi!Right now I get an #ERROR in Column A when the date is blank. However, I'd like it to return "N/A" instead.The formula below still results in an error.  What do you suggest?IF({Formatted Date},CONCATENATE({Formatted Date}," - ",{Event/Release}), "N/A")

I would like to do what was accomplished on this thread except to add the additional steps of updating another row in another column once the loop reaches a specified number. Please see the picture for reference on what I would like to accomplish:

Hi,How should I write the IF function to calculate commission based on A or B ? See below:If Take Rate is filled: Commission = GMV x Take Rate %Otherwise: Commission = GMV - Achat DistributeurThank you    

Hi,In one column I want to get shown a trend more obvious with colours.So I want the difference to the week before and in this case a green or a red broken heart.But somehow I am not allowed to put the heart at the front and the difference at the end ...(se result: NaN) 🤔 🤔Any idea, what I cannot see right now?Best regards,Martin

Have you integrated your Airtable bases with a backend data service/database/or used Integromat to draw information for other bases? Can you help with the below? We’re a GAAS platform that use Airtable to execute various company wide and customer requirements. One of these specifically is how we execute growth projects for customers. We have a central project template with predesigned workflows that we rollout for each customer project. One problem here is that version control gets hard to manage given that Airtable doesn’t allow you to create parent/child templates, ie so you can deploy changes to a template to all active projects that utilise that specific template. As a means to deal with part of this problem we’d like to build out a backend data service that would enable us to pull in generic information into a base and keep that part at least centralised. An example use case: A user on the project team creates a new experiment within the relevant tab on a project base. They will

Hello,Thank you in advance for your help. I explain you my project 😃I am in charge of a product sourcing agency.I need to make a catalog of my products, but I need to be able to change the price of the product depending on the country of delivery.Example:Product 1 : USA : Price 10 DollarsProduct 1 : FR : Price 15 DollarsProduct 1 : CA : Price 20 Dollars.If you can help me with this it would be great 😃I don't know how to do it, with a select single maybe?

What is the excel index match equivalent in a table?

Hi there! I'm new to Airtable and trying to figure out how everything works. Let's say I have a cell that as the following information I inserted below: Beef Onion Burgers with PicklesIngredients:2 hamburger patties1 onion, diced2 tablespoons ketchup4 slices pickles2 hamburger bunsHow can I separate all of the text to the left of the word  "ingredients" into one cell? I basically am trying to extract the name of the recipe into its own cell.

Hi all, does anyone have a formula to calculate Stamp Duty Land Tax (UK) in Airtable? I attach the Excel formula that I previously used. =SUMPRODUCT(--(A1>{125000;250000;925000;1500000}), (A1-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})+(A1*0.03)Thanks in advance!

I have a table called 'accounts' and another one called 'users' linked to accounts in a one account to many users relationship. I want to create a field in the table users that shows an array with all the other users in that account except themselves. I wonder if airtable would allow me to do that?I have tried with a rollup but I don't know what to enter in the aggregation formula to exclude the user in the record, so I get a list with all the other users in their account.

Would anyone be able to help me adapt the following formula to Airtable?N = –[ln(1 – [(PV * i) / PMT_] ) / ln(1 + _i)]N = Number of months remainingPV = Present ValuePMT = Payment AmountI'm struggling with an otherwise simple operation because I'm not sure how to account for the natural logarithm in Airtable. I'm ok with something that gets close enough, because the idea is I just want to arrive at an estimated payoff date. Doesn't have to be 100% accurate, just a decent estimate.Thanks for any insight.