Formulas

Hello! Trying to figure out a formula to compute for hours worked between two dates/time given a condition. The formula only needs to compute for hours worked between 10:00PM - 6:00AM. Here are the scenarios: Scenario 1 Time in: May 4, 2021 4:00AM Time out: May 4, 2021 1:00PM Hours worked: 2 hours Scenario 2 Time in: May 3, 2021 11:00PM Time out: May 4, 2021 8:00AM Hours worked: 7 hours Trying to compute for “night differential” as part of our payroll. Hoping someone can help or lead me towards the right direction. Thank you!

Hi everyone! Thank you in advance for taking a look at this. I have a formula that I think is REALLY CLOSE but keeps giving me an error. The field I’m having the formula search contains a series of state abbreviations, so MI may or may not be present in the string, and I want to differentiate between MI not being present (but other states being present) and the cell being blank. The formula should work a little something like this: IF (MI is present, then “SLP”), IF (MI is not present, then “Applying”), IF(BLANK(), “Candidate”). Here’s what I have so far. Each half works by itself, but when I put them together they always give me an error. IF({SLPs Licenses (from SLP Onboarding Link)}=BLANK(), “Candidate”, IF( FIND(“MI”,{SLPs Licenses (from SLP Onboarding Link)}),“SLP”, "Applying”))

Hi, I need it to work out, IF “Recycled” is “Weekly”, make the date in “Next Post” 7 days after “Post Date”. IF “Recycled” is “Every Two Weeks”, make the date in “Next Post” 14 days after “Post Date”. IF “Recycled” is “Every Three Weeks”, make the date in “Next Post” 21 days after “Post Date”. IF “Recycled” is “Blank”, leave “Next Post” blank. This is the formula I am trying but it is returning an error: SWITCH({Recycle}, “Weekly”, DATEADD({Post Date}, 7, ‘days’), “Every Two Weeks”, DATEADD({Post Date}, 14, ‘days’), “Every Three Weeks”, DATEADD({Post Date}, 21, ‘days’), BLANK()) Can anyone see what I’m doing wrong?

Currently trying to create a formula that shows the count of a record. (i.e. first record will have a value of 1, second record linked to the same data will have a value of 2) See attached picture for ideal / expected output: Is there a formula you might suggest to achieve this? (The red-colored numbers is what I want)

Hello good people of airtable! I would like to know how to: add two percentages together, then minus that percentage from an amount? to get the sum of that calculation in this case- FEE (£40) - (CUT (10%) + TAX (20%) = 30%) = PROFIT (£28)

So im trying to calculate days that are left for a project to end. Im using DATETIME_DIFF({End Date}, TODAY(), ‘days’) but some ended projects show -2 (the ones that ended 2 days ago) how can I make them just stay at 0? Maybe an if condition. But Cant seem to find it. Thanks!

Hello! I have a situation that I can’t seem to find the best solution to. I have a reference field that needs to automatically enter the data from the first field of 4 that is not blank in a priority order. I am able to get two of the 4 fields with a simple nested IF - but I’ll still end up with blank data in the reference field. Was thinking of trying SWITCH but the text in each of the reference fields is unique. Is there a way to accomplish this via formulas? My last ditch effort would be to create two separate fields that use the nested IF statement, then use the reference field to have the same nested IF statement for the two fields created. Which isn’t very elegant at all. Thanks for the help.

I have a field in Airtable with L x W x H listed in one cell. The information follows the same format every time. There is a space followed by an x followed by another space. I would like to pull each value into it’s own cell. I know it would be easier to go the other way around, but we already have a lot of data in this format and it’d be easier for the team entering the information to continue their process. Can someone help me write a formula to get these 3 values into their own fields?

Thank you for your help. I’m sorry for the beginner’s question, but please tell me. I want to make a cash register. I made it based on the idea of Excel, but could you tell me if it is feasible? ■ 入金 - 出金 = 残高 →This was achieved with the formula ■ 残高(upper record) + 入金 - 出金 = 残高 → How should this formula be realized? Or could you please tell me if you need to review the table design? Thank you.

Trying to create a filter in integromat to search records for between todays date and 10 days in the future. Can’t figure it out… Just playing around with some basic stuff I am getting weird returns NOT({Start Date}<‘2017/11/15’) That returned everything in 2018 and 2019 but did not factor in the month to include records greater than Nov 15 2017 like some that were Nov 25th 2017 and Dec 3 2017 Plan is to use the Date now merge tag in integromat and then date now+10 and bring back everything between today and 10 days from now so we can automate an events newsletter weekly only sending the cooresponding events that are happening in the next 10 days. Appreciate any help…

Slightly different from the other many birthday calculations. I have AGE(date of record opened) and need to calculate AGE(Current). I do NOT have a birthdate and can’t get it. How do I calculate - DateCreated - XX(years) = YOB Which would allow me to then use - DATETIME_DIFF(TODAY(), {YOB}, ‘years’)

I’ve been checking around the community for a workaround for this issue. Sooo… here’s my dilemma… I have four columns: An item could be like a box of pens, while the pens would be the unit. I’m trying to find the percentage of actual Units in Stock as compared to the Total Units by a percentage. I checked to make sure all columns are integers. I tried it two ways: {Total Units in Stock}/{Total Units} {Total Units in Stock}/({Units/Item}*{Items in Stock}) Total Units = {Units/Item}*{Items in Stock} PS. I tried to change all columns to decimals as well with no luck

Hi, what formula can I use to show many days remain for a task? For example, if there are 4 days remaining before the Due Date, I’d like that cell to read “4” I tried using a formula from another table but it didn’t work. The existing formula was: IF({Name (from Due Date)}, DATETIME_DIFF({Name (from Due Date)}, TODAY(), ‘days’)) My new formula is: IF({Follow Up Date}), DATETIME_DIFF({Follow Up Date}), TODAY(), ‘days’)) - but this one doesn’t seem to function correctly. Any help is greatly appreciated. Thanks! Christopher

