Formulas

I am trying to compare dates across 4 different columns. Here is the formula I need help with  - IF(DATETIME_DIFF(({Start (from Roadmap)}, {M's Start Date}) /This is trying to compare dates in two columns OR / This is to ensure the second set of date comparisons are also accounted for.  ((DATETIME_DIFF({End (from Roadmap)}, {M's End Date copy})))) /This is trying to compare the second set of dates in two columns If the first or second condition is true i.e the dates don't match I need it to return a value of say 1. Can someone help correct this for me ?

Hi there,Using the formulaDATETIME_FORMAT(SET_LOCALE({Clinic Date}, 'es'), 'dddd D MMMM YYYY')to translate dates into Spanish, however, the translations are missing "de" between date, month, year. I'm having trouble adjusting the formula so that date read. For example, the translation is working well and producing "jueves 30 marzo 2023" however, I need it to translate to say "jueves 30 de marzo de 2023" I've tried problem solving with formulas I found in AT trainings, but it's not working for me. How can I update the formula to include the word "de"?

I try the formula that IF({Date due}!=0,CREATED_TIME({Date due}),BLANK()), but the result is the created time of the row, not the date the {Date due} cell changes. What should I do?

Hello,I'm really new to Airtable and completely inexperienced with formula options.I'm looking to set up a formula so I can easily create a list of dates for the calendar year. Right now I used # option but I think a formula is more in line with what I need...Here is an example 

Hi there! I've been trying to replace  "," by "&" in a lookup field using Substitute and IF statements but I still receive an error or the result is not giving me what I want. So the result I want is IF First name contains  "," then replace by &..  

Hello,I'm completely new to Air Table and very unfamiliar with the available formulas.I want to have rows display dates for the whole of 2023. Yes, a long list of every day of the year. I'm including a picture as an example.I'm sure I can achieve this with a formula? Instead of manually...  

Hello. Looking for a formula that allows me to both convert to central time & round to nearest hour. What I have 2022-11-11 4:01pm 2022-11-11 2:01pm 2022-11-11 12:02pm 2022-11-11 10:15am Want 2022-11-11 4:00pm 2022-11-11 2:00pm 2022-11-11 12:00pm 2022-11-11 10:00am

