Formulas

Hi everyone, I searched through the community posts but could not find anything.Using a formula I created custom URL for every person in our company that will allow prefills in a form.CONCATENATE('https://airtable.com/appxxxx','?prefill_Your Name=',Name)for example 'https://airtable.com/appxxxx','?prefill_Your Name=',John Doe)Problem is that there are now blanks and therefore only the first part of link is recognised as such. I know that I can use %20 is used in the final URL. But this does not make the URL in my dataset clickableAnyone an idea what I would need to do? Thanks for your help

I am using Airtable to track hours worked versus workable hours in a month to try and calculate how much overtime there was in a given month. The table below is correctly doing what I want where # Hours Worked is the number of hours a person logged in a given month for different tasks. Total Hours is work days including holidays, Total Work Hours is work days not including holidays. And Hours Worked - Holidays is self explanatory (we don't want to count holiday time towards comp time). Looking at the below screenshot, the comp time would be 1 hour (161-160). The problem is that I don't think you can make a formula that can do math with the summary types. I was trying to do rollups in a different tab but it wasn't working. In excel I would do a SUMIF function where the criteria is that Month = Sep 2023 and Resource = X and have it sum total work hours and another one summing hours worked - holiday so I can subtract them.I'm trying to do this by resource by month. Does anyone know a

Hello,I have a formula field that is supposed to return a value ("updated") when a last-updated field is today.I live in another time zone, but all of my dates are set to Eastern time to reflect my working hours. Some of the posts I have read have said that a time zone error will produce this problem. To show that all time fields are set up for Eastern, I am including screenshots below. You can see that the last updated date/time is today (10/10), and the value of today is also 10/10.The formula field is not showing the value "updated." Here is the way the formula field is set up  Here is how the last-updated field is set upHere is the way the start date field is set up  I have also tried using "now" instead of "today."I am sure that there is an explanation, but I'm unable to find one based upon my searches of the community posts.Thank you for any help you can provide!   

