Formulas

Im not very good with formulas so I am looking for help. I log most of my business activity in an Airtable database so that at the end of the month I can invoice my customers. I found a formula here so that I can calculate and view weekly dollar value sales totals and it works great however there is a known quantity of work that I don’t log and I want to add that known amount to the calculated value displayed. To get the weekly sales value I have a Formula column with this calculation - DATETIME_FORMAT(Date,‘w’) and I have a view where I group by this column and it sums the amount column in my database.

I have a list of about 28 employees that I have created a scheduler for. I have their name, what team they are one, what shift they work and what work week we are currently on and other info. What I am wanting to do here is to have a formula that will automatically input their shift. Example: Jon works 8-5 M-F. So if I select him from my already existing record I would like his schedule to populate onto the selected work week and be added to the work calendar, then do this for all 28 employees so I am not manually entering that on Monday they worked 8-5, Tuesday 8-5, Wednesday 8-5 etc. Also is there a way for AirTable to recognize what week we are in and automatically populate that into the Grid I have created for their schedules? Any help is greatly appreciated!! Thank you all for your time.

My client just started using Airtable and she shared her airtable calendar with me, but the drag and drop feature for me to add attachments/documents to tasks is not available. Does she need to change something in her settings to grant me access to edit the tasks? There is also no option to manually add attachments Also, she is on mac and I am on PC - we both are running updated programs, but when she uploads .docs for me to download, some of the images (emojis) are not there. instead, its the box that indicates something was there but did not work with the formatting. I tried to provide screenshots for this post but it says images are not allowed. We don’t have this problem when we share documents on other platforms like word, dropbox etc.

i create one base that showing income -expenses-profit every thing is good now i have one problem, i create one field name is bank balance.i put formula in field that( total bank balance(30000) + Profit),problem is now this field ,every row doing - from 30000 i want every row should make - from last field row

Hi, I am interested in using a conditional formula to either automatically select from a single drop down or to use a phrase with emojis. {Program Status} In this field I want to indicate in one field if a program is: In Progress Complete Future {Program Start} and {Program End} or {Program Start and End} I have a field for start date and a field for end date (or I can create one field and type a range) I am looking for a formula that will automatically generate a {Program Status} based on today’s date. I would appreciate any help. Thank you!

Hi! I’m trying to use a formula or count to use in my base. I have in a board all the information for each of the employees and in another table I have the departments and I want to add how many women or men I have in each department. How can I count them with a formula?

Need a little help here :slightly_smiling_face: What i’m after is: If code is ER and Order Received is after 29th July 2019, value is 21 else value is 17.5. The order received field contains date and time like: 29 July 2019 11:01am IF(AND({Code}='ER',IS_AFTER({Order Received},"29 July 2019 12:00am"),21,17.5))

Is it possible to set something like this up and if so, how? If the answer in Column 1 is X, then display these multiple choice options for Column 2 If the answer in Column 1 is Y, then display these totally different multiple choice options for Column 2 I’m a brand new user, so please account for that in any answers. Thanks!

I’m using the following formula to check whether the data in another column (Call Forward) is before today. If yes, then enter today’s date, if no then enter the Call Forward date. IF(IS_BEFORE({Call Forward},TODAY()),TODAY(),{Call Forward}) I’m getting an error where the Call Forward field is blank which, I suspect, is caused by the blank (if blank I want today’s date). I’m guessing I need and OR statement to check for blanks but cannot find a suitable formula to check for blanks. I’ve tried using BLANK() but still getting an error: IF(OR(BLANK({Call Forward}),IS_BEFORE({Call Forward},TODAY())),TODAY(),{Call Forward}) Grateful for any advice.

Hey All, I have a formula below that checks if a rental is currently Active. I would like it to include bookings that are finishing today also as an “Active Rental :blue_car: ” ( and also bookings that start today). Is there a way to do that (or a better way to construct this formula? My formula is below; IF( IS_AFTER( {Date Started}, TODAY() ), “Upcoming :calendar: ”, IF( IS_AFTER( {Date Ending}, TODAY() ), “Active Rental :blue_car: ”, “Completed :white_check_mark: ” ) ) Thanks for any help. Cheers, Harry

I need a formula that will automatically fill in a draft date that comes 5 days before the due date AND has to land on a weekday. So far I have, IF({Project Size}=“Large”, DATEADD({Activity/Due Date},-5,‘d’)). This almost works for me but say I have a due date of 8/1 and I need the draft submitted for review no later than 7/26 (for a 5 workday review) how do I write the formula to land on that date and move dates according to due dates that follow?

Trying to get the “)” to display after the “X” in COMX (({SchDep} & “-”) & {Dest} & “-”) & {LdTo} & " (" & {SCAC} results in: 2200-KLO-KLO (COMX but (({SchDep} & “-”) & {Dest} & “-”) & {LdTo} & " (" & {SCAC} & “)”) results in a formula error. I’m also unable to display the date from a date field as part of the formula. It ends up looking like 2019-17-26-T00:00:00.000Z Also, is it possible to formulate two linked records {A} and {B} to display in one cell with the above?

Hi there, I am looking to setup an approval queue for our membership service, whereby applications are brought into the “Applicants” base, and when the dropdown selector for “Approval Status” is set to “Approved”, the record is added to our “Members” base, and deleted from Applicants base. Is this doable? Any idea how I could achieve this? Thanks so much! Adam

