Formulas

Help please! I want to create a formula that spits out the total amount of hours work less a break time for team time sheets. I’m using time and date field to record when people work and a duration field for breaks. Can anyone give me the formula to use please?

Hello all, thank you so much for taking the time to read this, any help would be appreciated I have a question with regards to a topic that Gareth covered in is "“Allocating Resources with a Timeline View”. His demonstration is great, however the utilization field in the timeline only links to a single record, which is fine if you only take 1 day off at a time. My records need to have both a start and end, some up to a week apart. My question, is there a formula, or other method that will allow to be take both of these dates and consolidate all of the dates in between into a single cell? As this seems to be the only way for the timeline to recognize all of my holidays If there is a better solution for me to do this then any help is appreciated, but essentially the end goal is to find a way to combined projects and holiday data (2 dates) onto one timeline Thanks !

Is it possible, for a number of checkbox fields, to write a formula that will concatenation all of the field names for those fields that are checked (Airtable automatically adding those field names to the string, not using SWITCH or other operators)?

Hi community! Looking for a means of extracting the email address of the collaborator. Column 1: Collaborator: John Smith Column 2: Formula: Extract email address of John Smith Is this possible?

Hi, Is there ANY solution to adding numbers separated by commas in the same cell? I have used the SUBSTITUTE function to replace multi-select items with a numerical value and this has resulted in this kind of result: 3,2,1,4,1 Now I need to add those numbers so I get 11. The closest I’ve found to a solution is in this post: SUM of Values separated by a comma in the same sheet Ask the community Try IF( {Your Variable}, VALUE( LEFT( {Your Variable}, FIND(',',{Your Variable})-1 ) )+ VALUE( RIGHT( {Your Variable}, LEN({Your Variable})-FIND(',',{Your Variable}) ) ) ) IF( {Your Variable}, VALUE( LEFT( {Your Variable}, FIND(',',{Your Variable})-1 ) )+ VALUE( RIGHT( {Your Variable}, LEN({Your Variable})-FIND(',',{Your Variable}) )

Requesting help with a formula. Most likely an IF or Switch. We have an escalation review process that I am trying calculate the Level 1 turn time between when the review of an escalation began (Review in Progress selected) to when it was approved or denied. Escalation Decision (Single Select): Received, Review in Progress, Approved, Approved w/ Conditions, Additional info pending, Returned, Denied. Escalation Decision Last Modified (Last Modified field, only updates when Escalation Decision is changed) I was able to use the solution from a previous topic Justin_Barrett answered to grab the time stamp for when a specific Escalation Decision was an use three automations to populate a date/time field for each option. (Right now Review in Progress, Approved and Denied) How to get the time when a field was last modified to the desired value? Formulas This could also be done with Airtable’s native automations if your base is part of a Pro- or Enterprise-plan works

Hi I have This is probably really simple but I am not fab on formula. I have column 1 that is hourly rate (£) column 2 the task (looks up sheet 1) (text) column 3 Duration (looks up Time recorded via look up on Sheet 1) (Hour & mins) column 4 Budget for the task (looks up sheet 1) ((£) column 5 Final costs Column 5 I cannot get the formula right. It seems to use the day rate and multiply’s the mins so day rate of £265 worked 20 mins is totalling 318, 000, 00…Can a kind soul please help me? Thank you!

I am creating an income/expense log - I would like the currency amount to automatically become a negative number when the single select option is “expense.” Can I use IF to make this happen?

Hello . Is there a way to preview a URL link (in field 1) to show within a table field (Field 2) as a thubnail of image ?

I’m trying to set up a formula to determine the start up date of a project depending on whether it is a Tier 1 or Tier 2 event. Tier 1 will have a 4 month lead time. Tier 2 event will have a 3 month lead time. IF ({Tier} = 1, DATEADD({Event start}, -4, ‘month’) IF ({Tier} = 2, DATEADD({Event start}, -3, ‘month’) This formula is ‘invalid’ - please help!

If all users have a record in the base, is there a way to prefill the user field on a form with the user who is signed in when they access the form URL? Not having luck creating a prefill formula that accomplishes this.

Hi guys, have been sitting with this a couple rounds now and can’t get it right. Im extracting a telephone number from a column in Airtable, but when there is no phone number or the word message I get the famous #ERROR. How can I get Airtable to just show it as blank? My formula looks like this: IF({Telefon 1},(LEFT({Telefon 1}, FIND("Message", {Telefon 1}) -1)),BLANK()) Any advice is welcome Regards Jesper

Hello community, I already searched through the forum and found a helpful formula - with a minor problem based on that formula that I am trying to fix. Our datetime_format is D-M-YYYY. Background: We have a field called {start date} for when a new client relationship starts. Let’s say one client started on 1st September 2022 (past date) and another client will start on 1st November 2022 (future date). We have a report automation that we want to send out every year. The first report will be sent out one year after the client was onboarded, that process starting now. That means we do not want to send out multiple reports for a client that started on 1st July 2010, instead they will receive their first report on 1st July 2023. In our understanding we need a date formula adding one year to the {start date} and also consider the current year. Formula: IF({start date}, DATETIME_PARSE(DAY({start date}) & “-” & MONTH({start date}) & “-” & (YEAR(NOW()) + IF(DATETIME_FORMAT(DATEA

Hi can someone help me to extract the TEXT3 after the second " - " I’ve tried this formula but haven’t been able to reference the second " - " IF({Engineer + Seniority + Direct Manager}, REGEX_EXTRACT({Engineer + Seniority + Direct Manager}, “(?:\- )(.*)”)) Text sample: TEXT1 - TEXT2 - TEXT3

So I have the following: IF( TODAY() > {Date Due}, “OVERDUE” ) I have a Column named ‘Days’ for days left to do my task, so I made the following, using the nested IF tutorial: IF( TODAY() > {Date Due}, “OVERDUE”, IF({Days} < 5, “Soon”) ) I’m trying to figure out how to add my ‘done’ column to this but I keep on getting errors. So far I tried: IF( TODAY() > {Date Due}, “OVERDUE”, IF({Days} < 5, “Soon”) IF({Done} = Yes, “Done”) ) but it says invalid. idk how to return text from a multiple choice??

I’m trying to setup a training list. I have primary and secondary categories. I want to select from the primary category and then have the option to select from the secondary categories based on the primary category. How would I go about this?

Hi, I’m looking for some help please to write a formula that assigns a product to a particular shipping box based on dimensions of the product. The products have a height, depth and length, The boxes have minimum and maximum dimension thresholds for height, depth and length. How do I created a formula that assigns each product a box (or multiple boxes?) based on its dimensions? I created a demo of the Product and Box tables: Airtable Product - Shipping Base - Airtable Explore the "Product - Shipping Base" base on Airtable. Thank you!

Hi everyone. please help me with this. I have some lookup fields, they have many values on them. I want to add a formula to multiply each of those values by a percentage (%), at the time I make this formula the total shows 1.0 instead of the multiple comma-separated values with their values reflecting the discount made by the percentage field. Am I missing something in formulas to operate those values? Thanks in advance

Here is an example of what I have. Each student has 5 choices in the answers. 15 questions, 30 students and 5 choices per question. I need to create a global pie chart for the percentages answered to each question. In the example: How often do you consider someone else’s Pain? The answers: Very Often, Often, Sometimes, Rarely, Not at all I need the poercentage of how many students answered Not at all or often etc

Hi there! I’m trying to create a gradebook for a faculty member that I support. She gives students the option of dropping their midterm score if they did poorly and having their final exam make up a bigger portion of their final grade. I have a checkbox field to track that, so I want the formula for the final exam to say: if the checkbox is blank, average the midterm, final, and participation, and if it’s checked, average the final and participation. I keep getting an #ERROR value. Here’s my formula currently: IF({Reduce midterm?}=0, SUM(({Midterm exam numerical}.3),({Final exam numerical}.5),({Participation numerical}.2)), IF({Reduce midterm?}=1, SUM(({Final exam numerical}.8),({Participation numerical}*.2)))) Thanks for any help!

Hi, I’m trying to make a formula if a checkbox is tiped: If checkbox is tiped than calculate the price by multiplying a value in column A by another value in columbn B and show the result in column C. I could have something but the result shown was “error” when the checkbox was untiped… Thanks a lot for your help !

Hi community, I’ve built a labor tracker where we will track hours worked per Person/Project/Day. Based upon an 8 hour day, I want to calculate the percent of their day worked on that particular project. For example, if Veronica works on Project P-035.1 for 4 hrs, she submits that through a form by entering her name (linked field), the date she worked, the project (linked field) and the hours she spent on that project that day. In the case of working 4 hrs, the percentage worked on that project for the day should be 50%. I’m hitting a total blank on how to do that with a formula. (right now the % field is just a manual thing, because I don’t know what the formula should be) After that, I’m hoping to figure out how to calculate percentage worked on the project for the Month, because that’s how our budget predictions are created, and I want to compare and contrast predictions with actuals. Any help would be greatly appreciated!

I don’t know if this is possible to do - but if so would be of huge value to me. My Challenge: I have records in Airtable that contain ‘Media Bookings’ for advertising campaigns As an example: Campaign: Launch Site: Youtube booked Impressions: 1000000 Media Cost: $15000 Start Date: 01/10/2022 End Date: 31/12/2022 I need to easily convert this information into ‘Week commencing’ detail so that I can prorata the impressions and cost based on weeks of the campaign e.g. W/C 03rd October, 10th October Value of doing this: this would help significantly with monitoring pacing of campaigns, and suporting accurate invoicing of costs to clients Is there a way of doing this within Airtable… thank you so much for your help!!!

I need to compare two date fields to get a specific result. I am not an expert at Airtable, so my explanation isn’t very good :). My base is for tracking training. I need to mark a month “excused” if a person’s hire date is after the meeting date and then mark the month as completed or missed if their hire date is before the meeting date. The hire date is 9/1/2022, and the meeting date is 10/1/2022. That person is required to complete the monthly program. Any suggestions on a formula? - Wendy in Michigan.

Continuing the discussion from No More Static URLs for Attachments?: No More Static URLs for Attachments? But I’ve now lost hope that this WON’T happen if we complain enough… I head back from the support team who said they would share my concerns with the product team… I do believe in miracles… Ha ha! I guess you got your wish (for a while anyway).