Formulas

I want to make sure the cost of the services is split according to the months it's meant to run. For example, a Product is $16,500 and will run for 6 months (Start in Feb, End Jul), so Product Per Month is $2,750. I want to create a formula that puts a $2,750 in each field (image attached of my table). So in my mind the monthly formula should read: If Start is after 1/31/2023, than equal Product Spent Per Month. The formula that I have in each month is: {Product Per Month} and that's it. I don't know how to make the formula for If, than in this case.

Hi, I have a column with a date and time (starting date and time) in one tab and another column (Duration time) with a duration in another tab. I created a lookup in order to use this duration in a formula to get the endin date and time. I couldn't figure out why my formula is not working...Help ? 🙂

Hi All, I am trying to use the DATEADD formula to add a number of years to a date, The date is in one table and the number of years to add is in another table. DATEADD(Date,{Years till expiry}, ‘Years’) This just returns the same date as the date field. Annoyingly if I remove the {Years to Expiry} and just put a number in there it works fine, But i need to pull a different number of years depending on another field. I am trying to produce an expiry date for a qualification which is linked to a Google drive. the qualifications are listed in another table with the years to expiry number linked to each qualification. once this works i will want to run some conditional formatting in a report to see which qualifications are out of date. I have tried formatting the columns to integer and decimal. Thanks in advance Steve

Hi all! I'm trying to extract the ID from a google drives doc link, any ideas on how to do this? Knowing the ID of the file is the one after the /d/?

I have a field that gets the id from a different line, for example: iddepends on id (void if doesnt)statuscan be done1 In Progressyes21To Dono In this table, each task have an ID.This task might be done only after another task is done.For that, I want to

Hello! I am attempting to recreate some data I have in Google Sheet and improve my work flow. I am currently counting things by hand and it taking to long. What I am trying to do is create a "Master" sheet where all my info lives. But have a "Stats" table that will then automatically aggregate the data by Photographer and month. It would count the specific "Production Type" and sort it by "Photographer" and month. I have attached some screen shots. I have been trying to google it for days with not luck. Here is the start to my "master" tableHere is my current "Stats" tables I have in Google Sheets:By Month:It then feeds into a yearly data sheet

Hi, I have a column where the information is linked to a value in the same tab. I would like the information to be automatically filled with the information on the other line. For example on the screenshot, I would like to have John Doe in the cell for Brandon Walsh when I update the john Doe line.  I tried with the automation but couldn't figure out how to do that.  Thank you very much...

I have a formula for my primary field that looks like this: CONCATENATE({Last Name}, " - ", Type, " - ", DATETIME_FORMAT(Start, ‘MM/DD/YYYY’), “-”, {Job Address}) and it turns out looking like this: Thorton - G, DS, GC, - 08/17/2020- 692 Chateau Ln. Holly Lake, Tx 75461 I would like only the House number of the address to show in the primary field. How do I shorten the address in the formula? Thanks in Advance!

Hi Folks, I'm trying out using the rating field for a ongoing review process and want to be able to roll up an average of those ratings. I've managed to get the rollup to work but can't get rid of the NaN for fields that don't have ratings yet. I've tried a couple different formulas that I've seen for similar questions but nothing seems to be working. Thanks!

Here's what I want to do. I have a field that lists percentages for the fees my company wants to add to a contract. Admin fee 10%, Late fee 5%, etc. If I were to stack these fees, I want to have a field that returns the sum of each percentage multiplied by the contract subtotal field. Is there any way to do this? Like: SUM ( (10% x $800), (5% x $800) ) but where it could recognize the array and do the math for each percentage. 

I have created a formula that gives employees a personalized link to fill out a form so that when they fill out the form, they only need to answer the questions and don't have to put their name, employee id, etc. Is there a way to get around not having them sign in to use the personalized links?I have already turned off the "See who submitted this form" box and the under Share Link settings the "Restrict access to enterprise email domains" option. It doesn't make the person sign in with the regular link to the form, only the personalized one. Hoping there is a way around this. Any help would be greatly appreciated. Thanks!

I have a Table that lists everyone who has taken our leadership courses (Participant table). Fields include Participant Name and FLL Attended.I have a second table listing everyone who is a salaried leader in our company (600 records). I want to have a column (field) in the Salaried Leaders table that shows if they have taken our FLL course. How do I set it up so that the Salaried Leaders table links to the Participant Table AND populates the field with the class they took?  I know I can do a linked record, but then I have to manually select and that is inefficient.I know the answer is probably pretty simple, but I just can't get there on my own 🙄

Hi, I would like to extract only the 2nd character of a data field, to concatenate with another complete data field.Can this be done please? Many thanks.

Is there a way to easily setup and use “constants” across an entire Base?For instance, the price of oil per barrel might be 40. I have a lot of formulas that depend on that price. In addition, I might have several numbers like this that need to be referred to from multiple formulas - for instance the /mile that the IRS accepts for the mileage deduction, the number of tons of gravel contained in a gravel truck, etc.The advantage of the “constants” is that, if the price of oil changes, I need to change just one number in one place; I don’t have to hunt through tons of formulas looking for a hard-coded “40” in the formula.Is there an easier way to do this?

