Formulas

Hi there and Happy Friday!I am trying to calculate the time it takes an individual to get a specific type of number at the time they enroll in our program.The formula I have is essentially, "If the individual does not have a preexisting number, calculate the time difference between the date they got the number and the date they enrolled in the program." The formula is: IF({LO has FTN at time of Enrollment}="No",WORKDAY_DIFF({Enrollment Date},{Turned in FTN})) That works but if “Turned in FTN” is blank, the calculation results in "#ERROR" and I hate "#ERROR". So, that means I need to add something like, “If “Turned in FTN” = Blank, then “””. I tried adding that a few different ways but am still getting #ERROR. Any ideas how to get rid of the #ERROR? Thanks in advance!  

Ok, I have been trying to do this for a while on my own with no luck.I have a list of serial numbers. These serial numbers have more than one entry. I group the view by serial number so I can see all entries for any particular serial number. What I would like to do is figure out a way that I can see only the last entry for every serial number. My thought is to have a third field for a conditional formula. Something that just posts the number 1 if it is the most recent date. Then I can use a view to only see everything with the number 1 in that field. The problem is that I need to pull the most recent date for EVRY serial number group. Not just the most recent date over all. Hope that I am explaining this right. Here is an example of the a sample base: Please help! Thank you.

Hi guys!I'm using Ubiqod for attendance tracking in our company. When you scan code, you click on the correct button (arrival, break, going home). After you submit it, the data comes to google sheet and with automation it gets to the Airtable. Now the problem occured. The timestamp from the google sheets it's not the same in the airtable. My guess is that this has got to do something with the different timezones. In googlesheet it's correct timestamp, in airtable it's 2 hours later.This is timestamp from google sheet:This is timestamp from AT:How can I transform this date in AT so that the hour will be correct (it was supposed to be "14:07" not 16:07). Thanks! 

Hello!I have two fields that I want to meet certain criteria to return a value in another field.Return Value "Late" if field 1: Advanced Record contains "ADVANCED" and Field 2: If Date/Timestamp is after 10p EST otherwise return "On Time" if Date field is empty have this field stay empty Thanks!

Hey I am trying to set up a formula for the below problem. I am provided with a list of names in the 'home' column and certain entries I need to change to end up like the 'bot name'. I have tried switch/If functions and I just get errors.  

Hi!I'm using an airtable base to calculate my working hours as a freelancer who works in live entertainment and I'm trying to figure out the best way to have a flexible system for different employers' rules for minimum paid hours of a shift, after how many hours before OT and DT, and also how some allow for DT after midnight. There is also the possibility with some for Sundays to be considered "premium days" regular time=time and a half all day. Right now there is a tick box per "shift" record that allows me to say this shift is "premium".Right now I have a table of "billing rules" with lookup values for each parameter.Each shift is linked to a "billing rule" record, and these values are lookups. The lookup values are then used to determine how many hours fall under each.

DG mw me nahi hai na to be a great day hoga to be a

im accautn is Block support of the day is the best way to get

I would like to create a script that checks if my employee has already taken an advance on the same day. If it has already been taken, the new record should be deleted. I do this in the form. How can I get the current record ID from the form and delete it or add it if it was the first time. let table = base.getTable("Zaliczki"); // Zamień "Nazwa_Tabeli" na nazwę swojej tabeli// Pobierz dzisiejszą datę w formacie ISOlet today = new Date().toISOString().split('T')[0];&nb

Hi all,I have a base table with the transaction history which lists each profile's contribution amount, date, and the fund invested. Each profile is allowed to invest in many funds and can also invest in the same fund multiple times. How do I create a view that would sum the investment total and sort by funds at the same time?Here is the transaction history table (base):I created a "List" from Airtable with Level 1 as Funds and Level 2 as Profiles, but I was not able to sum the values from the same profile:As you can see below, there were multiple transactions from the profile that starts with "2...." within the same fund

Hi,i am using python-requests and trying to use f-string with filterByFormula here is a simple implementation:hard coding the name worked:   url=f"https://api.airtable.com/v0/{base_id}/{table_name}/" headers={'Authorization': f'Bearer {api_key}', 'Content-Type': 'application/json'} query={"filterByFormula":"({full_name}="some name")"} response=requests.get(url, headers=headers, params=query) data=response.json()    but when i tried to use f-string it didn't work. here is a a piece of code where the problem occurred:   url=f"https://api.airtable.com/v0/{base_id}/{table_name}/" headers={'Authorization': f'Bearer {api_key}', 'Content-Type': 'application/json'} employee_name="some name" query={"filterByFormula":f"{{full_name}}={employee_name}"} response=requests.get(url, headers=heade

My text fields imported from excel have a question mark where an apostrophe should be.  Is there a simple formula to correct this?

Hi everyone - I have a Airtable based that hosts events across states. And another base that hosts emails and location of users. I'm trying to create an automation that send emails weekly event reminders to users based on the event location that matches the users chosen locationsIn the email list base - a user can input multiple location based on their preferences. In the event based - events are listed based on state. I think I should be doing the automation in the email list and link that to the event base. I hope to send a list or grid of event to X user name and email. I was able to setup the first half of the automation using find records but not sure how I can automatically send the emails with limited manual work. could someone please help me out? Happy to share my base to assist 

I have a URL field that I need to grab specific text from (in bold). Does anyone know if there is a formula I could use to easily grab just the string in bold without pulling in additional data from the 3 possible formats?Here are the multiple formats that the URL could be in:Sample format 1:https://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9ASample format 2: includes ?ABC=1 at the end https://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9A?ABC=1 Sample format 3: includes 2 URLs only separated by a spacehttps://www.website.com/en/others/A1087A50-AB7

