Formulas

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/20241/2/2024111/2/20241/4/2024221/4/20241/4/2024011/5/20241/10/202455 I started with this DATETIME_DIFF formula to calculate duration:DATETIME_DIFF({End Date},{Start Date},'days')Then I tried introducing an IF formula to replace "zero" with "one" when duration = "zero" (I tried many variations on this):IF(DATETIME_DIFF({End Date},{Start Date},'days'),0,1,DATETIME_DIFF({End Date}{Start Date},'days'))Airtable accepted some variations on this formula, but when it was accepted, it was automatically changed to this:IF(DATETIME_DIFF({Date Finished},{Date Started},'days'),0,1)And the result is this:Start DateEnd DateDuratio

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 columns in Airtable and using additional colum

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 product infos + quantity in order to be able to place it on

 

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  WORKDAY_DIFF({Start Date}, {End Date}) hence it returns in terms of days only like "4" but i want to return the hour also like "4.71" 

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

HiI need a function that I will use to count the numbers of the same value in a column here is an example that explainsthinks

I'm using a date field {Date Processed} and a formula field {Report Periods} to interpret that date and produce a comma separated list to find the year, quarter, month, week, and day. I use this for reporting. Check out this screenshot showing how 12/31/2023 (USA time format) is messing up the formula.The final day of the year should be in the 53rd week of the year. Instead it's starting over at week 1. I believe this is a bug in Airtable. But please let me know if there is any known solution. The formula for the {Report Periods} field is:IF( {Date Processed}, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( DATETIME_FORMAT({Date Processed}, "YYYY-ⓨ, YYYY-ⓠ-Q, YYYY-ⓜ-MM, YYYY-ⓦ-ww, L"), "ⓨ", "year"), "ⓠ", "quarter"), "ⓜ", "month"), "ⓦ", "week" ) ) 

Is there an airtable formula that combines linked records from a column (say column 1) and another column (say column 2) and outputs it as linked records in column 3?  If not formula is there a way to do this without scripting?

Hello everyone,I've been racking my brain on this question for over a week and have tried countless attempts to solve this, but am still spinning my wheels. Basically I'm aggregating total work history of all our staff across all our events this year, so I need to calculate how many shifts someone has worked. The issue is that it's possible to have multiple records of the same worker per event because different supervisors may submit the same record with different ratings.In my example, here are all the reports (records) that we received on 1 worker for the year (Table 1):Here is the summary record (Table 2):In the summary record (Table 2), Total shifts should be "9" and successful shifts should be "6". The current formula in the ROLLUP SUM(ARRAYUNIQUE(values)) doesn't work because if any event has the same number of shifts as another, it'll get skipped. Because there's 2 records of DF23, I can't use SUM(values) on the "# SHIFTS" field either. I've been able to identify

Hi!So I'm trying to set up a table for our home school group to house everyone's birthdays. I've scoured the forums and can't work out why I'm getting an error on all dates on or after 13/05/2019. All dates prior to this work fine and I just can't figure it out! I also wanted to see if I can incorporate a set_timezone formula to ensure it's based on my Australian timezone. Here's what I have set up:DOB field (members will enter their DOB via a form) and a formula field "Birthday (this year)" with the following formula: DATETIME_PARSE(DATETIME_FORMAT({DOB},"DD/MM")&"/"&YEAR(NOW())) Questions:1. I'm getting an ERROR for any DOBs that are from 13/05/2019 onwards. 2. Do I need to add a SET_TIMEZONE formula to set the timezone to Brisbane, Australia to ensure the 'NOW' portion of the above formula will be correct throughout the year? 3. If I wanted to add another field titled "Birthday (next year)" how would I add 1 year to the above formula? Thank

I have the following formula to set the entry for "Current Month": DATETIME_FORMAT({🟢 Start Time}, 'YYYY-MM') = DATETIME_FORMAT(NOW(), 'YYYY-MM') It seems to be working just fine, however the last entry from last month is setting the formula to 1.If I change the start time (right now) to 6:30pm or anything older, the formula gets set to 0. My first thought was a timezone issue, but I confirmed on the Start Time field that it is set to my timezone (CDT).Am I missing something obvious?

