Formulas

I am a beginner and wanted to make a few custom changes through formula’s and other customisation’s. I need help with the two doubts below. Also take reference from the two photos. 1. How do I setup the “Hours” formula field such that even if its 10 mins more than a perfect hour it will round it off to 2 hrs and so on for any more amount of time.2. In the first photo, is it possible for the “End Time” to solely be a time field Instead of Date and Time.Would be really helpful I could get some solid advice on this!

Per day, the employee works 8 hours and can be distributed in one or more projects (tasks) in this period. If he worked on only one project it would be 8 hours allocated to the project (easy), however if he worked on two projects it would be 4 hours each and so on. I need to generate a report of each person’s allocated hours by project. The challenge is that the tasks from different projects overlap for a period of days and I don’t know how to evenly subdivide those 8 hours by the amount of overlapping tasks.

Apologies in advance if this was answered elsewhere, I did try to find it.I have a lookup cell that is pulling information from another sheet.  In that other sheet, the cells all share an attribute called “Daily Flow.” I’m wondering if there’s a way for me to format this cell so that if there are duplicates, (like you see “An insect’s world” is shown multiple times), that it only lists it once?Thanks for the help!

I have been using this formula, and it works: IF({Company (from Distribution Locations) (from Invoice 2)}="Key Fresh 8890",{Cost *1 (from Products 2)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Marathon 16050", {Cost *4 (from Products 3)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Chevron 10255", {Cost *5 (from Products 4)},IF({Company (from Distribution Locations) (from Invoice 2)}="Chevron 3251", {Cost *2 (from Products 5)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Marathon 5", {Cost *2 (from Products 5)},{Cost (from Products 2) 2}))))) Now I want to add another nested IF; however, with "and," it is not working for me. I want to include the following: if(AND({Company (from Distribution Locations) (from Invoice 2)}="Chevron 3100",{Date (from Invoice)}<"03/19/2024"), {Cost *4 (from Products 3)}, {Cost (from Products 2) 2}) When I add this last

I have a formula in the primary field of my table that combines a Date/time and the name of the brand in the appointment.  Formula I am using: DATETIME_FORMAT({Appointment Time},'MM/DD, H:MM A')&" - "&Brand results are randomly adding 2, 4 and 6 minutes to the time format, ex:04/09, 9:04 AM - Gregory(appointment time is 9:00 am in the calendar column) I'm lost, what am I not specifying that would prevent this? Thanks!

Im trying to make a task management system, where task 1 would have multiple subtasks and subtasks would have their own subtasks etc. The issue i'm having is im trying to make a function where if a task has no "task parent", then the "Task Tier" would be 0. If the "Task Parent" has a "Task Parent" of its own, to start counting how many steps up the tree it had to go to find no value, and count how many steps it took. Any other way that would not force me to have additional tables, or limit the number of Task Tiers, would still be fine Thanks in advance

Can anyone recommend an Airtable Consultant, preferably in the UK, who can help me with some fairly simple formulas which for some reason, I cant work out why, are not working and/or producing missing data in some rows. I feel very scrooge-like and miserly saying this, but its the reality: I work for a charity so they cannot be too expensive. But I would be very grateful if somebody could help me. I have designed a multi-layered survey that evaluates the diffferent well-being dimensions of our clients and if I cant get it working soon I fear my project will be binned and I will be devastated as I've already done a lot of research and learnt airtable especially from scratch just do this- plus I love Airtable. So if somebody could help me that would be brilliant. 

I have a table "Products" and a table "Artworks". The Product table has a lookup field to the Artworks table. In this lookup field, I assigned multiple Artworks to each Product (e.g. "Product 1" has assigned "Artwork A", "Artwork B", "Artwork C", and "Artwork D").Product 1 can only go into production once all Artworks assigned to Product 1 have approval status "Approved".In the Products table, I added a rollup field that shows the records from the "Approval Status" field from the "Artworks" table. By default, the values appear like this in the rollup field: "Not submitted, not submitted, Approval pending, Approved"Instead of listing all the records in the rollup field, I want the rollup field to show:IF all records = "Approved": output "ALL ARTWORKS APPROVED"ELSE: output "APPROVAL PENDING"or alternatively:IF any record is not = "Approved": output "APPROVAL PENDING"ELSE: output "ALL ARTWORKS APPROVED"

hi there,I am trying to create a nested IF that checks if four cells are blank, and if they're not, returns the value that's there.When I use the below formula, it only ever checks the first IF, and it's driving me nuts!Can anyone help? I want it to return BLANK if there are no matches. IF( ArtSaleChecker2!=BLANK(), ArtSaleChecker2, IF( PromSaleChecker2!=BLANK(), PromSaleChecker2, IF(OtherSaleChecker2!=BLANK(), OtherSaleChecker2, IF(GenSaleChecker2!=BLANK(), GenSaleChecker2, BLANK() ) ) ) )  

Hi, as a result of my trying to execute a solution from previous post, I created a formula to combine name fields. IT cannot figure out what aim doing wrong. IT seems perfectly logical to me. But it does not work. I have posted the formula and a screenshot of the data below.The data is First Name, Last Name, SP First Name, SP Last Name. First Name and Last Name are short text fields. SP First Name and SP Last Name are Lookup fields.Thanks, ScottTRIM( {First Name} & IF({Last Name} != {SP Last Name}), " " & {Last Name} & " and " & {SP First Name} &" " & {SP Last Name}, IF({Last Name} = {SP Last Name}), " & " {SP First Name} & " " & {Last Name} )   

Hi all, I'm pretty useless with formulae generally and just wondering if anyone can help with this.I'm trying to generate a formula field that is based on a source date. The formula is to calculate 8 months + 23 days from the source date (i.e the 23rd day of the 9th month after my source date).I also want to include an "If" being; If field A is populated, then field C should be the 23rd day of the 9th month after field B. Does this make sense?Below is what I have but depending on whether the source field has 28, 30 or 31 days, I am getting variable dates.IF({Next Accounts Reqd},DATEADD(DATEADD({Next Accounts Reqd},8,'month'),23,'day'))

Hi! I'm trying to set up an inventory system that takes into account materials used for each of our products. On my products table, I have a column for materials and one for finished goods. I've set it up so that when we assemble a product, the materials qty decreases and the finished goods qty increases. This seemed to work well until I ran into this issue:We make notebooks, so the raw materials are the covers. So when we make say, notebook A, notebook A covers would get deducted, when we make notebook B, B covers are deducted and so on. The problem is we also make 3-packs, with one of each notebook A, B and C. These sets are a different product, BUT they use the same raw material (covers) as the single notebooks. I can't seem to figure out how to make it so when we assemble a 3-pack, it automatically deducts one of each cover for A, B, and C. We have several 3-pack designs too, which further complicates it. Any help is very welcome! 

Hey everyone, I had a quick question to see if this is possible. I view my projects in a timeline view that is grouped by what phase the projects are in. The top group are projects that are coming up, but sometimes I dont have a date for an upcoming project aka I dont see the project in my timeline view. I dont want to forget about possible projects that are coming up with no specified date and was wondering is there like some sort of an option with airtable timeline view to have records that dont have a date show up under the group name on the left bar or somewhere where I can remember like ohhhh right that needs to be scheduled? Thank you for any help!

Hello - we use a formula field to create unique & automated "Activity Titles" for records.Our current formula pulls from various fields:Program (multi select)City (linked field)Type (single select)Date (date)Current formula is: Program & " - " & City & " " & {Type} & (" ") & " (" & (DATETIME_FORMAT(Date,'YYYY/MM/DD')) & ")"This results in an Activity Title that looks like: Corporate Event - Philadelphia Webinar (2024-03-21)The problem we've run into is that some of our Activities are with multiple cities - 30+.So when the formula pulls into those Cities into the title, the title then becomes unusably long - Corporate Event - Philadelphia, New York, San Francisco, Pittsburgh, Buffalo, Winchester, Miami, San Diego, P

Hi AllI am looking for a formula to extract project status from a string with multiple response column. Criteria is IF "Market Single" is appear anywhere in the "Updated Status", then return as the 1st word in that particular string. Example below: the output I am looking for in "Final Status" is:- Row 1: Ongoing (Albania in Market single, Ongoing being the first word in the string)- Row 2: Hold (Andorra in Market single, Hold being the first word in the string)- Row 3: Cancelled (Antigua in Market single, Cancelled being the first word in the string)Currently this is the formula I have - and it returns all three response as Ongoing - which is not what I am looking for: IF(  FIND({Market single}

Hey all! I'm back with a new issue that I'm hoping won't be too hard to solve but I can't seem to find the solution myself. I have two fields that I need to combine. Field 1 contains a variable, such as GKM3, GKM5, etc. Field 2 contains comma-separated values like L16, L17, L18. I have been trying to find a formula to combine the value of Field 1 with each of the values of Field 2. So the output in the 3rd field would be GKM4L17, GKM4L17, GKM4L18. Unfortunately, when I try a concatenate or substitute function, it only appends the Field 1 value onto the first value in the list, like in Field 3.  I would really appreciate any help! 

Hi there, I would like to use a formula in the primary key column of an airtable data set to create a unique ID, or autonumber, but i want that number to recalculate when i insert a new row anywhere in the master data set so that the ordering is retained. if you use the autonumber field type, it will create the record using the next available number, then all my views start to loose the ordering i want.is there a way to write a formula that will re-calcuate the primary key column to retain the existing order, with the new record in place.  For example: 1 TestA2 TestB3TestC If i insert a row between TestB and TestC, the primary key would show as follows:1 TestA2TestB3Testnew record4TestC 

I'm trying to write a formula to remove the "➟" if the preceeding field is empty.Current Formula:Origin& " ➟ " &{Destination}&" ➟ "&{Backhaul From}& " ➟ "&{Backhaul To\\Return}& " ➟ " &Origin &" - LANE #" & {LANE#}  Here is the output of the above formula.  Formula above works great, (example 1 below) but if there is no data in {Backhaul From} and {Backhaul To\\Return} the result is example 2 with 2 extra arrows.  Example 3 is the desired results.

I looked for other solutions on the forum and either I'm too much of a novice to know how to solve it or it didn't work for my problem. I have a rollup field that is collecting multiple dates. I'd like to have it only return the earliest date in the list.  Is that possible?  I tried using MIN(values), but only got a 0 in the field.  Thanks!

Hey all! I have a problem that I hope you can help with. I have a massive amount of data that I need to put into Airtable that I received in the below format. However, I need to put each individual instance into a linked record field in AT, so the 1-5, will need to be 1, 2, 3, 4, 5. The amount of data I have will take me months to manually import one record at a time. I am hoping to create a formulate that will pull out the hyphenated numbers and return a value of GKM5L1, GKM5L2, GKM5L3... etc for the first line so that I can copy and paste values into AirTable instead of entering manually. I would really appreciate any help! Thanks!

I have addresses with several fields, some fields are not always necessary.MemberOrganizationMail Address2nd Mail AddressCityStateZipo CodeTrying to get a multi line field that has no blank lines.Here's the formula I am using. I cannot find a way to get rid of the blank lines. What am I doing wrong?I also included a screen shot of the existing multiline field.Thanks in advance for any help, Scott TRIM( IF( AND( Organization, {2nd Mail Address} ), Member & "\n" & Organization & "\n" & {Mail Address} & "\n" & {2nd Mail Address} & "\n" & City & ", " & State & " " & {Zip Code}, IF( AND( Organization, {2nd Mail Address} = BLANK() ), Member & "\n" & Organiza

Hi! I am struggling to come up with a formula for this problem I am having:I need to create a formula on Airtable that will list all the Sundays that fall between a field called Created Time (date), and another called Start Date (date that is after the Created Time).For example, if the Created Time was today: 03/19/24And the Start Date was a month from now: 04/19/24I would like this formula field to display the following: 03/24/24, 03/31/24, 04/07/24, 04/14/24If any of those 2 dates are a Sunday, they should be excluded from the formula.Can this be done? Anyone have any tips on how to do this? I am trying to find a workaround as Date fields aren't very dynamic when used in Automations, and would like an automation to auto-populate a linked record field (within my "Events" table) with all the Newsletter emails I have on a separate table's calendar for Sundays.

Hello, I am new to formulas so I was hoping someone can help me with the formula below. I am trying to create two new formula fields.In the first new formula field, I am trying to do the following: If the 'Category' field is Finance and the 'Owner' field is Jackie, Anton, or Jalen, then have the new field return "Assigned" If the 'Category' field is Finance and the 'Owner' field is not Anton, Jackie or Jalen, then have the new field return "Not Assigned"In the second new formula field, If the 'Asset Type' field is Marketing and 'Attachments Resource' field is empty, have the new field return Awaiting AssetsIf the 'Asset Type' field is Marketing and 'Attachments Resource' field is not Anton, Jackie or Jalen, have the new field return Agency OwnedAppreciate the help in advance!

Hey! I'm trying to figure out how to add a portion to a formula that would ensure that it's set for 90 days after a particular field, or before June 1st. The current formula pulls from a date field for when they registered (Date Registered), and adds 90 days. How would I add a restriction onto this formula so it's 90 days, or until June 1st?  DATETIME_FORMAT(DATEADD({Date Registered}, 90, 'days'), 'MM/DD/YYYY')

I'm using this formula in a Deadline Alert column: IF({Deadline} < NOW(), "Late", IF(IS_SAME({Deadline}, TODAY(), 'day'), "At Risk", "On Time")) I have a separate Status Column to mark tasks Done. Is there a formula to show "Done" (instead of "Late") in the Deadline Alert column when "Done" is selected in Status?