Formulas

Hi all! Thank you so much for helping with this math! I keep trying but short on solutions in how I’m thinking about this. I am attempting to write a formula that will assign Paid Time Off (PTO) to an Employee based on Start Date and consuming our Business Rules for assigning PTO. We have a Field labeled ‘Employee Type’ with values Hourly or Salary. Salary returns the result “Open PTO” and Hourly needs to do the math. Hourly PTO Rules Year 1 = 80 Hours PTO Year 2 and 3 120 Hours PTO Year 4 and beyond 160 Hours PTO Optional Override Field {PTO Override} - if a value is entered here use it and ignore the Yearly rules above. Ideally when an ‘Hourly’ employee ‘Starts’ they will get Annual PTO prorated up to 12/31/YEARNOW(). And the balance of their first 80 hours commencing on or before the {StartDate}. Then the pro-rated amount of Year 2 (120 Hours) up to 12/31/YEARNOW() +1. In other words, an employees Annual (1/1-12/31) PTO amou

Hello! I’m not great with formulas, but I’ve spent hours on google and airtable forums trying to figure out how to do this. Let me see if I can explain this well. I have a string of text and I’m trying to find the first name of the person listed in the text. The text looks something like this: Hi Stylist, You have a new reservation. Customer First Name: John Customer Last Name: Doe Customer Phone #: 1234567890 Customer Email: johndoe@gmail.com Location: Salon Date: Thursday, December 9, 2021 Service: Package 1 Employee: Stylist Time: 1:45pm Yours Truly, The Team I just want to grab the First name only from the string and I can’t for the life of me figure out how to format the formula. Any help would be much appreciated!!

Hello guys! I would like help creating a “Monthly Billing” table. The question is: I have a table called “Jobs” where I add all the jobs that my clients ask me for, this table contains several columns, including a “month/year”, and also a column referring to the value of each job requested. Now I would like to create a table referring to the “Monthly Billing”, containing the “Gross Monthly Billing” and the “Net Monthly Billing”. In “Monthly Gross Billing” I would like it to be the sum of the value of all jobs referring to month “X” in the “Jobs” table and in “Monthly Net Billing” I would like it to be {Monthly Gross Billing} - all values ​​from column “Cost/Month” of a third table called “Cost of Operation”. It would be very important that this table called “Monthly Billing” could be organized by Month. I’m new to Airtable so I’m still learning how it works, thanks so much for all the help. I put some pictures of my tables to make it easier t

Hi, I am trying to get a record to delete after 24 hours. I have copied a formula from another thread and changed the time from hours to minutes. I have no idea about scripting so wasn’t able to implement the second part of that thread. I currently have the “delete” column set to add the word “delete” to the formula cell after 1440 minutes. This then updates another view filtered by “if delete is empty”. In other words, if delete is empty in the new view the record will show on the front-end currently Softr. This formula seems to work but it’s sluggish and doesn’t update on the 24-hour mark. IF(DATETIME_DIFF(NOW(), CREATED_TIME(), ‘minutes’) > 1440, “delete”) My long-winded and probably not well-explained question is: How do I auto delete a record after a 24 hour period? Thank you.