So I am attempting to use the api (via the npm client) to find records that are now or in the past, then perform an action, and mark them as complete (mark completed column as true). Select is as follows: view: 'Scheduled', filterByFormula: `DATETIME_DIFF({Game Start}, NOW(), 'minutes') <= 0`, The formula works fine for records in the past, but not for when the times are equal. For instance, if it is 8:25am and the ‘Game Start’ is also 8:25am, it is not coming back as a result, but when it is ran at 8:30am, it does return as a result. Not sure if it’s a timezone difference, formula issues, or what. Any help would greatly be appreciated. Thanks!

i create one table for employee .in that i create one filed passport expiry.i want that one more field should shoe that if expiry date is gone.filed show some message

Trying to achieve an outcome where: IF Column One is prefixed 381 then Apple If column one is prefixed 382 then orange etc Column 1 381-9287 382-2818 383-2812 Column 2 formula Thanks!

Hello Everybody, Firstly thank you so much for any help in advance. I am trying to mimic a formula that we have in G Sheets where we highlight any new instances of an email address (customer) in a list (but only the first & not any repeating instances) so we can see if the customer is a new customer or if the customer is a repeat customer. I have hit a wall with trying to figure it out myself. With my current formula, a “new” customer record will switch to an “existing” customer row when the customer buys from us again (as it is no longer unique & has more than one instance in the column) My current AT formula (totally incorrect I know) IF(ARRAYUNIQUE({Renter Email (ROOT - LINK)}), “NEW”, “Existing”) My GSheets formula that I am trying to replicate in AT. =if(isna(match(E3787,$E$1:$E3786,0)),“New”,“Existing”) Thank you so much for any help again. Cheers Harry

Hi all, I’m using Airtable as a reservations automation platform for my short-term rentals. In 1 table (Bookings) i have all the bookings with their relevant information (check-in, check-out, # of guests, …etc). This table is linked to a second table called “Guests”, which summarises everything for this guest, including the booking this guest has made, unique phone numbers, unique emails, how much they’ve spent so far…etc In the “Guests” table, i am trying to create a field which counts the number of unique reservations (based on check-in date), this guest has made. I currently have the following set up in the “Guests” table: Right now, the formula in “Returning guest” is showing “Returning” if the “# of reservations” field is larger than 1. This is not completely accurate because a guest can have multiple reservations on the same day but for multiple properties, which does not make him a Returning guest. Ideally i would like to have the formula compare the number of dates in “All che

Hey Everyone, I’m pretty new with Airtable and really enjoyed the freatures that it gives the user. I’m creating a Data-Sheet that basically manages Leads and also outputs them to Asana, Now my question was if there is a possibilty to generate a Text (Name of an Employee to Handle the Case) in a seperate cell based on a Number in a cell (Number of Teams - Prioritized) and a Postcode in another cell (The Data in the Sheet Currently has 5-6 Characters, all numbers) The thing is we seperate Tasks based on the first 2 characters of the postcode to our Employees. Do you see any Possibility to get this to work? Thank you in Advance :grinning_face_with_big_eyes:

Hi, How do I create a column or what is the formula that will automatically add the date based on a single selection in a previous column? For the date, I just need Month / Day / Year, not time. Example: Column A single select choices are: New / Pending / Closed. Column B is the date when I select Column A- NEW, Column C is the date when I change Column A from NEW to PENDING, Column D is the date when I changed Column A from NEW or PENDING to CLOSED. (I do want to be able to see all three dates which is why I’m using Columns B, C and D vs. have the date change in Column B.) Thanks, Pat Barron

I’m hoping someone can help here… I have a base with two tables (Initiatives and Tasks). Each Initiative is linked to one or more Tasks and each Task has a State field (single-select [Backlog | In Progress | Complete]). What I would like to do is create a separate State field on the Initiative record that is driven by the State of the related Tasks. Something like this… If all Task State values are “Complete” then make the Initiative State = “Complete” If one or more of the Task State values is not "Complete then make the Initiative State = “Not Complete” Any suggestions would be greatly appreciated

Hey there, Trying to setting up an Airtable Base to manage my product Inventory. I sell and install device to irriguate fields, each device have a default quantity of piece that I use for earch order. To help My employees I’d like to create Packing List stored by date and client with Airtable. I managed to do so but only by referencing each piece at a time (one device got many different piece). To prevent errors i’d like to create a categorie where I can set all piece and default quantity for one Device Then on my Packing List simply add One device to pack and all the different piece will be added to the packing list with their default quantity at once. I didnt see anything to help me with customed Group where I can add Piece and Quantity to display every with one click. Thanks for helping,

I have a date and a turnaround time. Need to calculate start date = date - turnaround time. The problem is date is date format and turnaround time is a number like 28. Is there any way to convert a date to a number?

Hey guys, I am wondering how I would go about importing text from another field like “Diamond Plus” and translating it into “diamond-plus”? Any way to do this without just building a really long IF statement? Thanks so much, Adam

I’m building something for my client wherein they choose a single-select and it will show results for 6 total fields based off that selection: i.e. If x = selection1, then 1 = A, 2 = B, 3 = C, 4 = D, 5 = E, 6 = F If x = selection 2, then 1 = B, 2 = C, 3 = D, 4 = E, 5 = F, 6 = G If x = selection 3, then 1 = C, 2 = D, 3 = E, 4 = F, 5 = G, 6 = H I’ve included screenshots and my formulas below and I cannot figure out WHY “Round 3” shows blank when I’m like 100% positive that the formula is correct. Please help! Formula 1 for “Round 1”: IF(T({Choose Item Type & Round}) = “charge-offDOLA”, “remove this account due to the fact that there are different dates last active reported. How can there be different dates when this is what stipulates when the account naturally falls off? You can’t report different months and different years!”, IF(T({Choose Item Type & Round}) = “charge-offDOLP”, “date last paid shows mutliple dates and this makes it look like I paid when I didn’t and didn’t wh