Solved

Building a payroll system and need formula help with splitting up total hours between Daily Hrs, time and a half pay, and doubletime. Here’s my setup: My “Total Hrs/day” is the actual total of all hours worked, calculated with: (OUT-IN)-BREAK Then, I want to limit the amount of hours from the “Total Hrs/Day” that can go into “Daily Hrs” to 8 hours of regular pay. I do this with: IF((OUT-IN)-BREAK<=8, (OUT-IN)-BREAK, 8) This means that if 10 hours were worked, “Daily Hrs” is capped at 8. I want the remaining two hours to populate into the “time and a half” to be multiplied by that 1.5x rate. The caveat is that I need this to be capped at 4 hours of overtime, before spilling any remaining hours from the “Total Hrs/day” into “doubletime”. IF( (OUT-IN)-BREAK>8, ((OUT-IN)-BREAK)-8, BLANK() ) I can then spill the remaining hours into the “time and a half” with this formula above, but I can’t figure out how to also cap it at 4 hours before sending the remainder to doubletime. I tried

I have 3 tables: Units - a list of all our technology units whether classified as inventory or leased to a customer Customers - linked to the Units table Unit Setup - this is a form to be used by the shipping department to go through shipping checklists AND assign a Unit to a Customer. I cannot figure out how to get the Unit Setup Form to update the Unit assigned to the Customer. I can get it to add a new linked record column… but I want it to update the main associated Customer record to the Unit.

While I’ve seen plenty of posts on formulas based on whether a checkbox is checked or not in another field, I’m curious as to whether it is possible in the inverse? I want to create a formula that will say, “If condition exists in some field, mark checkbox in another field.” Can this be done?

Hi! I am trying to set up an automation so that when we update the record, an email is sent out to a client with the updated notes. However, when I use the date link in the Automation, the email test comes back with “2020-10-30T18:04:00.000Z” as opposed to the US format (10/30/2020 at 6 pm). Am I missing something that is causing this to come out this way?

Hi All, I’m looking for some help in building out a formula field (Transfer Tax) that is formatted as currency. I’m new to Airtable but have a lot of Salesforce.com experience. The Transfer Tax field provides the dollar amount that will be charged when the dollar amount in another field (Purchase Price) falls in a certain range. The rule is: the transfer tax is $3.30 for every $1,000 of the purchase price. Essentially, a Purchase Price anywhere from $0-$999.99, the Transfer Tax field should be showing $3.30. If the Purchase Price is anywhere between $1,000-1,999.99, the Transfer Tax field should be showing $6.60. If the Purchase Price field is anywhere between $2,000-$2,999.99, then the Transfer Tax field should show $9.90. This continues for infinity. Ideally, I only need it for a purchase price going to about $100,000. In Salesforce.com, I would likely do this using something like the IF and QUANTITY logical and numeric functions. That said, I don’t think Airtable has the QUANTITY n

Hey everyone. I’m using this formula to calculate how many days it took for a response. DATETIME_DIFF({Response date},({Submission date}), ‘days’) & " days" I’d like to add a part to the logic that says if {Response date} is blank, calculate days between Submission date and today’s date. Any ideas?