This has probably been answered before, but I'm not sure how formulas work or how to maniuplate them to fit my use case.My base has multiple tables, one of which is a Members table. This includes the person's name as the primary field and other info, including their birthdate, in other fields.On another table, I have a list named Releases. Each Release record links to the Members table, and includes every Member who participated in a Release. Also, each Release has a release date field.As an example (Note that I'm only interested in linking to the H!P Members field, not the Non-H!P Members field):Let's use the record named Morning Coffee to hopefully explain things well enough.Morning Coffee was released 1998-01-28, and five H!P Members participated in the release: Nakazawa Yuuko, Ishiguro Aya, Iida Kaori, Abe Natsumi, Fukuda Asuka. I would like to have a field running a formula that displays each individual member's age in years on that date, preferably in a list. As I

I am using a Formula field as my Primary Field for a schedule table (each record is a time slot).Among my formula for naming each time slot, I would like to add a number (ie. 001, 002) which is generated based on the Date field.ie. Record X: March 5, 2022 = 001, Record Y: May 7, 2022 = 002, Record Z: February 9, 2024 = 003So, I need a formula that creates a number based on the record's Date field, in relation to the rest of the records.On top of that, I would like to have separate sequences of numbers based on another field. I don't want all records named in sequential order, I want all Screening (option in single select field named "Type") records named 001>, and all Volunteer Shift records named 001>, etc.ie. Screening A is named 'Screening 001' (although it may be the 10th record in the sorted-by-date table) and the volunteer shift for that screening might be named 'Volunteer Shift 005' (because there are 4 volunteer shifts scheduled before that one).(The 001 number format isn

I'm currently working on a project in AirTable where I need to include .PNG images in a mapping application. However, I realized that .CSV files can't store .PNGs, so I decided to pull the URLs of the .PNGs in my base and create a new field to store them. This seemed to work fine, but I soon discovered that the URLs for the .PNG images expire every 3 hours. So they were coming up as a thumbnail on my map, and then they expired and disappeared. To solve this issue, I'm looking for a script that can run an automation to automatically update the image addresses in each corresponding record by using the file with each record. I've tried searching for solutions on my own, but haven't had any luck so far.

I'm trying to create a field which lists a user's two heads of department (HoD) for each building.I have all the pieces I need in my current table, but am struggling to find the best way of looking up the data to return.I have the following fields for every user: Building (Single Select), Department (Single line text), HOD (formula returning true/false - there are two HoDs for each department) and would like to have a field called User's HoD that returns the two users who match Building, Department and HoD = true.Any suggestions would be really appreciated!

Hello everyone,We have a team of 10 persons that have a non-linear schedule (i.e., We are working between 1 and 5 days a week and those days varies from one week to the other).We have defined a set a todos (4tasks) for each day. Every week (Sunday) we are assigning people to one of the 4 tasks for the week to come.Based on that assignation, we want to automate daily slack send-out specifically to the person responsible of completing the task. The current way that we have a doing it is :- a google sheet in which we enter the name of the responsible (one week in advance) for a specific task and day- index(match()) formula to match the name and the slack ID on the gsheet-zapier to copy everything to airtable  -slack automation (with an if (date=today) then slack the ID)We are trying to cut the gsheet to airtable automation, but we would need an equivalent to index(macth()) formula in airtable Does any one know one? Have you ever solved a similar issue?Thanks

I am trying to create formulas that skips weekends, showing 5 days after a date field called Sendt (and then two other formula fields for 14 days later and 30 days later)I tried using the workday function, however it is displaying a Sunday, as you can see in the screen shot below.I have tried, I kid you not, over 30 different types of formulas and cannot get this to work. What am I doing wrong and how I can get the formula to spit out the next BUSINESS DAY 5 days after the Sendt date? For reference 3/27 is a Monday and 4/2 is a Sunday.

In the theatre world our "fiscal years" are actually seasons that start in the fall and end in the spring.  And specifically, there are two big "quarters" = a fall/spring season, and a summer season. More complex than just "quarter 1" plus "year." and I don't think I can use the MONTH function.I want to be able to use an if then statement that I can set once and forget it without having to create a new formula field when each new season rolls around.If the date range is between 09/01/(yyyy) - 05/31/(yyyy+1), it's displayed as the "(yyyy)/(yyyy+1) Season"Ex:  Engagement end date is 09/02/2022.  It would be displayed in the column as "2022/2023 season"If the date range is between 06/01/yyyy - 08/31/yyyy, it's "yyyy Season"Ex: Engagement end date is 0701/2023. It would be displayed in the column as "Summer 2023 season" Is this possible??M

I am trying to create a payment due date based on adding days (either 15 or 30) depending on what kind of vendor type is selected. Both 'if' parts of the formula work if I place them in separate formula fields but I would like this to all come through on the same column so I can see 'payment due date' in one place - anyone able to help?IF({Vendor Type (from Vendor) 2}=“Independent Contractor”,  DATEADD(Created,3,‘days’),IF(  {Vendor Type (from Vendor) 2}=“Included in Payroll/TEAMs”,  DATEADD(Created,5,‘days’))

Hi, I'm trying to create a formula that will direct to a Gantt view filtered by a ProjectID field (So I can create a "Open Gantt Chart" button in an Interface using the "go to url in record" action that will open a Gantt view filtered by a specific project.) I can create a formula that generates a filtered link to a Shared View (https://airtable.com/shrXXXXX?filterEquals_ProjectRecID=XXXXXXX)  but this Shared View isn't editable, which defeats the point. I have tried a formula for the non-shared view URL (https://airtable.com/appXXX/tblXXXXX/viwXXXXX?filterEquals_ProjectRecID=XXXXX ) but that only opens the un-filtered view. Are there any tweaks to the URL formula that would make this work? Thanks! [Slightly off topic rant - I'm working on creating a semi-complex workflow Interface, and I'm getting increasingly frustrated. It seems like for every single feature I want to implement, there is a limitation in Airtable that I have to create seve

Hello! I'm creating an Airtable form, and I want to enable a default text to fill in a field if the field is left empty by whoever is filling the form. Example if name is not filled in, then it will default to anonymous instead of being blank. How do I go about doing this? I tried to use the blank formula myself but I failed :')Thank you so much!

Hi all, I would like to extract an ID of a link with Regex formula but I don’t find how this formula works. Example: Link Drive (false link) https://drive.google.com/drive/folders/E1x1a1m1p1le1 And I would like to extract this part: E1x1a1m1p1le1 (i.e. part after “folders/”) I have tried this formula: IF({Link}, REGEX_EXTRACT({Link}, “folders/[^\s]+”)) but I have the following result : “folders/E1x1a1m1p1le1” instead “E1x1a1m1p1le1” I guess it should only be a small adjustment. Could you help me? Thank you in advance, Sébastien

Hi Folks, I'm currently struggling with a formula. I'm trying to track the progress of a project that requires an asset to be translated to several languages. I created checkboxes for each language and for when those assets were QC'd. I'd love to be able to consolidate all the checkboxes to a single text field so I can easily see which languages have been completed and QC'd. I've been able to create a formula to pull a checkbox per language but now I'm adding the second step of QC and my IF AND formula isn't working. Below is a sample of the formula that worked and the one that isn't once I added the second step. TRIM(IF(({BR Art}),"BR, ")&IF(({DE Art}),"DE, ")&IF(({FR Art}),"FR, ")&IF(({IT Art}),"IT, ")&IF(({JP Art}),"JP, ")&IF(({MX Art}),"MX ")) The two step I was testing outIF(AND({BR Art},{BR Art QC}),"BR, ",IF(AND({MX Art},{MX Art QC}),"MX, ",IF(AND({IT Art},({IT Art QC}),"IT",BLANK()))) 

Hi all,For about a month, I have noted that when I create a new table and the Primary field , which I have as a formula set to pull it from from the "Name" field, doesn't automatically self populate. So if i create 1-2-3-4-5 new tables, with individual's names, when I look at the new entities in grid view I can peek at the data but the first primary field to the left is blank until I refresh the URL. Then it shows the details. We have a Base with about 4500 tables.  Not sure if that changes anything or if this is a result of the size of our base.  

How can i create a field that reads, for example, "Friday March 17, 2023" ?

Hello All,I am trying to figure out how to perform a calculation if possible.I have jobs that are sorted by date. Currently, data is grouped by date. I want to take the flat rate for example $500 and divide that by the number of jobs. So that I can have a column that tells me how much each job was worth at the flat rate. So if there were 14 jobs and the flat rate is $500 each job for that day will get its portion of the 500. The number of jobs per day can vary. For example, on some days, I will only have 6 jobs but the same flat rate. In addition, I would like to know how many jobs are urban and how many long distances. I already have the data using a lookup by zip code. Just need to know the percentage of revenue that is urban and long-distance. Hope this makes sense. Been trying different formulas but cannot figure how how to get the count working. I appreciate any help or advise you may have. Thank you so much

I imported a financial report from PayPal. All of the Negative amounts in the "Amount" field use a "-" in front of the value. I want to create a new field that removes the "-" and just lists the value of the Amount field.  If the amount doesn't have the "-" symbol, I just want it to leave the record blank.  I tried using a substitute formula in the new field, but not sure how to get a blank value in the new field if the original amount value does not have a "-" sign. SUBSTITUTE(Amount, "-", " ") What would be the best way to do this? Thanks in advance.

I'm trying to build a formula that returns "true" every 180 days (and subsequent 180 days) after a record was created.  I'm heading down a very complicated route involving dividing  {Days Since Created}/180 and checking the length of the return result.I'd like to be able to build a "divides exactly" check into this:DATETIME_DIFF(NOW(),{Date Created},'days')Any ideas would be gratefully received.

HII have  "Creation Time" column for my records. The records are created when user submits the form.Everyday at 9 am or 10 am in  morning meeting, i need to show all records created between  " Yesterday 8am to Today 7:59am".Is there a formula to achieve this?I am not  good at formulas to if any one create one for me please.Regards