Formulas

hello everyone! i have this table that clocks in and clocks out people at work, but i would like that it would subtract 30 minutes(lunchbreak) so the person would clock-in then later clock-out and at clockout would subtract -30mins this is what i currently have: Přihlášen = clockin in Odhlásit = clock out . IF( AND(NOT({Přihlášen}), NOT({Odhlásit})), “0”, IF( {Odhlásit}, ROUND( DATETIME_DIFF({Odhlásit}, {Přihlášen}, ‘hours’), 60 ) & " hrs " & ROUND( MOD( DATETIME_DIFF({Odhlásit}, {Přihlášen}, ‘minutes’), 60 ), 0 ) & " min" , ROUND( DATETIME_DIFF( NOW(), {Přihlášen}, ‘hours’), 0) & " hrs " & ROUND( MOD( DATETIME_DIFF( NOW(), {Přihlášen}, ‘minutes’), 60), 0) & " min" ) ) any help would be apreciated, thank you.

Hi all, I’m trying to create a formula in a field that will take the value from one of two fields, whichever is not empty. One field is a multi-select, and the other a lookup. In essence this is quite simple for the multi-select field and the rollup field records (that are standalone strings), given the following formula: IF({Multi-select field}, {Multi-select field}) & IF({Rollup field},{Rollup field}) However, if a primary key in this table is linked to more than one linked record that the rollup field is referencing, then the rollup field outputs each of their associated rollup records separated by a comma. Thus, if the same above formula applies, I get an output this removes the comma and essentially combines all these rollup values into one long string. To address this, and to copy exactly how the values in the rollup field look (no comma removal), I nested a substitute formula to add any commas back in for when my second IF s

I am exporting events from Airtable base into a web calendar via CSV. The web calendar requires that date and time be imported as separate fields. To create a “date-only” field in Airtable, I simply duplicated the Airtable Date/Time field and turned off time. Done. Now I need a way to create a separate “time-only” field in Airtable. Can someone recommend a formula that extracts the time from an Airtable Date/Time field? Converting it to text would be fine. For example, converting “4/14/2019 1:00pm” to “1:00pm”. Thanks for your help

I’m a bit of a newbie and my apologies if this covered elsewhere, I just can’t seem to find the right answer. I have this formula below to calculate the total weight/value based on calculations below. I am trying to round up the total to the next 1.5kg because the product comes in tins of 1.5kg. So I need to calculate: IF(Fumigant=“FUMITOXIN (ALP)”,ROUNDUP(({H1 vol}Dosage3.0/1000/1.5)*1.5))) H1 Volume= 5314 * Dosage=1.0 3.0/1000/1.51.5 at the moment it’s equaling 16kg and it should be 16.5 How do i get it to display the .5? Formatting has been set to Decimal 1.00 16.5kg / 1.5kg = 11 TINS I hope I’m making sense

Hi Friends I have date data that is being presented to me in a YYYYMMDD format. I am putting that in one column. I would like to create a formula column to convert that unstructured date column to a proper date format. Can anyone suggest the composition of that formula. I have tried a few things but nothing is working.

Hi there I’d like to use the datetime_diff() function to compare a date in the {Date} column of each record to a fixed date: 29 March 2019. I can’t seem to figure out the syntax to insert that fixed date into the formula: datetime_diff({Date}, [what syntax to make 2019-03-29 a datetime?], ‘days’} Thanks Travis

How would I subtract a duration from the total number of hours in the day? For example, if I have a task that takes 6 hours, I’d like to set up a field that shows how many hours are left in the work day. I’ve tried a few things but keep getting errors. Thanks!

Hello Everyone, I’m very new with AirTable and I have been struggling with this formula. I have these 3 different columns: 1) Lead Count, 2) Opportunity Count 3) Total Amount ($). I’m creating a formula to select the Account Status based on these three columns in this way: If Lead Count = 0 --> “Cold” If Lead Count = 1 --> “Aware” If Lead Count >1 --> “Qualified” OR If Opportunity Count >0 --> Opportunity (regardless of Lead Count) If Total Amount ($) > 0 --> Customer (regardless of anything else) How can I put this into a formula?

I have been attempting to copy and paste a design into an email. It seems to be working beautifully, but it goes to the top of the email and will not move. I know this is not the way this is intended to work, but I am trying to duplicate and replace a portion of our workflow, where we copy and paste promotional info straight into an email and send. Again, it looks like it may work, but there seems to be some kind of coding that take it straight to the top (and in front of) the page and will not move. Does anyone know how to stop this from happening? I’ve tried it in various email apps and they all do the same. Please advise.