Hello,I am working on a base that includes artists' names and a lot of them have diacritics in their first and last name. Is there a formula that I can use that could identify if there are diacritics used? It can be a simple True or False formula that I can then use a filter to isolate everyone with diacritics in their name. For First and Last name I am used simple Single line columns.A list of names is important because it is used for a remapping feature on our website, so the end result for airtable needs to be a filtered view of names. I appreciate any help you can provide with this. 

Is there a script/formula I can use to extract zip files as separate files and have them saved in another field?

Hello everyone,Me and my team are working on a database for my business, to be frank we're new to Airtable, and there's something we can't quite figure out. There's this one table that is for recapping all of our clients' consultation sessions. After each client consultation sessions, that session will get added to this table as a separate entry, and be given a specific Consultation ID. The goal is to make something like this:Consultation ID: EVA/COU/2024.06/000004-001The things that matter here are the field "Unique ID" which is 000004, and the three digit after the dash, which in the example is 001, which should signify the amount of sessions they have with us, 001 here meaning it was the client's first ever session.The field "Unique ID" is tied to the field "Participants" which is linked to a different table filled with details of our clients. Each Par

Hello!I am trying to create a formula that categorises ships by type, then size category, and if they meet both of those criteria they are assigned a value that is annual fuel consumption appropriate for the vessel's size category. If not, the IF test continues through all size categories and finally ends on "NA" if nothing it true. I would have thought I would be able to nest like the below example, but for some reason it only generates the correct answer for the first two size categories, then everything higher in size gets the same second highest fuel value:IF(AND({VESSEL TYPE} = "SHIP TYPE1" , {SIZE CATEGORY}<=1000),"XX Fuel consumption value",IF(AND({VESSEL TYPE} = "SHIP TYPE1" , {SIZE CATEGORY}>=1000<=1999),"XX Fuel consumption value",IF(AND(

I am setting up a new system for our company to track our projects and record how many days each designer has worked on a project and, equally, how many days a project has been worked on. The calendar view doesn't work for us, as date ranges don't work here. We also don't want to have to fill in time sheets anymore, we just want the table to calculate the number of times a record (project) appears in the design schedule. The rollup function isn't working here to add up each instance of a record.Can you help me figure out how to set this up so that the "Projects" table, and the "Design Schedule" table link to count up how many times a project appears across the 5 days. Ideally it would be great to figure out how to show how long each person has spent on each project but ultimately the goal is to work out how much time has been spent on each project total. I don't know much about functions and their language so I assume this is wh

Hi Folks,I am essentially trying to automate quota calculations for the sales team in Airtable. As I explore setting up the tables, I'm running into constant brainfogs.1. Im dividing the "record" (quarterly target) by "3" (to break this into a monthly target) but i'm receiving a #ERROR. Can anyone help me fix this?2. The way I set up these records is to have the employee ID + Month when the plan start date is; Therefore, each employee has 12 records. How can I build a reference table so I can link the records and specifically link the values into the current table? I currently calculate the quotas in Gsheet to then copy and paste in the current process but I'd like to automate this. Additionally, if I need to link the record to each of the 12, how can I automate that? Seems like automation setup could address this issue.I thou

I'm experiencing something weird - I have a formula field ("Cheque 2 amount") set up to subtract different currency fields. All fields, including Cheque 2 amount are set to currency format with 2 decimal places.  Everything looks normal in Airtable:However, I have a "send email" automation set up that uses the Cheque 2 Amount as a token, and some of those emails are displaying this token with 14 or so extra decimal places (here is the email generated from that same record above): Seems to be happening to maybe a quarter of the 160 or so records I was working with this morning. I can't see anything different about

Hello everyoneI'm NawafI have a long nested if statement that i used to use in excel but I can't figure out how to use it in airtable the idea is i have 3 columns first ( permit status ) second is ( remark ) when the conditions meet, I want the third column ( action ) to show as "action" or "no action" or "no action closed"(permit states) has only two terms closed or valid(remark) is different i want to find the word "renewed" or "to be" or "re-issue" or "on hold" "redesign"this is what i used to use in excel =IF(AND([@[Permit Status]]="Valid",[@Remark]="No need for renewal"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="No need for renewal"),"no action closed",IF(AND([@[Permit Status]]="closed",[@Remark]=""),"need action",IF(AND([@[Permit Status]]="Valid",[@Remark]=""),"no action",IF(AND([@[Permit Status]]="Vali

I found this formula in the community. However I want to include a timestamp aswell. Since {date 1}, {date 2}, {date 3} may occur on the same day. I tried adding a format specifier but it wont work... The formula sets the time to a default 12:00am instead of the actual time that is showing on the datefields.IF( OR({Date 1}, {Date 2}, {Date 3}), DATETIME_PARSE( MAX( IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD'))) ), 'YYYYMMDD' ) ) 

Probably no way of doing this without an Automation right?I'd just like to have an image for my records based on a single select field. Essentially a Single Select field for images! I want to be able to assign a Logo for each record. Then on an interface, we can see Red Team or Blue Team logos distictively.My formula solution for this would be to host the image URLs somewhere and use a SWITCH() formula to find the URL. I don't know how to get it from the URL to the Attachment field.I can do this through automations but that seems over-engineered. Since I already have the teams in a single select field, all the data is there and I don't need to overcomplicate my base. The want for a logo is purely cosmetic haha.

I receive a due date and have to calculate backward to identify different milestones in our projects. What formula(s) do I need to use to make this work? I've attached a picture of what I'm trying to do.  Thanks in advance for the help!