I am trying to combine an IF formula with a DATETIME_DIFF formula and I am not getting the results expected. Goal: Determine the difference between two dates to see how long something takes, but never have a value of "zero." I want the lowest value to always be "one."Start DateEnd DateDuration (what I get)Duration (what I want, instead)1/1/20241/1/202401 1/1/2024

So i have a problem that i have been trying to figure out for awhile now. Some of the data that I am tracking contains Julian Dates when i paste the data in. It is currently in the format of YDDD where 9045 = 2/14/19 or 0105 = 4/14/20 . I have been using excel to convert these 4 digit Julian Dates into Calendar Dates using the following formulas. For 2019: =DATE(2010+LEFT(C1),1,RIGHT(C1,3)) for 2020: =DATE(2020+LEFT(C1),1,RIGHT(C1,3)) This has been by far the fastest solution i had been able to find at the time to convert the date as all it takes is a few minutes to copy paste from airtable into excel and then copy paste into a different column in airtable the converted date from excel. I have not found a viable solution to convert the 4 digit Julian Date to a Calendar Date directly in Airtable so i thought about converting that 4 digit Julian Date to a 7 digit Julian Date (YYYYDDD). by breaking down the 4 digit date into several different col

Hi AllI am trying to build a formula to reflect Master project status based on (sub)project status. Here's what I have. So the hierarchy should be:If ANY of the project status contains "ongoing", then master project status to reflect "ongoing"If "ongoing" does not exist and any of the project status is "planned", then master project status to reflect "planned"If "ongoing" and "planned" do not exist and ALL of the project status is "hold", then master project status to reflect "hold"The remaining should reflect as "concluded"So in the below table, the correct reflection should be:Row 1, 3, 4, 6, 8 should be "ongoing" (because "ongoing" applies to any)Row 5, 7 should be "planned"Row 2, 9, 10 should be "concluded" Thank you!!

Hello!Situation:I have a table named « Leads » in which is all the information of my prospects (first name, last name, email, phone Numbers).These prospects, via a Tally sent by email, inform their needs with regard to 9 products (always the same). Some customers take all product references, others only 1, nothing is mandatory.With my current organization and a Make automation, the columns that have the title of the different product references are also in the leads table, which is not very optimized.So I created a new "products" table in which a row corresponds to a 1 product and each column to information containing the latter (name, reference, power).Here is my question: how can I ensure that when prospects indicate the products they want via the Tally, the information is properly / correctly stored on airtable?I don’t understand how to establish good relation/look up.The purpose being in the second step to concatenate the p

 

I have made a formula to calculate the date difference between start date and end date and returns a value in terms of day and hours as a fraction like "4.75" here is the formulaIF(  {End Date} >= {Start Date},  ROUND(DATETIME_DIFF({End Date}, {Start Date}, 'hours') / 24, 2),  'Invalid')But right know i want to change the above formula only for working days tho, i know there is a formula to calculate the working days difference  WORKDA

I have a column called “Date”. I’d like another column to look at my date column and if the date is in the same month as TODAY(), then it should say “This Month”. If the date is last month when compared to TODAY(), then it should say “Previous Month”. To be clear, I don’t want it to look at the previous 30 days. So, if a record has a date of 06/30/20 and today is 07/01/20 then that record would say “Previous Month”, but its month is in a previous month when compared to TODAY(). Does that make sense?

Hello, I have a cell with a date picking like "20240204 12:00" and I would like to format the date to "Feb 4th".I don't find how to reformat in this particular format.  Thank you for your help ! 

I've got a single select field for an address, and I want to autofill a secondary field based on that selectionIf 123 Street is selected, then it returns the corresponding email, etc.I'm using the below formula, but it keeps returning info@email.com regardless of the selection. IF(OR(Address="1234 Street",Address="1234 Road"),"Info@email.com","Info@email2.com") What am I doing wrong?

So, I have over 3,000 people in my database that people can opt in to help the charity in numerous ways like being: volunteers or subscribers or sponsors or trainers etc etc but some people has said they can help by doing a few of those options.My problem is that this data is pulled in from other platforms (Active Campaign, google forms etc etc) and I have zapier populating my CRM everytime a new form is submitted.  This means I currently have 3 records for a 'Mr Smith@email.com' as he offered to be a 'volunteer' and a 'sponsor' and he as subscribed to our newsletter.  So he now has 3 different record ID's as my CRM creates an ID for each new entry. I need a formula or an automation that creates a Unique ID that is linked to an email address so if he registers for a 4th option his Unique ID will automatically populate the ID field with the same ID as the other three if that makes any sense at all!! 

I have a column of time duration.  How do I add all of those durations together?Picture to show what I am trying to add up for a total time