Hello Airtablers, I am new to Airtable and am working through connecting the base I designed to my Gmail account. I’m currently trying to determine how to write a search formula that will identify if the subject of an email contains a Contact Name. (This is as a part of the “Find or Create Record” zap.) So what I’m going for is, if I receive an email with subject “Hello from Jane Doe!”, when Zapier runs that against existing Contact Names, they ID that yes, in fact, I do already have a record for Jane Doe, or no, I don’t have a record for Jane Doe, and one is automatically created. Currently, the Search By Field and Search Value fields result in the creation of a “Hello from Jane Doe!” record, which I expected. The problem is that, as a novice, I can’t seem to identify the function(s) or operator(s) that might get me the result I’m going for. The good news is my workplace is a small one, so not finding a solution won’t be the end of the world. The bad new

Hi! I have a base where I register exam questions. I class them by Subject (linked table field); I would like a formula that enumerates them by subject, for example: Pharmacology 1 Pharmacology 2 Development 1 …and so forth. Can it be done? Thanks in advance!

Hi, Here’s the scenario: I have a row with a product. I want an auto generated number to assign to that product. I want to then duplicate that row, but know that it is a duplicate of another row. So - Product 001.1 gets duplicated, I want the duplicate to then show Product 001.2 If I create a new product (not duplicating), then that product would be Product 002.1, and so on. Any ideas?

Here’s the idea: I want to automatically fill a record’s Primary Field in TableA with a title that is a combination of the “Guest Name” from the same table AND the “Show Code” from TableB, a different table in the same base. However, the “Show Code” from TableB should be automatically determined by the “Show Name” also in TableB that is referenced in TableA’s linked field. I feel like there most be an If, and, then something or other in there. EXAMPLE FIELDS: TableB Fields SHOW NAME - “Command of Desserts” SHOW CODE - “COD” TableA Fields: PRIMARY FIELD - “COD: Harry Morgan” SHOW NAME - Linked to TableB’s Show Name Please help me clarify my convoluted question and thanks for ignoring my ignorance . :grinning_face_with_sweat: Best, Chris

I’m trying to figure out how to create a column that lists the last date that a particular person was contacted. Right now, I have two tables: one is a list of contacts and another is a list of messages to that contact (with dates). I created a lookup table that populates a field “last contacted” by the time stamp on the message associated with that person as lookup to a linked field. Here’s the problem: the field lists every single date that the person was contacted, not the last time the person was contacted. How would I filter the column to only show the most recent date? Or, some other work around that only takes the most recent time stamp associated with a linked record? thanks

So I am trying to create a formula that would help me out w/ some grammar issues in my Page Designer. Basically, if there is a contact in the secondary contact field named Gina Malone, I want the function to populate as “and Gina.” If the secondary contact field is blank I want the function field to remain blank. The below function does a great job of producing what I need when there is a contact in the secondary contact field, but always populates as “and” when secondary contact is blank. IF({Secondary Contact}!=BLANK(), "and “) & LEFT({Secondary Contact}, SEARCH(” ", {Secondary Contact})-1)

I am trying to do a date diff formula and i need help. Lets say it has to do with aging. Products a for a 1 week, product b for 2, etc. If I have product type in column “Product” and start date in “last active”. I need “aged” column to look at “product” to get the type know how many weeks it needs to age and do a date diff from “last active” to tell me either “within min time” or “time met”. Any ideas?

Hello, I would like to make a formula that does this: Display 1 if the item “lightyellow” was selected from the Color multiselect I see that I can use the SEARCH or FILTER functions. However, these will also evaluate to true if a non-exact-matching option was selected. For example, let’s say I have the options “yellow” and “lightyellow”. The following formula will undesirably match items that have “lightyellow” selected. SEARCH(“yellow”, {Color}) Is there nothing like this? CONTAINS(“yellow”, {Color})

Hello! What I need: For a field to update automatically depending on the number of days remaining in a clients account. For example, if the account ends at the end of the month 4/30 - I would want a “Renewals” field to say NEEDS RENEWAL in Red if the date is less than 14 days away. What I have done: I cannot figure out how to write this formula within one field. So what I have done is set up another field that puts the function - DATETIME_DIFF({End},TODAY(),‘days’) which displays the number of days until the account needs to be renewed, and then in the Renewal field, I put the statement IF({Renewal Date Parse}>14, “”, “NEEDS RENEWAL”). I would like to know if I have over complicated this and if it could be put within one field to decrease redundancy. Here is an image for concept. Thanks so much!

