Formulas

I am trying to sort by the Week # of the year that an entry is assigned on my base. Its sorted by year, then by week - so 2024, week 1 through 52, etc.However, for anything this week up until the 31st, its showing up as the 1st week of Dec 2024 (which confuses me as you would think it'd be 1st week of 2025?). I would like that to show up as the last week of Dec 2024 (week 52) or even Week 1 2025.Formula is:WEEKNUM((Assigned), "Monday") It is pulling data from the "Assigned" column, which is listed in the format: 12/30/24 11:00amThanks!

Hi again,Is there a way for you to have data from a field "written" to that field and fixed for those changes elsewhere will not affect the result in this field in the future?An example of this might be;A fee in one table is designed to be updated in future years, and that field is referred to in other locations. Once that booking has been confirmed and paid, I want to be able to "fix that price" for that booking so that if the lookup value in the first table changes in future, that the confirmed bookings either do not change or, that a field has been updated which then remains at the agreed price.I hope that makes sense? Thanks S

Hello everyone!I need help with a formula that computes two fields as {field 1}/{field 2}.However, the base is new for my team, and we're working to input backdated data, so some records that use these fields are 0 (zero), at the moment. The formula field that computes the two fields is used on current data, and we'd love to eliminate the infinity returns so as not to interrupt the SUMs we use in other areas of our work.I've played around with several iterations of the formula and solved one to lose another 🤪 The current formula I'm using to keep us sane in the interim is:IF({KGM 500 Total Attendance}>0,{KGM 500 Total Attendance}/{KGM 500 Total Capacity}) Attached is a screen shot of the fields in the base for reference. As we work to gather missing data, Total Attendance may be inputed first or second to Total Capacity. When the missing data is entered, both fields will have a number greater than 0, I want the Formula Field to compute {KGM 500 Total Attendance}/{KGM

HelloI would like to build a base where I have tables with planned income, planned cost and summary table that reflects incomes and costs on monthly basis.My question is, if it is possible to take bank balance as a basis for the calculation and calculate Bank balance +income - cost for each record so that the basis for each calculation would be the result of previous row? For clarity, here is how I would like it to work:I have seen some similar questions here as well, but I am not sure if they would work for this use case as well.Thank you in advance.  

Hello!I am wanting to add a DATEADD formula to my base to set deadlines for different tasks based on a hiring date. I've done this in a previous base, but have since lost access. Can someone help me with the formula I need? For reference, some of the formulas I need help with are:Task: Email for photo, new hire survey, and short bio of new hire (-7 days before Hire Date)Task: Assign desk space for new hire (-3 days before Hire Date)Task: Give Uniform (on Hire Date)Task: Collect how to contact employee while OOO (1 day after Hire Date) This is my current formula:IF({Task} = "Email for photo, new hire survey, and short bio of new hire", DATEADD({Hire Date}, -7, 'DAYS'))When I add another {task} = ..... , it changes the format of the first deadline from a date to T00:000:00.OOOZ and then the next task's deadline just shows up as "true." When I reopen the formula, it looks like it changes itself from IF({Task} = "Email for photo, new h

When I do a rollup to get the unique documents, it is correctly retrieving just the unique values - but while a lookup brings up the actual attachment documents, the rollup displays Airtable's Internal URL for the document.How do I then retrieve the document?   

I have a few currency fields that I am adding up in a simple formula field. I am then trying to display the output of that formula field within a text string I am building with another formula. However, I’m getting some strange decimals when showing the numbers.In an effort to troubleshoot this, I create a new blank Airtable base for testing and I’m getting the same result. For no apparent reason (to me), when I reference the total field in the string, it will sometimes add a ton of decimal spaces, like this: Here’s a link to my sample/testing base that is showing this issue: https://airtable.com/appqBYuTjL8QbhmF4/shrM4rk6GDRaJQ4QNMy formulas are pretty simple:Total of Amounts 1 & 2: {Amount 1} + {Amount 2}Display Total in a String: "The total of both amounts is $" & {Total of Amounts 1 & 2} & "." I can’t figure out what might be causing this, especially when I manipulate the Amount 1 and Amount 2 values to reach the same sum, the decimals somet

Trying to figure out how to do a search to see if a number falls within 2 fields.For example a field with Start year and a field with End year.  Lets say on one record my start year was 1978 and my end year was 1986.  I would like to do a search for 1983 and have it retrieve that record.  Can someone point me in the correct direction??

Hi, to be honest I’m not sure if it actually is a bug, but this function does not follow the ISO standard of numbering weeks. For example, today (2016-10-13) should be week 41, but the function returns 42. Probably because the first week of this year was week 53 according to the international standards.

Hello!I was recently helped on another post to create parameters for the dateadd function. However, it is not calculating dates correctly. Why? How do I fix this?Here is my function:SWITCH(  Task,  'Email for photo, new hire survey, and short bio of new hire', DATEADD({Hire Date}, -7, 'days'),  'Assign desk space for new hire', DATEADD({Hire Date}, -3, 'days'),  'Give Uniform', {Hire Date},  'Collect how to contact employee while OOO', DATEADD({Hire Date}, 1, 'days')) The complete by dates should be: 12/24, 12/31, 12/28, and 1/1 

Hi,I have a base where I keep my business's time entries for each of the employees.  However, they may be in a different state based on the day, so I'm looking for a way to populate a field for each time entry showing their location.The time entries have a date, a person who performed it (currently as a linked record), and the the number of hours.I currently have a sheet with a separate record for each date and single select field for each employee showing the state that they were in.  Note I can change the organization of this table if needed (for example to entries with a date, a single employee and a location).  Any thoughts on how to best set this up?  I currently have lookup fields for each individual employee in the time entries table, looking up their location on the given date, and then a formula field that uses a switch that selects which lookup field based on the user who performed the time entry.  However, it requires a new lookup field and an u

I need to calculate different due dates for tasks based on Workday instead of dateadd. Here is my original formula: SWITCH(  Task,  'Email for photo, new hire survey, and short bio of new hire', DATEADD({Hire Date}, -7, 'days'),  'Assign desk space for new hire', DATEADD({Hire Date}, -3, 'days'),  'Give Uniform', {Hire Date},  'Collect how to contact employee while OOO', DATEADD({Hire Date}, 1, 'days'),  'Verify that background check has been completed', DATEADD({Hire Date}, -7, 'days'), 'Hiring Manager select starting date', DATEADD({Hire Date}, -21, 'days')) When i change DATEADD to WORKDAY it gives me an error message. What else do I need

I want to filter to the infinity values but am unsure how to do this. I don't want to remove them using an IF statement or the other tricks you can use to have them become zero or empty values. The first screenshot shows the data and that there is one value with Infinity. When I try to use the filter condition to equal the infinity value I am unable to enter the word infinity because it only allows numbers. Next I tried to filter to a value greater then the highest value other then infinity and this filters out everything including the infinity value. How can you filter to the infinity values?    

Hi,I want to create a formula to create a name for a field that consists of "FIELDA" or "FIELDB" If "FIELDA" has no value i would like to show "FIELDB" in the name instead. If "FIELDA" and "FIELDB" both have a value, I would only like to have "FIELDA" filled in the name of that field. If both have no value, no value should be shown. Thanks for helping out! Mark 

I'm stuck. I need a field type and formula to display the cumulative annual profit or loss (P/L) displayed as a percentage (%) and based on a Q1 starting amount and subsequent amounts entered in Q2, Q3, & Q4 fields.This seemed easy until I got started. Now I'm digging a mental hole I can't climb out of :-). Help... thank you.

I have a [Anniversary] field and a field that calculates the next anniversary, so I get reminders and write a card for my volunteers. Here is my problem:The basic formula works fine when there is an anniversary in the field. When there isn't one, I get the #ERROR, which one would expect. I tried ISERROR(...) and IF(ISERROR...) to get it to show blank, but I can't get it to work. All the fields go blank, even if there is a date.Here is the working formula in the AV_Next field:DATETIME_PARSE(DAY(Anniversary) & "-" & MONTH(Anniversary) & "-" &(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),"D-M-YYYY") When I tried this, it worked for showing blanks, but then the date formatting ignored all normalcy. I get this: 2025-07-20T00:00:00.000Z IF(ISERROR(DATETIME_PARSE(DAY(Anniversary) & "-" & MONTH(Anniversary) & "-" &(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(T

Hi,I've created a formula to calculate my patients' current age based on their date of birth (Lookup field). When I look at the record in grid view, it gives me an error message, but when I check the formula for accuracy, the calculation preview gives me the correct calculation. I'm not sure why there is an error in the record in grid view if the formula is accurate when I check and edit it. HIPAA-protected info has been redacted in the screenshot below. DATETIME_DIFF(TODAY(), {Child's Date of Birth (from Lookup) 2}, 'years') & ' years, ' & MOD(DATETIME_DIFF(TODAY(), DOB, 'months'), 12) & ' months' Thanks so much!

Hello dear community,I have already searched but found nothing. I want to use a value from one row (for example, row 1) in a formula in row 2. In row 2, the value from row 1 should be used minus the value from row 2 (same field). How does that work?Automation is not the right way, as the dataset is not newly created (trigger), but already exists. Specifically, it is about the following:I want to use the value "km" from the field "refueled at km" in row 1 and calculate it in row 2 in the field "kilometers driven". This value is calculated from the field "refueled at km" in row 1 minus "refueled at km" in row 2. Can someone help me? Thanks in advance.

Hello,Our team has reduced available hours in the summer, and I'm trying to figure how to represent this when using the Utilization summary in Timeline view so that their Available Hours are reduced during a set start and end date. Thank you!

Hello--We're developing an Affiliate program where the affiliates offer a free month on our app. We're gathering new user data from Typeform forms that are automatically dropping in the information into different Airtable sheets as shown below.I'd like for all of the new users to then be put into another table that I can sort by start date, but I'm not sure how to do that.  Thanks in advance!

Hi everyone, I’m facing an issue with counting linked records in Airtable, and I’d love your help! I’ve created two tables for managing group interviews:1. “Entrevues Dispo” (Interview Slots) – Tracks the availability of each interview slot.2. “Entrevues Inscriptions” (Interview Registrations) – Stores candidate registrations linked to the available slots. In the Entrevues Dispo table, I have a linked field called “Entrevues confirmées” (Confirmed Interviews), which pulls all the candidates linked to a specific interview slot. The relationship works, and I can see the correct linked records. The Problem: I’m trying to calculate the number of candidates per slot using COUNTA({Entrevues confirmées}). However, it only counts 1 record, even though there are multiple linked records (e.g., 2 or 3 candidates). I also tested with a Rollup field using ARRAYJOIN() and confirmed that all linked records appear correctly (e.g., emails of all candidates). Despite this,

I am new to the free version of Airtable which I am using to keep track of my craft stamps. I have at the moment around 500 items which have sentiments listed. When I try to find a particular sentiment I search for eg has any of, but it gives me "enter comparison value" and all the sentiments I have created. What I want is just a search box so I can just type just one word eg happy, so all those sentiments are found. What have I done wrong please and how can I rectify it.

Hello, I am trying to get this to work but all I get so far is a massive headache haha 🙂Context : organizing automatic reminders to clients we're meeting at a trade show. On the day of the meeting, I want to send a reminder to the invitee at 8:00 local time (and of course this local time changes depending on where the trade show is) to tell them they have a meeting with us that day.I have two tables.> One for trade shows with Trade Show Name (linked to the other table) and various fields, one being a number field providing the hours difference as well as a time zone field 'Europe/Paris' for instance, this being the time zone where the trade show takes place. > One for meetings with plenty of fields, including one that comes from our booking tool (Calendly) and where I've set up a webhook and the meeting time always comes as UTC. I have there also a lookup field repatriating the hours difference as well as the timezone field.How do I create a field that will set the t

I have a base with two tables: Employees and Projects.Employees has a primary field of Employee ID and Project IDs.Projects has a primary field of Project ID, Project Manager(s), and Status.In the Employees table I want the field "Project IDs" to be automatically populated with the corresponding Project ID from the Projects table when the Employee ID matches any names in the Project Manager(s) and when Status is not "Completed".Sounds like it should be simple. But I can't seem to figure it out. Thanks for any assistance! 

I created a form for our rental car contract in Fillout. Everything is populating correctly into airtable except the time. My issue is that I put the "date - time out" in the form eg 12/19 8:00pm. It will populate to airtable as 12/20 3:00am if I have the airtable time set to UTC. if I change the time in airtable to Denver (which is correct) it populates properly from fillout to airtable. HOWEVER, when I open the PDF everything in changed back to being a day in the future at 3:00am. I think it will work properly if I create seperate fields for date and time, which I can do, but it seems like it should be able to work by doing it the way I am. I'm sure this is a timezone code I cant for the life of me figure out. Please help, Thanks!