Formulas

Hey folks, I’m trying to calculate the number of days someone has worked in a job. I’m familiar with the DATETIME_DIFF() function, which works great when you have a Start Date and an End Date, but what if you don’t have an end date? In other words, imagine that someone is currently in their position, therefore the field registering their end date is empty. The theoretical solution I came up with is creating another field with the formula TODAY(), and then, if the field for end date is empty, use the field for today to calculate the difference between today and the start date, and calculate the time that the person has worked. I’m having trouble with the syntax! Any thoughts be greatly appreciated.

Hi all - need help! I have a Multiselection of “profiles” linked to a store. I then have items which may relate to one of those profiles. I need a formula to look at the “item profile” and see if it exists within the “store profile” multiselection - returning a simple true or false will work. I am hoping the below helps explain further

Hi there! I’m trying to do a fairly simple formula but it has quite a lot of conditions. I’m a beginner and not sure where I’m going wrong, could someone help? The board is a basic project overview. I’m trying to set a column to display “on track” or “overdue” depending on what the due date and status of the item is. My current formula is written like so: IF( AND(IS_BEFORE(Due,TODAY(), IF( OR( Status = “In Progress”, Status = “With Client”, Status = “Not yet started”, ), “Overdue!”, “On track” ))) Any help would be greatly appreciated! Meri

So I’ve gathered that I can’t use both AND and OR filters, but the workaround is to use formulas. Problem is, I can’t find a formula to solve my problem. I currently have two columns that I want to be able to filter, a “status” column and a “release date” column. This is the filter I want to set up: WHERE “release date” is on or after 14 days ago OR “release date” is empty AND “status” is not archived. Is this possible using formulas? Or are there too many layers? Thanks in advance for everyone’s help!

Hi there! There are a few formulas out here for similar-ish issues but I can’t find on that matches exactly what I need. I’m trying to create a formula that shows “Done” if the date is in the past, “Upcoming” if in the future or “In progress” if it’s on the day. The first two are working fine, but I can’t get it to show “In Progress”. It’s driving me insane as I can’t figure out why :frowning: IF( IS_AFTER({Live Date}, TODAY()), "✅Done✅", IF( {Live Date} = BLANK(), "🛑DATE NEEDED🛑", IF( IS_BEFORE({Live Date}, TODAY() ), "⏰Upcoming⏰", "🏃‍♂️In progress🏃‍♂️" ) ) )

Sorry I dont understand formula. Iam using 2 row for my stock, first row is (Stock-DO), Second is IF({Stock}<0,‘ :x: ’,IF({Stok}=0,‘ :heavy_minus_sign: ’,{Stock})) I DO belive there is a better solution. So how to express Switch((Stock-DO),Stock<0,‘ :x: ’,Stock=0,‘ :heavy_minus_sign: ’,Stock>0,‘Stock’)

Hi everyone! I am a beginner and was trying to modify other formulas I found on here but couldn’t get them to work. Would someone be able to help me nail down a formula that would check my due date field and then set a single select Urgency field depending on the due date for the following: -Not Urgent (due dates that are farther than 1 month away from today’s date) -Upcoming Due Date (for due dates that are 1 month out or less) -Past Due (for due dates that are before today’s date) -TBD (for any due date fields left blank) Thanks!!!

This is probably really simple - can someone please help with a formula… What I am trying to achieve: remove space after comma in a string Example of existing string: Kim Jones, Ted Jones, Fred Jones, Sam Jones What I am after is a formula to convert the above to: Kim Jones,Ted Jones,Fred Jones,Sam Jones I have up to 11 names in my string - any advice is greatly appreciated.

Hello, I’m trying to create a formula that returns a workday date after 4 days are added. How do I adjust the field that is feeding the data, and the formula itself to always return a date that is on a weekday? It returns an “#ERROR” every time I try one of the formulas below. DATEADD({Process Vetting Start Date},4,‘days’)) DATEADD(WORKDAY({Process Vetting Start Date},4,‘days’)) WORKDAY(DATEADD({Process Vetting Start Date},4,‘days’)) Thanks in advance

Hi everyone, We have updated our service pricing in October and I’m trying to update the calculation for services rendered after 10/1. In the below code {Direct Time} is {Direct TIme} + {Indirect Time}. Is it possible to use calculations like this within a nested IF statement? (IF AND( {FY}=“22”, {MM}>=“10”, ({Total Time} * {Rate by FY by MM})+{Pass through Materials}, ({Direct Time} * {Rate by FY by MM})+{Pass through Materials}, ) ) Best, Ben

Hi. I would appreciate help with the following: I have the fields Country (required field), Region and Title. I need to concatenate these fields in different ways depending on which fields are filled. -If just Region is filled then Region, Country -If just Title is filled then Country- ‘Title’ -If both Region and Title are filled then Region, Country- ‘Title’ -If neither are filled then Country Thank you in advance for any help. Katy

I didn’t get how to reach this result 2021-06-15T15:30:00.000Z. Do I need to use the datetime parse or format? Could you suggest the formula please? My starting point is a date field (ex: 02/10/2021 5:08 pm) Best

Hi everyone, I have one “Status” column with different status; I want for each status to be linked to another status, slightly different in another column called “Client Status”. To do so, I wanted to use a Switch formula: for a status A, there will be a status A’ linked, as shown in image attached. I wrote this formula: SWITCH({Statut} ,“ :mailbox: Contact entrant - à traiter”,“ :white_check_mark: En attente de validation”,“ :arrows_counterclockwise: Offre en cours de traitement”,“ :1234: Offre validée → n° d’acquisition à créer (CAP18)”,“ :truck: Collecte à effectuer”,“ :date: Offre validée → collecte à planifier”,“ :truck: Collecte à effectuer”,“ :truck: Collecte planifiée → à effectuer”,“ :truck: Collecte à effectuer”,“ :computer: Collecte effectuée → prestations à faire”,“ :computer: Prestations en cours de réalisation”,“ :email: Pres

Hello, is it possible to track a Winning streak? And do you know how? For example Cell Win increments +1 –> Cell Series should also increment +1 -> If Cell Lose increments +1 the Cell Series should be reset to 0. How can I safe the highest value of the cell (best Winning streak)? Do you have a better logical process? And is it posible with airtable? Thanks and best regards!

I’m having trouble getting DATETIME_FORMAT to work, and I can’t figure out what I’m missing. My date field is in local time. I wanted to get week numbers from the date, which I have working fine, but it is seeing dates in UTC I think, and DATETIME_FORMAT did not work. Formula: DATETIME_FORMAT(SET_TIMEZONE(Date,'America/Los Angeles'),'MM/DD/YYYY') Referencing the red box you can see two entries with incorrect format. I’m a little stumped.

I’m looking for help to write a formula that will help filter records that meet these requirements. I can’t seem to get it done using the filter function. Where date is within the next week or Where date is within the last 3 days and Where check box is not marked

hi i need help counting a field with multiple values separated by a semi -colon. This column catches data from jotform, which is a multi-select field.

Hello- I’d like to create a formula that allows me to track expiration dates status (three possibilities): Due soon, but not within four weeks (blank value in table); Due within four weeks (“Yes”); Past due (“Late”) I’ve been able to create this with the first two status items, but not the third (“Late”). Here’s what I have: IF( AND( DATETIME_DIFF({Expiration}, NOW(), “days”) < 28, DATETIME_DIFF({Expiration}, NOW(), “days”) > 0 ), “Yes” ) Thanks!

This is not a question more so a time saving work around for anyone else that faced this issue. Scenario: We have a column of emails in the format of ‘firstname.lastname@domain.com’ We needed to break this down to their full name in one column but the standard UPPER and LOWER case formulas left the data looking ugly and wasnt great for merging into emails. Alas, this ugly AF formula is what I could piece together from various wild google chases. Enjoy I hope it helps you. REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_R

Hello ! I built a gantt project with starting date and due date in the same column (through the due date) It is displayed like below: If I do a copy/paste, it looks like 24/06/2021 → 25/06/2021 I would like to increment the whole range (not the dates) by let’s say 10 days. I already checked dozens of posts relating to incrementing dates, but not dates range. I tried the formula :blush: And I also tried all my Excel formulas to try to manually increment these dates but it does not work. Does anyone has a solution? Thank you :smiling_face_with_halo:

Hi All - This will be my first post. I would like for our category row ‘Summary’ cell to sum the output of our number of work days driven by the following formula IF({Start Date} = BLANK(), “0”, WORKDAY_DIFF({Start Date}, {Finish Date})) The formula currently is working to output the numeric value of the number or work days, but AirTable ‘Summary’ does not recognize it as numeric and thus does offer the option to sum the results into the Summary cell.

Hi everyone and thanks in advance for your help :), I’m building a Soccer Analytics tool with Airtable My goal is to extract the results of the 5 last games for a given team. (For exemple “Win - Win - Lose - Draw - Win” or " W - W - L - D - W") So far, I’ve build two tables, one that contain the teams info and one that contain the games info. Each game records is link with two teams (Home Team & Away Team) I tried using roll-up to achieve my goal, but I was only able to get the result for the games played Away and the games played Home separately and in order to work I need the results to be in chronological order in a single cell. The closest I was able to get is by formatting the result of each game in a single string with the team names (ex: Alaves=L/Real Madrid=W) From here, I’m able to use a lookup in the team tables to get an array of all the results for the games played by a

Hi, I have landline and mobile numbers in my data base. If one of them is missing, i want to show the other, if both are missing, i want to keep it blank, and if both are full i want to concantenate them. This is my formula syntax but its not working. Can you please help correct it? IF( AND( {Mobile Phone 1} = BLANK(), Landline = BLANK() ), “Both Blank”, IF( AND( {Mobile Phone 1} = BLANK(), Landline = NOT(BLANK()) ), “Landline”, IF( AND( {Mobile Phone 1} = NOT(BLANK()), Landline = BLANK() ), “Mobile”, “Both” ) ) )

Hi, Airtable seems to only offer a lookup for a specific string. I am trying to do a lookup based on another cell’s value. Example: Company receives a request for one table. In turn, it needs to make a request for quotation from 3 companies for wood. So you want to a lookup that says "If (Table Request) + IF (Vendors Chosen), Lookup {Contacts: Table Request : Vendor). As in, show me the applicable contacts that relate to this particular vendor and this particular request. Thank you!

Hi Everyone, I’m trying to nest a long string of “IF” statements to populate a field and keep getting errors back (Sorry, there was a problem saving this field). I think it might be because I’m targeting different cells with my “IF” parameters… Can someone help me design the correct formula to handle the following: IF({CELL1}, FALSE(), “INPUT1”), IF ({CELL1}, TRUE(), “INPUT2”), IF ({CELL2}, TRUE(), “INPUT3”) IF ({CELL3}, TRUE(), “INPUT4”) and so on… needing about 8 of these in total. Any help would be greatly appreciated!