Formulas

Hello, I need to create a formula to include permanent (fixed) class hours in our airtable school calendar :blush: . Classes are repeated on the same days at the same time , depending on education levels throughout the school year (from September 1, 2020 to July 5, 2021, eliminating the following school holidays: • From Saturday October 17 to Monday November 2 • From Saturday 19 December to Monday 4 January • From Saturday February 20 to Monday March 8 • From Saturday April 24 to Monday May 10 • From Thursday May 13 to Monday May 17

Hi, I would like my primary field to be autonumbered, starting with “PW10001”, then +1 value to each new record (so PW0002, PW0003, etc). I think I need a hidden Autonumber column to give me each new sequential number, but I’m stuck on how to string the formula together in the primary field to bolt the “PW10000” to that. Can anyone help?

Hi Guys, Having trouble finding out how to write a formula to check if one field contains another field’s data. The specific use case is data being pulled from an ecommerce site. Each order comes from a page dedicated to a suburb/postcode. Each order populates a record with this suburb/postcode in one field as well as the delivery address in another field. I am trying to create a formula that will check if the delivery address contains the same postcode in the other field (and if so label it “true”). Any help would be much appreciated! Thanks in advance!

First - I am pretty new to writing formulas. I know enough to get myself into trouble. :slightly_smiling_face: Here’s my problem … I have a table which is a listing of tasks that are assigned and have associated dated. The fields in this table includes: Date field that includes the time field (Start Time) I created an automation that would send an email when a particular person is added to a record (assigned a task). This email includes in the body of the email, the name of the task and the date and time that this task is scheduled to begin by inserting data from the Start Time field. The resulting email looked ok except that the data from the Start Time field was ugly (for lack of a proper technical term). So I learned how to convert the data from the Start Time field into text by creating two new Formula fields using the DATETIME_FORMAT function: Field name: Date STRING // Formula: DATETIME_FORMAT({Start Time},‘MM/DD/YYYY’) F

I’m a new Airtable user and still learning the ropes. I have a nested if statement that keeps getting kicked back as invalid and I cannot figure out what the problem is: Here’s what I need the formula to populate: IF{Appra Waived?} = ‘YES - Waived’, “N/A”, IF{Appra Waived?} = ‘NO - Needed’, then: a)IF{Appra Accepted By Due Date} = ‘NO’, then insert a date 4 days after {Appra Order Complete} not including weekends and holidays. b)IF{Appra Accepted By Due Date} = ‘YES’, then insert a date 2 days after {Appra Order Complete} not including weekends and holidays. Here’s the formula I wrote that does not work: IF({Appra Waived} = ‘YES - Waived’, “N/A”,IF({Appra Waived} = ‘NO - Needed’,IF({Appra Accepted By Due Date} = ‘NO’, DATETIME_FORMAT(WORKDAY({APPRA ORDER COMPLETE},4,2020-10-12,2020-11-11,2020-11-26,2020-12-25,2021-01-01,2021-01-18,2021-05-31,2021-07-05,2021-09-06,2021-11-11,2021-11-25,2021-12-24,2021-12-31’),IF({Appra Accepted By Due Date} = ‘YES’,

I am trying to get values that follow a defined string in incoming emails. Example: I have zapier setup to bring emails of interest in from UPS and I want to put the "tracking number in a new cell from that email. FIND("Tracking No.: ",{UPS Email})+11 what i get in the cell is 11 not the next 11 characters that I want.

Hi guys, I have a question about a formula regarding a URL. I want to have the domain without all www. etc before the domain and without the .com or whatever. LEFT( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”), IF( FIND("/", SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”))=0, LEN( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”)), FIND("/", SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”))-1)) My script is working on the left side and it gives me the URL: airtable.com, which is my goal. Now I want to delete the .com, what do I need to add? Thanks in advance!

If i have 2 tables one has rent of different location and in other if some one booked from to some location when from and to matches in both tables, in booking table give rent amount from rent table. Is it possible or any way to do this action and get result.

Hello everyone, for an automation with Zapier I would like to count in a formula field in Airtable how often the respective mail address already appears in this grid view. For example, if xxx@gmail.com is already two times in this view, then it should display 2 next to the mail address in the formula field. The email field is the primary field. Unfortunately I didn’t find a solution here in the community, I hope you can help me! Thanks a lot!

My linked table has 17,000 records. I want to look up the record I need but I only can look up one of these options: Keyword A Phrase (“keyword A keyword B keyword C”) What I want is I want to be able to look up “Keyword A Keyword Z” where keywords are not part of a phrase. In the picture below - I want to be able to find the entry for Baby Carriages & Buggies by searching keywords Home Buggies. Right now I have to either search Buggies or search Hearth or search the expression “Home & Hearth” or search expression “Baby Carriages & Buggies”. Are there some operators or wildcards I can use?

if ( {revenus ou dépense}=‘revenu’, LEFT({catégorie}, 5), {revenus ou dépense} ) i want my key field to display different things according to what is written in another field thanks girls and guys in advance

Creating a table that will be split into different views for different online marketplaces, as they each require different fields. The Status field at Abebooks corresponds to the Update Delete field at Amazon, though, so I’m making the Update Delete a formula field that converts the language like this: ‘’'SWITCH( {STATUS}, “For Sale”, " ", “On Hold”, “Delete”, “Sold”, “Delete” ) My question is how can I add a statement that will look at the last modified time field I’ve named Updated and return the value “Update” only if that Updated field has today’s date? So that I can get only the updated today records into their own view to be downloaded as a CSV files I can use for the online marketplaces? I feel like this is a thorny one, but this community has surprised me on multiple occasions so maybe I’m just too much of a newb to recognize real Airtable thorniness.

Hi, I have managed to fetch latest modified date to AirTable, to see when a task was completed - now i want to add a field displaying WHO finished the task - is that possible?

Hi All, I’d like to create a unique number for a record based on the year and week. So for example, I have a date field which I use as YEAR(Date)&" - "&WEEKNUM(Date). So if the date was today this would give me an output of 2020 - 43. Now, I want to add a counting number for each record with a date in that week in order of when the record was created. So the first record created with a date in that week would be “2020 - 43 - 1” and the second record created would be “2020 - 43 - 2” and so on and so forth. But when the week changes to the next week I want the counting number to start again, so it would then go “2020 - 44 - 1”, “2020 - 44 - 2” and so on. I just don’t know how to do this and would appreciate any help thanks.

Thanks for being here! I’m trying to calculate weekly sales as a percentage of monthly sales goals. I’ve made a conditional formula to populate sales goals according to month, and my formula is accepted but only returns errors. I’m also wondering if I can bypass the separate sales goals field and embed the formula into a calculation, i.e., can I say that IF {Month} is MM/YYY, {Current Month Sales}/‘XXXXXX’ If not, I’d like to just be able to calculate simple division, but that doesn’t seem to be working either. Heather

Hi, I need some help, I am counting the total hours worked of my employees, But I need a formula that won’t count the lunch time or that some how if table (lunch in) or (lunch out) is empty it won’t say NaN. some people take lunches, some don’t this is what I have it like this. I have it to count hours worked before lunch and after lunch and add that all up. Screen Shot 2020-10-19 at 1.25.01 PM|700x99

Hey there, I need to show the most current step in progress in my “Is Current Step?” formula as “1”. I’m running into an issue with my current formula because some of the Upcoming Due Date Lookup dates are the same. (Example below for the Content Quality Objective, where it looks like there are two current steps). Ideally the formula would say “Is Current Step?” = 1 when Actual Completion Date (per Objective) is empty. It’ll then look at the next upcoming date based on the Revised Completion Date. If those dates are the same, it’ll look at the Step # and it should be the next one, chronologically. FYI I need it to be “1” because my Objectives tab will show the current step based on the condition (below). Thank you in advance!

does anyone know a way to convert hexadecimal values to actual color names (or the nearest neighbour). Examples: #ffffff = white #eeeeee = light grey #45c1f6 = light blue

Hi, I am looking for help as i am struggling to work out why a nested formula will built isn’t working for one login but not the other within our tea, despite it being the same and all the tags, fields etc being mirrored. We are trying to categorise our database into - Gold, Silver, Bronze and have used the formula below which works for my analyst, but not for me. Can anyone help me understand why it isn’t working on mine IF(Category = “Gold”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>60,“Overdue”), IF(Category = “Silver”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>90,“Overdue”), IF(Category = “Platinum”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>30,“Overdue”)))) Note: We copied and past the forumla across, so not sure if that has had an impact