Hello, I am new to Airtable (AT) and I am evaluating it so as to become (or not) my mainstream application. One of the features I desperately need (and I can’t find it) is the vertical summation of records. It is a pity that the AT provides a summation indicator at the bottom ribbon of each field but this cannot be accessed or used in any way. Am I missing something? Can this be done? The second question refers to dynamic linking of fields. I want to link an entire field (with how many records it may have, i.e. linked dynamically) because there is no way I can add them manually one by one with the plus sign (they are just too many records and the number of records in the field can change any time). Is there a way around this?

I tried to post this yesterday in much more detail… but got an alert saying the post had to be approved? Not sure why, that’s never happened to me before, and it hasn’t been approved over 24 hours later… so let’s try this again! Background: I’m building a base for employee clothing orders. Based an employee’s title, they get a certain allotment of clothing. If they want to get more than their allotted amount, they are responsible for paying for the overrun. Let’s say a manager is allotted 5 sweatshirts. If the manager wants to get 7 sweatshirts, they are responsible for paying for the extra 2, and we will pay for the 5 most expensive items. What makes this tricky, is that larger sizes have an associated upcharge. Say $2.50 for an XXL, $3.50 for a 3XL, and so on. Table set up: [Clothing Standards] holds each title and their associated allotments for each type of clothing. [Items] holds all clothing items, sizes, and prices. [Clothing Orders] li

Hi there! I’m trying to create a base to track employee clothing orders. Each employee, based on their title, gets a certain allotment of several types of clothing. For this example, say a manager is allotted 5 sweatshirts. If that manager wants to get 7 sweatshirts, they certainly can, but they would be responsible for paying for the extra two. Here’s where it gets tricky… If all sized sweatshirts were the same price, this would be pretty straight forward, but they aren’t. All sweatshirts over an XL have an upcharge (XXL adds $2.50, 3XL adds $3.50, and so on). I want to determine how much the manager would owe if we pay for the 5 most expensive items. Say the 7 values of the sweatshirts the manager chose are in an array… can I look at that array and only sum the 2 lowest value? Table set up: [Clothing Standards] determines what title gets what allotment [Items] holds all Clothing items, sizes, and prices [Clothing Orders] links to b

I have 2 date fields. A ‘Book in’ and a ‘Book out’ that logs the date and time someone shows up to work and leaves. Then I use DATETIME_DIFF({Book out}, {Book in}) to get the total hours worked. Is there a way to also calculate over hours. If working past 6pm then anything after 6 would calculate as over hours?

Hello Everyone, I am trying to apply some logic to some of my fields that get automatically filled by a web hook API by Zapier. For instance, when one of our staff submits a form, the data will be sent to Airtable and then a sequence of actions will happen. My question is this: how can I get a value of one field to change the parameters of another? I.e. if field 1 labeled “Service Type” contains Google Ads, I want field 2 labeled “Team Group” to automatically assign a tag called “Search Team”. Basically, if “Google Ads” then “Search Team”. I just don’t know how to correlate this formula because there are multiple selections for field 1 that would all need different ‘answers’ from field 2… if that makes sense. Please let me know if you can help my situation! Best M

I’m having problems suppressing extraneous comma delimiters when trying to combine multiple fields into a single column of values separated by commas, but showing no comma if any of the combined fields are blank. I tried various combinations of this formula to no avail. IF({Racer 1 Full Name},{Racer 1 Full Name})&IF({Racer 2 Full Name},", "&{Racer 2 Full Name})&IF({Racer 3 Full Name},", "&{Racer 3 Full Name})&IF({Racer 4 Full Name},", "&{Racer 4 Full Name})&IF({Racer 5 Full Name},", "&{Racer 5 Full Name}) The above formula yields the following output: The above example is trying to place 1 to 5 names in a single field separated by commas, if the name field being combined is blank, there should be no comma separator. Note, there is always a Racer 1 name so no need to suppress on that field, j

I got a field that summarises some numbers with some text. One of the numbers needs to be rounded to a with 3 decimals so I wrap the number in ROUND(), but it only gives me a precission of 1. 'Total weight: ’ & ROUND({Total Weight},3) & ‘kg’ Gives me: ‘Total weight: 0.8kg’ But looking to get: ‘Total weight: 0.800kg’ What an I not getting?