I want to combine a linked field containing a job title with each item in a multiple select field, which contains different statuses and sub-statuses in the application process. Ultimately, I need to be able to keep track of each status that a candidate has ever been in. I’m using the simple formula below: {Job Title} & "_" & Status But it seems like Airtable treats the different items in the multiple select field as an array. The formula produces the job title followed by a string of each of the statuses separated by a comma. I’d like for it to produce the different combinations - Job F_1.a, Job F_2.a. Does anyone know how to do this?

Hello all, Wondering if anybody can suggest efficient way to extract text and put into fields. I was going to play with LEN and SUBSTITUTE formulas but I know it will take me a day or two to figure it out because I don’t really understand well how the two formulas work. I have used them successfully to extract Airtable attachment links to separate fields as URLs and it only worked because some genius here came with the suggestion. All I try to do is to go to Amazon, copy the description, I would past that into a field and then the formulas would grab it and populate the individual fields. Specifically I am grabbing this information (PRODUCT INFORMATION - it’s at the end of description in Amazon product page, just before the Customer Questions & Answers section). This is what I need to copy: Product Dimensions 3.54 x 2.24 x 0.47 inches Item Weight 0.353 ounces ASIN B072K8BMST Item model number CardH-BlackWP ==== The formatting doesn’t show up when past in Airtable.

3 lookup fields “30 Min Call”, “Proposal Sent” and “Contract Signed” 1 result field “Status”, Formula needed IF “30 Min Call” has a date, enter “Prospect” in “Status” BUT IF “Proposal Sent” has a date, enter “Onboarding” in “Status” BUT IF “Contract Signed” has a date, enter “Client” in “Status” IF all three fields are EMPTY, leave “Status” blank Likely scenario would be when “Status” shows “Client” then all 3 lookup fields have dates (but is not a requirement). I’m pretty sure I am missing one simple thing but I have tried all kinds of strings and tried comparing in the help functions but still getting error results. Thanks for the help.

お世話になります。 初心者の質問で申し訳ないですが教えて下さい。 出納帳を作りたいです。 エクセルの考え方を基に作っているのですが、実現可能か教えて頂けまいでしょうか。 ■入金列ー出金列＝残高 →これはフォーミュラで実現できました。 ■残高列（一つ上のレコード）＋入金列ー出金列＝残高 →この式はどう実現したら良いのでしょうか。 もしくはテーブル設計を見直す必要があるかご教授頂けないでしょうか。 よろしくお願いいたします。