I would like a formula for this: When the status of a record is Project Complete for 3 workdays, change the status to Archive.

Bonjour, je cherche à indiquer un rang (de 1 à 10) dans un champs selon une notation d’un autre champs. Un peu type formule rang sur excel. Merci d’avance.

Very new to this so apologise if its a simple question. I have a field that is “cost”, a field that is “cost over-ride” and then a feild that is “actual cost”. What i want to do is to use the cost over-ride field but if blank use “cost”. Any tips will be much appreciated.

Can you pls tell me how to write a formular when I have a retail price in a colum and I need to have a colum that is 40% of the retail price.

Hi guys, I’m struggling to get this formula validated, which is odd as it’s been validated by an Excel master…? IF( {User status}=“New sign-up no story”, “Send new sign-up email”, IF( {User status}="New sign-up story in new”, “Send new story email”, IF( {User status}=“Sign-up no story”, “Send sign-up no story email”, IF( {User status}="Sign-up Story in new”, “Send new story email”, IF( {User status}=“Sign-up Story in draft”, “Send story in draft email”, IF( {User status}="Sign-up story in published”, “Send post-publication email”, “Nothing” ) ) ) ) ) )

Hi everyone, first day on Airtable. I am currently transitioning my spreadsheets from Excel. Basically, I have two records with the same field (Trip #) with unique data in each record. The unique data that i am trying to compare are the odometer readings and determine the exact miles that were driven from the pickup of record one to the delivery record for record two. I changed the Trip # to a date format in attempts to use the “is_same” function but it keeps returning the individual mileage for the each row and not an aggregated total of the odometer readings into the loaded miles field. Thank you in advance for any help or insight that you might have.