I am trying to incorporate a formula column which looks for entries which may include swear words, but I cant seem to get this formula to work. Here is a snippet (I've taken out a lot of the middle which is just 3230 different swear words) but the beginning and end are there. Any ideas? IF((OR(SEARCH(“2 girls 1 cup”, ARRAYJOIN({Lower})) > 0, SEARCH(“2g1c”, ARRAYJOIN({Lower})) > 0, SEARCH(“4r5e”, ARRAYJOIN({Lower})) > 0, SEARCH(“5h1t”, ARRAYJOIN({Lower})) > 0, SEARCH(“5hit”, ARRAYJOIN({Lower})) > 0, SEARCH(“a fanabla”, ARRAYJOIN({Lower})) > 0, SEARCH(“a pecorina”, ARRAYJOIN({Lower})) > 0, SEARCH(“a tomar por culo”, ARRAYJOIN({Lower})) > 0, SEARCH(“a tomar por saco”, ARRAYJOIN({Lower})) > 0, SEARCH(“a$$”, ARRAYJOIN({Lowe

Hey guys,Just trying to work out a simple Formula to create a day count until a certain date (based on one of our fields) without including weekends. For example. We have a customer due date for an order which is put into a field, just wanting to have a field that calulates how many days we have for production until that date. So far I have: DATETIME_DIFF(  {Due Date},  TODAY(),  'days')Which works fine but is including weekends. To combat this, I did try:WORKDAY_DIFF(  {Due Date},  TODAY(),  'days')But this is throwing an #ERROR. I know I am definetly missing something. Any help is appreciated. 

hi, when i use the field #number, the number always look like this 1.00but i i want to put my numbers as 0.01 how can i do that? because everytime i put 0.01 they change it to 10 

Hello,I program a film festival and we use Airtable to organize the submission we receive. This year we have two different programming teams that should watch half of the submitted projects. I wanted to create a formula that would calculate the total of duration of the films (we have a field for the duration in HH:MM:SS), divide it in half and then assign each half for team A or team B. Is that possible? I have tried to add some simple SUM formulas but I'm still a beginner in all of this. Thank you!

I am looking to remove the comma separating the two lists. Here is a picture to show an example.    Is there a way to do this?Thanks!

Hello everyone,Trying to come up with a formula using Chat GPT (and some tweaking) to create a formula field type that displays a subscription's (record) amount in a cell based on budget status and the payment schedule.I want to create a formula that checks the following conditions:The Payment Schedule is "Annually."And one of the following is met:a. Budget Status is "Projected" and Renewal Date is within the specified range.b. Budget Status is "Actual" and Purchase Date is within the specified range.If these 2 conditions are met, it displays the {Subscription Amount}. If none of the conditions are met, it will display "Conditions not met".Here's the code:  IF( AND( {Payment Schedule} = "Annually", OR( AND( {Budget Status} = "Projected", {Renewal Date} > DATETIME_PARSE("2023-01-01"), {Renewal Date} < DATETIME_PARSE("2023-02-01") ), AND( {Budget Status}

I have a base in Japanese and want to use Japanese field names but when I tried to put together a formula, it does not work.  Is there a work around for this issue?

In my "CRM" database, I am building out a reporting table that gets rollups of IDs from other tables (i.e. interactions, etc) and am trying to get one array of IDs that I can count to determine the number of unique IDsInteractions TableOne to Many Contacts, lookup field of Contact Record IDs from "Contacts" TableProjects TableOne to Many Contacts, Lookup Field of Contact Record IDs from "Contacts" TableReports TableRollup of Contact IDs from Projects, Rollup of Contact IDs from InteractionsMy formula below doesn't get all the IDs. I don't know if maybe there's some nesting going on. Any suggestions for combining these arrays? properly? COUNTA(ARRAYUNIQUE(ARRAYFLATTEN({Interaction Contact IDs}, {Project Contact IDs}))) Each of my rollups in Reports has this formula ARRAYUNIQUE(ARRAYFLATTEN(values))   

Hello all,I need a formula that will delete every instance of a string of characters between [] including the brackets. Basically I'm pulling a products from a database which is SKU code then the product name. Every SKU code is like the following [SHU-BATT-22-EU] SHURE BATTERY 22. Is there a way to remove any code between [] including the brackets so I am just left with the product name? Thanks in advance!

Howdy, Recently encountered a twitter account that I like which posts a progress bar for the year. I would like to make an extension that simulates that, so made a field that displays the day number of the year; specifically: DATETIME_FORMAT(NOW(),"DDD")However, when I am trying to then convert that output into a percentage, I only get an error.DATETIME_FORMAT(NOW(),"DDDD")/365I'm very confident there is a simple detail that I am overlooking. Would any of you happen to know why? 

Hello,This one has me frustrated because I know it can not that hard. 😁I need to create an Expected Ship Date field.  I have a Start Date field (formatted as Date) and a Ship Days (lookup field from another table).  I want to find out how many days prior to the start date I need to send out the package. First My thought was Start Date - Ship Days, that just gives me NaN message.Then I thought I needed DateTime_Diff(Start Date,Ship Days, 'days').  That gives me a 4 digit number in some cases and NaN in other cases.  i.e. 1/3/2024 (Start Date) - 7 (Ship Days), I get 8221.  I am thinking that is good and just need to DateTime_Parse it.  So I enter,  DATETIME_PARSE((DATETIME_DIFF({Start Date},{Ship Days},'days')),'MM/DD/YYYY'), and I get #ERROR!What am I missing?

Hi people! I’m building a “Birthdays” table in my “Calendar” base and I’m looking for a formula that says: if “Birthday” is within the next 2 months then display “soon” What would this formula look like?

I want to combine multiple IF statements from data in two columns and return a TRUE or FALSE result.The two columns/data I am using are:User Subscription Level (Basic, Pro, Premium)Total Requests (Sum of values from multiple fields)What I've tried to do so far seems to not be working very well and I am a complete novice when it comes to Airtable formulas. IF( AND({Subscription} = "price_1NZdQ5C8Jx0D8bOIPFa8Nlto", {Total Requests}<"8"), "Limit Not Reached", IF( AND( {Subscription} ="price_1NkT9EC8Jx0D8bOIMXi0g37m", {Total Requests}>="25" ), "Limit Not Reached 2", IF( {Subscription} ="price_1NkT9EC8Jx0D8bOIMXi0g37m" ), "Limit Not Reached 3", "Limit Reached" ) ) Ultimately what I'm trying to do is check the user's subscription level and the number of requests they've submitted and evaluate if they are at their limit or under their limit.I'm not sure if I'm fully grasping how the logic is supposed to flow and I

Hi, hoping this is easy...Can you help me adjust the formula below to return just the number of work days?formula:  IF( AND({Squad Start Date}, {Squad End Date}), IF( AND( {Squad Start Date} < DATETIME_PARSE("10/28/2023"), {Squad End Date} >= DATETIME_PARSE("10/01/2023") ), DATETIME_DIFF( DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({Squad End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/28/2023"), "X"))), "X"), DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT({Squad Start Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/01/2023"), "X"))), "X"), "days" ) + (MONTH({Squad End Date}) = 10), 0 ) )  Thank you so much! Ann

Hello, I am looking for a way to calculate a date minus x days based on a date field. I.e. the result should be 31.12.2023 (DD.MM.YYYY) minus 10 days = 21.12.2023. How can I do this? I tried to use this formula, but it returned an error:  DATETIME_FORMAT(DATEADD({Date Field}, -10, 'days'), 'DD.MM.YYYY') Thanks for help, M.

I’m probably missing something, but how do I get the current day of the year? For instance, we have WEEKNUM(TODAY()), but not DAYNUM(TODAY())? I can do year(today()), and week(today() but when I do day(today()) it shows the day of the month and not the year. Thanks! Example: Jan 1st 2021 is day 1, etc.

I already have a formulas that work fine although i need to add “( )” around my Base field called MODELEIF(AND({PRODUIT},{MODELE}),{PRODUIT} & " - " & {MODELE} & "\n",IF({PRODUIT}, " " & {PRODUIT} & "\n") &IF({MODELE}, " " & {MODELE} & "\n"))& Right now when I got the formulas it shows ANNEAU DE FOULARD - ALEXANDERI would like to add ", so it will show this.ANNEAU DE FOULARD - "ALEXANDER"   

Hello again,I am trying to puzzle out the best way to know if a member of an organization has lapsed in their membership dues and when they were due.I have a payment table with records of each payment by members with the date of the payments. I want to list members who have not paid in 12 months since last payment and, list them as lapsed in the renew column.Added problem. The table lists all payments over time. So each member could have many entries of payments in the table. I only want to test on the latest payment per member. Yikes!Anyone have any thoughts on this?Thanks in advance, Scott

Hi, we're looking for someone to help us to integrate Airtable with our WordPress website (particularly Ninjaforms), with a pdf generator and with Canvas by Instructure. This is us www.thelearningcircle.co.uk Please get in touch on info@thelearningcircle.co.uk

I have some numbers i need to include in a string.These numbers needs to be rounded down to two decimals, which I use ROUND() to do, my problem comes if the number has trailing zero's fx:233.50 becomes 233.5233.00 becomes 233Does anyone know how can keep the trailing zeros? 

I have this formula as 1 of 3 parts that's extracting certain bits of data from a reference cell into 3 adjacent cells. What I'm wondering is if I can add something to get a specific character insert BEFORE the info...Examplereference cell contains : flavor1, flavor2, flavor3 This part below pulls out "flavor1" into a 2nd cellI'd like it to do that but add a " / " before the flavorSo the result would be " / flavor1"IF( FIND(",", FlavorsLookup1_1),LEFT({FlavorsLookup1_1},FIND(",", {FlavorsLookup1_1})-1),{FlavorsLookup1_1})  Thoughts/suggestions are appreciated in advance.

Hello-I am trying to create a formula/automation/script that looks at a field of images (All PDF Files), and takes the field that is named in the file "RULES", and add this file only to another field called RULES.Any ideas?Thank you!