Formulas

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 

Hiya, Basically want to know if there is a formula where if the tickbox "marketing brief" column is filled in it will automatically then remove it will remove the marketing brief option from the multi-select "outstanding" column? I've been looking on the forum and can't quite find anything similar. Thanks!  

Hello everyone, I'd like to make a formula with an IF statement that shows "OK" if a date is in the last 6 complete months. For example, if today is the 17th of March, it would show "OK" for every record since the 1st of September.Is this something possible?Thanks 🙂

Is there any reason for -0 in the result of the simple formula as Paid-Shipped? Is it interesting, as it shows only in one row.  

Hi! I need help creating a formula and not sure if it is possible. I have a table that has several tasks with a single select option of completed, missing, or n/a. I want to get the average score of the tasks that are marked as completed. Example: Task 1 is completed, task 2 is missing, task 3 is completed, and task 4 is completed. The average would be 75%. Or if Task 1 is completed, task 2 is missing, task 3 is missing, and task 4 is N/A. The average would be 25% and so on.  

Hi I'm a Plus plan subscriber so starting to explore AT more in depth. I have a built a base with a JOBS table that lists jobs by job number, and using various currency, numerical and formula fields, delivers a profit per hour figure for each job. It does this by dividing the total job profit by the number of hours spent on the job. A second table called TIME LOGGING is updated with forms submitted by staff and tags their time to a job. These individual time logs are rolled up in the JOBS table to deliver the profit per hour figure. The next part is where I need help. How can I now deliver a PROFIT PER HOUR PER STAFF MEMBER figure which takes into account that time they have spent on each job? On some jobs, several members of staff log time, so the figure must be proportional to the time they spent on the job. E.g. If the staff member logged time to two jobs, one of which was just them, and one of which was part of a team, this would calculate as follows: Job 1: Profit per hour is $100

I am trying to build a base for a company that delivers natural food for pets. The clients tells us the date they want their first delivery made, how often they want to receive it (every week, every 2 weeks, etc), the preferred day of the week they want to receive the deliveries (monday, tuesday, etc) and also the number of deliveries that are already paid for. Based on that I would like the base to automatically fill the future delivery dates for each client record considering the informations we have. This will be importante so that I can set up a calendar view later that will allow me to know all the clients I have to make deliveries on any specific date. Thanks in advance for any help and I am sorry if this is not the right place to ask.