Help! I keep pasting this formula into Airtable, and it deletes it after the 5 Item section. Even if I can get it to “take” the 7 and 10 item part of the formula, it says there’s an error. Where is the error? IF(AND({Number of Items}=5, {VA Recipient?}),((65-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=5, 65-{Item Discount}+{Shipping Total Cust. Paid}), IF(AND({Number of Items}=7, {VA Recipient?}),((85-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=7, 85-{Item Discount}+{Shipping Total Cust. Paid}), IF(AND({Number of Items}=10, {VA Recipient?}),((100-{Item Discount})*1.06)+{Shipping Total Cust. Paid}, IF({Number of Items}=10, 100-{Item Discount}+{Shipping Total Cust. Paid})))) This is trying to say: If there are 5 items and a VA Recipient, then do X. If there are 5 items and NOT a VA Recipient, then do y. Then it repeats for 7 items and 10 items. Thoughts? Thank you all!!!

Hi, this is my first post, and forgive me because I’ve been newly converted to Airtable from notion and excel. I’m trying to create a formula, but my problem is that some cells are returning ERROR or negative numbers, which makes sense because in some cases the roles are ones without a physical start date, or their start date is in a future period. In excel I’d normally overlay this up with an iferror and also nest the future start date, but it does not seem to work in the same way. I’d appreciate any help with this. Thanks! IF(({Annual Base}/12/WORKDAY_DIFF(‘2021-12-01’,‘2021-12-31’)*WORKDAY_DIFF({Physical Start Date},‘2021-12-31’))>{Annual Base}/12,{Annual Base}/12,{Annual Base}/12/WORKDAY_DIFF(‘2021-12-01’,‘2021-12-31’)*WORKDAY_DIFF({Physical Start Date},‘2021-12-31’)) .

Here’s my entire formula: WORKDAY_DIFF({Start Date}, {End Date}, ‘2022-01-01,2021-12-25,2021-12-24’) Works great, but I would like it to only execute if the Start Date and End Date have been filled in. I’m trying to suppress the #ERROR that shows up in the cells otherwise. Thank you!

Hi everyone, I try searching the numerous existing posts but I couldn’t find my answer. Here it is, I need to migrate a database from Notion to Airtable and I’m not sure if the formulas in airtable enables me to do the same as the existing Notion one :slightly_smiling_face: Having a formulas counting different multiple select occurences and adding text within the results too (cf screenshot) So the idea, as a result, is to get 1 line per language with the total of the different languages occurences: Français : 4 Espagnol : 5 Anglais : 2 Etc. I tried something like IF(LEN(SUBSTITUTE(Maternelle, "Français", ""))>1,"Français:"+(LEN(Maternelle) - LEN(SUBSTITUTE(Maternelle, "Français", ""))) / 8,"") But I get NaN as a result → does it mean formulas can’t have calculation +

SOS!!! I just need to know when to do a mid-evaluation for an employee…so I need this formula to tell me when they are three months in. Thank you :sweat:

I have a short text field with a record containing the text ‘Açaí Bowl.png’ I have another formula field that strips the file extension from text in this field, but instead of the expected result of ‘Açaí Bowl’, I get ‘Açaí Bong’. :smiling_face_with_halo: Somehow the last two characters of ‘Bowl’ are transforming? Here is my formula text, which usually works fine: IF( OR( RIGHT({File name}, 4) = ".jpg", RIGHT({File name}, 4) = ".png", RIGHT({File name}, 4) = ".pdf" ), REPLACE({File name}, LEN({File name}) - 3, 4, ""), IF(RIGHT({File name}, 5) = ".jpeg", REPLACE({File name}, LEN({File name}) - 4, 5, ""), "..." ) ) Does it behave the same way on your base? Is there a way to prevent this? Not a huge deal since not a common case but it would be nice to know.

Hi community, I need some help in regards to triggering a reminder for an inventory base. I’ve got it working for a 1 month basis but needs to be recurring every month. For example: I receive a product that has 9 cartons, on a date 11/20/2021 (Date Received), but i charge a storage fee every month this product doesn’t get shipped out. I set a 1 month deadline date of 12/20/2021 (Storage Deadline). I have a column that has # CTNS (what originally was received) and a column that shows CTNS SHIPPED and CTNS LEFT My formula is as follows: IF({Storage Deadline}, IF( AND({Date Received},{# CTNS} - {CTNS SHIPPED} > 0,{Storage Deadline} <= NOW() ), "Trigger Reminder" )) My automation runs only when the deadline reminder column displays “Trigger Reminder.” Was wondering if there is a way to get it to trigger eve

I was wondering if someone was able to help me out. I am looking use a formula to calculate the amount of days until an item has to be renewed. What I would like is that when it’s nested within an IF statement it is calculated as a VALUE rather than a STRING. The reason for the IF statement is that when the Expiry Date is empty, no value will show in the “Days Remaining” Field. Here is the formula: IF({Passport (Expiry)}=BLANK(),’’, DATETIME_DIFF({Passport (Expiry)},TODAY(),‘days’)) I’m likely missing something really obvious in this regard.

Hello guys! I would like help to do a subtraction according to the values ​​that are being established. The situation is as follows: I work in credit format with my clients, so they start the month with 8 credits, as they request me, I establish the value in credits and these are consumed. Because of this situation, I would like to create 3 columns, 1 column “Cost in credits”, 1 column “Remaining credits” and 1 column “Total credits”. As I establish the cost of each material, I would like the column “Remaining credits” to be the result of “Total credits”-“Cost in credits”(for each row). I know Airtable has a different focus than spreadsheets, but the point is that I’m really enjoying the platform and I’d like to centralize as much activities as possible here, my company is just starting out, so splitting things across multiple platforms ends up messing up the flow of work. If there is a way to do what I said, I would be very happy. Thanks in advance!

I keep receiving an invalid error when trying write this formula: IF({Close ID} != “”, "imported”, IF(AND({Commercial?} = “yes”, {Close ID} = “”), "ready to import”)) field 1 is a single line text field and field 2 is a single select field. Both If statements work when using them separately. Any help would be much appreciated!!

Hello, So far a big fan of the product, however I am a bit puzzled how to tackle this problem. I have a customer list which are either ‘active’ or ‘in-active’; based on their plan. I would like to make an overview which automatically calculated the monthly recurring revenue from people with an active plan. Currently I set manually the active & in-active date. So my logic was as followed: January 2021: IF ‘month= 01’ & ‘year= 2021’ is WITHIN ‘active date’ AND ‘in-active date’ THEN SUM ‘Monthly recurring revenue’ To add would like to have also a list of the amount of active customers: January 2021: IF ‘month= 01’ & ‘year= 2021’ is WITHIN ‘active date’ AND ‘in-active date’ THEN COUNT ‘Customers’ As I am not an experienced Airtable user; would like to know if someone can help me. Since tried a few things; but I seem not to find my way

Hello to everyone, I really need to know if some one could help me, I made my formula but ir give me errors, I let you my formula to be reviewed: https://airtable.com/shrPSoWMs4e0xYtGzprefill_nombres=receqiENsD9NZtYwT I am not sure how to get the correct syntaxis. Regards

I wrote a formula to add today’s date when a specific text appeared in another (single select) field. But it doesn’t hold that date; it changes each day. Is there a way to freeze that date to when the text first appeared? Thanks in advance!

is there a formula to count the number of days between two date fields?

Hi All, I’ve got a formula that tells me when a job post is about to expire, when it is expiring today, and when it’s expired. I would like it to display an “Acvite” status for all other days, is this possible within this formula? Any help would be appreciated! IF(IS_AFTER(TODAY(), {Expiration Date}), “ :x: Expired”, IF(IS_SAME(TODAY(), {Expiration Date}), “ :firecracker: Expires Today”, IF(IS_AFTER(TODAY(), DATEADD({Expiration Date}, -3, ‘days’)), “ :raising_hand_man:t2: Expires Soon”))),

Greetings, I put together a video that illustrates what I am looking for.

Hi! I’m new to regular expressions and I want to create a formula in Airtable which switches the text left from the ‘-’ to the right and visa versa. So U27Y11/12-U13Y07/08 needs to become U13Y07/08-U27Y11/12 Basically swapping the right and left side. (the ‘-’ is always the separator) Can you guys help me out?

Hi everyone So I have this simple IF formula IF({Method}=“Digital”,1,0) Where {Method} is a multiple choice field, and can contain different type beside “Digital”. My problem is that this formula returns 1, only when {Method} is strictly equal “Digital” and nothing else. And I want it to return 1 if one of values is “Digital”. For example: Digital, Offset, Heatpress

Hi, I’m setting up a bookkeeping system for my business. What I need is that when I select the option “Expense” from the Single Select option in the Type field, I want to add a negative sign with the Received Amount field so that it is subtracted from the sum. I’m attaching a screenshot for clarity. I’d really appreciate it if someone can help me with the formula for this. Thanks a lot :slightly_smiling_face:

Hi, I’m trying to come up with a formula that extracts the first name, last name, and email that I’m getting into Airtable from another application. The format of the UID I’m receiving is shown in the couple of examples below: John_Smith_john@example.com Tracy_Jones_tracy@example.com Basically, it’s going to be first name underscore last name underscore email. I need to write three formulas, in my Table for the First Name, Last Name, and Email columns. I’m not sure how to proceed especially because the UID string length will be dynamic based on users’ names and emails, so I can’t use a LEFT or RIGHT formula to count and extract the text. I know there should be a way to do this, but could really use some help with the formulas. Thank you.

Hi! We’re trying to figure out what formula is to be set in place for this scenario: We have three forms, all connected to autopopulate on Airtable. Column A = Form A Column B = Form B Column C = Form C Basically, what we have a column with the formula that determines what form a client has filled out based on what Column has been autopopulated - let’s call it Column X. What we want to happen is = if Column A has been populated, Column X says “Form A”; if Column B is populated, Column X says “Form B” and so on. Only one column will be populated at a time per row (ex. Row 1 Column A populated, Row 2 Column B etc), so I hope that makes the formula creation easier. Thank you in advance!