I have a date & time field.For example, below is the data. I will have a different date and time and 100 rows with data.1 Jan 2024 1:00 PMAt 1:00 PM on 1 Jan 2024, I want to send a Slack message using airtable automation.How can we make this trigger?My ideas: using formulas and views

Hi there!I am trying to create a formula that will pull the Last Modified date and combine it with my initials and my Pending Items column. The formula I'm using right now works, but the Last Modified date is pulling tomorrow's date. I'm assuming this has something to do with time zones because it doesn't switch the date until 6pm CST, but I'm not sure how to fix it! Please help 😊Table:Formula:CONCATENATE(DATETIME_FORMAT({Last Modified},'M/D/YY')," ","RE: ",{Pending Items}) 

I'm working on my graduation project, using Airtable as an API. In the API documentation, specifically in the "create records" section, I'm having an issue. When I try to copy the 'YOUR_SECRET_API_TOKEN' for authentication, it stays as plain text and doesn't convert into the expected key format.Has anyone else run into this problem? Any ideas on how to fix it?Thanks for your help!

I am trying to put a formula I pulled together in the primary field, but get the following error "Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference".  Back story, I am using one tab to house all the event details. I am using the nesting feature to pull through things I need for all the sperate views (agenda, session intake, speaker details, ect.), where I would usually use separate tabs for each of these things. Here is the formula that I have pulled together. Depending on what Is selected in the Content Track field it will pull a particular reference field. I want to use this formula in the primary field, but even if I remove the "for column 1" field I still get the circular reference error.  Any one have any suggestions?     <--- THIS IS MY FORMULA    Here is a snapshot of some of the fields. [ Pink highlight I want to pull the formula I created and that works

Hi Airtable Community,I am having an issue with a formula that I was hoping to get some help on. Here is the issue:We do our invoicing on Airtable. We have a few different types / categories of invoices - they are as follows:Quotes: This is what we use when we have to submit a price 1st before any approvals to begin have been granted.Suppleental: This is like a change order - we use this when the job has changed and or scope was added.Estimate: This is used  when we have submitted a price but it has not been approved - what we call "settled"Invoice: This is what we use when we have submitted an estimate and it has been approved. We have the primary field be a concatenation field that takes the name  of the customer, the type of work it is, and then looks to the "(MIT) Settled Date" field and if it is empty, uses the word "ESTIMATE" as the last word of the primary name. The thinking here is that when there is no date in the "(MIT) Settled Date" field, it has not been appr

I got a table with DOB in date format dd/mm/yyyBest way to filter those records whose anniversaries (mm/dd) are within 5 days ahead?

Can someone help me figure out why the ‘else’ part of my IF statement isn’t working? I'm trying to create a friendly date/time/timezone field IF a certain date field is not blank. That part is working. But if the date field is blank, I want it to put “N/A” in the new field. That part is returning an error. Here is my formula.IF( {REQUESTED GO LIVE DATE} !=BLANK(),  DATETIME_FORMAT({REQUESTED GO LIVE DATE}, 'M/D/YY') & " at " & {REQUESTED GO LIVE TIME} & " " & {GO LIVE TIME ZONE}, "N/A")I also tried it this way:IF(  {REQUESTED GO LIVE DATE} !=BLANK(),  DATETIME_FORMAT({REQUESTED GO LIVE DATE}, 'M/D/YY') & " at " & {REQUESTED GO LIVE TIME} & " " & {GO LIVE TIME ZONE},IF({REQUESTED GO LIVE DATE}=BLANK(),"N/A"))But neither formula is recognizing what to do if the REQUESTED GO LIVE DATE field is blank. Oddly, it is working when I remove the first part of the formula to only look for blank date field. IF({REQUESTED GO LIVE DATE} =