Hello! In our table we have multiple checkbox fields that we would like to count/sum if they are checked (in a Total formula field). In our base, there are about five fields that can be checked to indicate that a participant received that credential (it is unchecked if they did not). What’s the formula we can use to count/sum the checked boxes across these five fields for the Total field?

I have a multiple select field {Services} with entries like: CIQ COR SKED CNL DLA TECH STOP etc. Since it is multiple select, they can be combined in one record, so many combinations are possible. I want to use the SWITCH() function to apply to this field and produce a result depending on contents like: SWITCH( {Services}, MID({Services}, FIND(“CIQ”, Services), 3), “CIQ”, MID({Services}, FIND(“CNL”, Services), 3), “CNL”, MID({Services}, FIND(“COR SKED”, Services), 8), “COR”). Oddly, I receive error message which I can not find the reason of. Any help and ideas will be greatly appreciated.

Hi there, I really try my best to work out these formulas, but I have hit a wall now that I’m having to potentially combine AND’s and OR’s with my IF’s… I currently have 2 different fields (working fine) - but I am hoping to combine them (mainly so I have reduce number of fields and also use the 1 field in a roll up calculation. Below is ‘essentially’ what I am trying to achieve in one field, if anyone is able to provide some guidance on writing the below out correctly… IF {Status}= ‘Pre Live’ OR ‘Active’ AND {Sale Type}= ‘Traditional’, {List Price}*{Listing % Forecast after Buyer and Auction} IF {Status}= ‘Pre Live’ OR ‘Active’ AND {Sale Type}= ‘Auction’, {Reserve Price}*{Listing % Forecast after Buyer and Auction} IF {Status}= ‘Escrow’ , {Transaction Commission}*{Sold Price}

Hello ! I am trying to calculate my margins on my projects. All my costs appear in the “Costs” box. I would like to add up all these costs in the next cell “total costs”. Which dormule should I write? Then what formula should I write to subtract “TTC cell” - “Total Cost”? Thank you very much for your help, very much appreciated :slightly_smiling_face:

I’m trying to create a formula whereby : IF [date 1] is after [date 2] OR [date 3] is after [date 4] to tell me “Needs Review”, otherwise “Updated”. How do i do this? i’m not sure how to compare the date fields. Thanks!

Hi! I need assistance if possible! I have a table where I’m tracking information about a wave of people (table 1). I have another table where I have the names and individual information for those people. (table 2) I have a linked field connecting the names from table 2, to the wave on table 1. Part of the information on table 2 is a formula that rolls up from another table with possibilities of 0-51. I have a look up field bringing the numbers for each person into table 1 separated by commas and I can use Average() to get an average. BUT if there is no one linked, there are no numbers to average and it returns NaN. I would like it to either return 0 or stay blank. I have read previous articles but they mostly seem to focus on dates and I am using integers.

Hi Community, formulas seem like a bit of a dark art to me so I have no idea where to start, tried a few things but getting nowhere fast! I have what I think is a basic formula to write involving commission percentage amounts etc. So a Price, a percentage commission amount of the Price paid to me, the remainder paid to the third party. So for example, item is £1500 less 40%, £600 to me, £900 to third party. Percentage will be a variable. Looks pretty straightforward here but not sure how to implement in Airtable. I have attached a screen grab of the relevant fields. The first row has been manually entered to show what I want the formula to do! Any help greatly appreciated, thank you.

I’m trying to build in the ability to automatically flag order data in my Airtable CRM as one of three categories: “new” - first time ever order for this customer “retained” - the same customer placed an order last fiscal year too “recaptured” - same customer placed an order at some point in the past, but not in the previous year. I felt like I was pretty close to solving this when I used an uniquearray formula to create a field that combines all fiscal years in which an account has orders into one array, so that I can compare each individual order fiscal year to that array. However, since there’s no apparent way to intentionally sort that array from, say, earliest to latest or least to greatest, I have no way to reliably compare a given order’s fiscal year to the array. I feel like I’m doing a bad job explaining this, but maybe someone will know what I’m talking about.