Hi, all! I’m facing a challenge to do the following: Column A with multiple choices (All, Sheep, Goats… up to 12 more) Column B with multiple choices (Universal, Black, White) Column C with single choice (F, SE) Column D with multiple choices (3 months, 6 months, 9 months) Because I have more than 500 records I need a formula to do the same as filtering manually. May be the topic should be divided in two parts: I want to have a formula that can tell me if a certain combination for a record is “true”. If it is true, we can name it (All-Sheep-Black-F-3-months), then the formula displays the name of the combination (All-Sheep-Black-F-3-months). I found that in excel this could be done with ISNUMBER and FIND but Airtable doesn’t support ISNUMBER. The problem for me here is that I cannot use AND and OR in columns with multiple choices because it doesn’t work or I just can’t make it work. For combination All-Sheep-Universal-Black-F-3-months this is what I use: IF( AND( OR({Column A}=“All”,

Hello - I’m having trouble with IF and CONCATENATION formulas Here is what I’m trying to achieve: Concatenate “SUB SKU” - “PRODUCT NAME” If “SUB SKU” = Blank, concatenate “Parent SKU” - “PRODUCT NAME” I tried it this way but it’s not working. IF({SUB SKU}, CONCATENATE({Sub Sku},” – “,{Product Name}),IF({SUB SKU}=BLANK(),{Parent SKU},” – “,{Product Name}) Appreciate any help. Thanks!

Hi All: This is incredibly simple I think but want to verify. In my view, I’m just filtering out the entries that older. I’m using Created Time and when I choose any of the preset options (one day ago, one week ago, etc.), it all works fine. When I enter in a specific number of days though, nothing will appear in the view (but should). When using a specific number of days ago for a filter based on created time, do I need to have a separate calculation for day count and base the filter off of that?

Hello guys, I have this string : DECO > OBJET DECO > AUTRE OBJET DECO and I would like only : AUTRE OBJET DECO so only what is after the second >. I tried with : RIGHT( {Catégorie}, FIND( “>”, {Catégorie} ) -1 ) RESULT was " DECO" (The last 5 characters) I really don’t understand why only the last 5 characters.

once an Action is selected, it can’t be deleted. The only way to fix it is to delete everything and start over (keeping the Trigger alone is not much use). Hoping this is on the roadmap for urgently needed fixes.

Hello Community, When checking the API Doc, I can see Airtable is providing size and thumbnails dimensions for an Attachment Is it possible with a formula (not script) to pull properties from the first attachment? I tried several things like: Attachment[0].thumbnails.full.width … but didn’t succeed :confused: Regards.

Hello! Newbie on Airtable, so I’m pretty sure this is a really daft question, but after looking through the help and this forum for about 2 hours and not finding anything, I’m now starting to wonder if my goal is even possible! I have 2 tables: An Authors table has name/contact details/author image fields. A Content table, which had a load of fields, including a ‘link to another table’ field to display the ‘author name’ field from the Author table. What I want to do is to have a field in the Content table with a tick (or yes/no or some sort of indicator - I kind of don’t care what it is!) which shows based on whether there is or isn’t an image uploaded in the ‘Author image’ field on the Author table. So kind of this (which I know isn’t actually a formula, just trying to lay out what I’m trying to achieve!): if field ‘author image’ in table ‘author’ is not blank, then show :white_check_mark: in field ‘got author image’ in table ‘content’ Any ideas? Is it possible? Or am I trying to ma

Hi AirTable friends, I have records that each have a long text field which is TOO long. That long text field is called “Excerpt.” What I’d like to do is create a formula field called “Trimmed Excerpt” that copies the content from “Excerpt” field but limits the word count to, say, 50. What’s the formula for that? I Googled for a solution but came away empty-handed. Thanks to those of you who take a crack at this one!

Can you do simple references within formulas? Current formula: IF({Frequent escalation?} = 1, “However, {he/she} frequently escalates, and so {he/she} needs a significant amount of support.”, “{C He/She} does well there with minimal supervision and supports by adults.”) The formula is working, except the pronouns are not. They are of course referencing other formulas, and there is a checkbox called “Frequent Escalation?” If not (siiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiigh) any suggestions on how to get around this? I am eventually wanting to get to more advanced texting formulas, but need to figure this out! Thank you so much!

I have four fields: Unix Timestamp - a single line text field that contains the unix timestamp Date & Time - a formula field that converts the unix timestamp into a readable date and time using the following formula: DATEADD(‘1/1/1970’,{Unix Timestamp},‘seconds’) Date - where I wish to extract just the date from the Date & Time field Time - where I wish to extract just the time from the Date & Time field How do I get numbers 3 and 4 to work?

Hello Airtable family, I’m struggling to figure out a solution on this one and hoping that someone might have a viable approach I can try. Background Info: We are using Airtable as a small business CRM, and the services/products sold are in an ‘orders’ table, which links to a ‘customers’ table. Each order links to a customer, and each customer can have several orders linked. The Mission: When someone calls our shop, it is common that we need to look at their customer record and see information about their orders on the fly. We want to be able to find the customer quickly in the customer table, expand the record, and see particular information about the orders. The Challenge: Our base is, well, rather complex utilizing nearly 100 columns/data points on each order. Filled with formulas, lookups, calculations and even static fields generated from automation scripts. When we input a new order, we use a view that curates these fields to only the ones needed for input. This is also the

Different usage limits for Sync depend on your Airtable plan. See the screenshot below for a quick highlight, or visit airtable.com/pricing for more details.

Here’s the formula I’m using: IF( {Number Field 1}!=’’, ({Number Field 1} / {Weight (number field)} + {Number Field 2}), IF( {Number Field 2}!=’’, ({Number Field 1} / {Weight} + {Number Field 2}), ‘’) ) My goal with the formula: I’m trying to reach an inventory count as a result of this field. A) If the inventory count is low, we can manually enter the exact count of the part into “Number Field 2” B) If the inventory count is higher, we get an approximate count based on weight. So we enter the weight into “Number Field 1” of the total part. It calculates total weight vs the part weight to give us a part count. C) If for some reason during the process we weigh the inventory, but find more of that part, we sometimes add the exact count on after. So if there’s numbers in each of the field, we need the formula to account for both. The result of the formula “works”, as in the numbers are right. But it isn’t a number output. So for us to use the result of the field, or format the output, it

I want to provide a workaround solution given that Airtable natively cannot do much with the change log, and I want to create a column that would update another column (within the same table) every 1 or 2 days. For example, something like the below: Column B = IF( Last Modified Date < Today - 1, = Column A, IF FALSE DO NOTHING). Through this I am hoping that even though Column A is continually changed, I can see the previous value of Column A via Column B. Thank you in advance, and please let me know if I should explain further.

Hi! I’m going crazy trying to come up with an NPS formula. I am planning to have a formula return -100, 0, or 100 depending on the value so I can then average that column on dashboards. I can get my NPS just fine, but I can’t figure out how to exclude blank cells in an IF statement. In words, I want the following: If {Program NPS Value} is between 0 (inclusive) and 6 (inclusive), -100 If {Program NPS Value} is 7 or 8, 0 If {Program NPS Value} is 9 or 10, 100 If {Program NPS Value} is BLANK, “” There’s gotta be a way to factor out these blank cells. I’m getting -100s for all my blank cells with this formula: IF({NPS Value} < 7, -100, IF({NPS Value} > 8,100,0)) I appreciate your help!!

Airtable Not Working by anyone?

Hi there, Im looking for a bit of help with a formula. I’ll try and explain it as best I can… I have six fields as follows: 1st Payment Date (date type) (e.g 1st Jan 2020) 1st Payment Amount (Currency) (e.g £500) 2nd Payment Date (date type) (e.g 3rd Jan 2020) 2nd Payment Amount (Currency) (e.g £500) 3rd Payment Date (date type) (e.g 5th Jan 2020) 3rd Payment Amount (Currency) (e.g £500) What I would like to do is create another field with a formula that would basically sum all the payment dates which fit within a specific date range (an example date range relating to the fields above would be the 1st to the 31st Jan 2020) Would anyone be able to help with this? Thanks in advance!

Under “collaborators”, I am shown as the owner along with the message “This workspace also has 1 base collaborator not represented above”. No other collaborators are shown. How do I find out who is this other (hidden) base collaborator?

I have a bunch of different strings of text and want to remove different phrases, but I’m unsure of the formula. Here is an example of what it may contain: 123.jpg 234.eps 345.ai 546%20(1).eps I want to remove every instance of “.jpg”, “.eps”, “.ai” and “%20(1)” Is there a way to do so?