Formulas

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! Show Current Step, based on complete, date Formulas Hey team! I want to be able to show my “Current Step in Progress”, based on the information in my “Steps” table. How it should look: If there is an actual completion date, the f

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.

I understand that to have sum of column A and column B, I can use formula SUM(column A + column B). But If I want to add up 50 different columns into 51st column, I will have to use formula SUM( Column 1+ Column 2 + … + column 50). Is there a simpler way to do this?

hi, i’m trying to do a simple multiplication formula, Qty * Unit Price , but it come out #error , anyone can help me to solve this problem , thanks!!

I have a base providing the back-end of a ReactJS app. It has worked really well in production for multiple years. Now I have some tweaks to make to the base formulae BUT I’m scared of breaking them… “if it ain’t broke, don’t fix it”! So I’d like a version history on specific formulae, in case I need to roll back the changes I’ll be making. Base backups won’t suffice, since a roll-back means potentially losing data that users may have entered since that last version of the base. I need something more granular, allowing me to roll back single columns. So I’m curious: beyond manually copy-pasting the formulae out from Airtable UI into a code repository, is there any recommended version-control for Airtable formulae? Has anybody else already asked this and ended up going with the manual option? The affected columns number 15-20 but there’s only likely to be this onece-off edit, so the manual approach could serve well enough. But I suspect it will become a more relevant question as we star

Airtable Grid view - Airtable Explore the "Grid" view on Airtable. I need help in Formula IF to detect someone birthday dues in today : TODAY Formula : NOW() with European Format Column 1 Formula : DATETIME_PARSE(DATETIME_FORMAT(TLahir,‘MM/DD’)&"/"&YEAR(NOW())) column 2 Im Copy paste from : airtable Help (Recurring Date ) But when i put the Formula to detect Staff’s Birthday on Today : Column3 : IF(Formula=Today,‘TODAY’,‘WRONG’) The RESULT is always WRONG . why is that ?

Happy Friday, Airtable community! I’ve got a formula that works, but only sometimes. Its purpose is to cut news sources down to just their names, no taglines, no section headings. While it’s a long formula, the sources within it are in alphabetical order so it shouldn’t be too hard to find the ones that are giving me trouble, namely: Al Jazeera, Literary Hub, Mother Jones, Open Culture, Rolling Stone, Slate, Teen Vogue, The A.V. Club, The Guardian, The Intercept, The New York Review of Books, The Progressive, VegNews, and Washington Post. Hoping someone can see a pattern. Here’s the formula: IF({Source}= “action’s Email Updates - Tracked Events from GovTrack.us”, “GovTrack”, IF({Source}= “Al Jazeera - Breaking News, World News and Video from Al Jazeera”, “Al Jazeera”, IF({Source}= “Animal welfare | The Guardian”, “The Guardian”, IF({Source}= “AP News - AP Top News”, “AP News”, IF({Source}= “Art and design | The Guardian”, “The Guardian”, IF({Source}= “Article – The Nation”, “The Nation

Hi All, New user (doing pro-bono work for a small edu business) so not too familiar with this but have read all the ‘form’, ‘pre-fill’ and ‘button’ articles here and around 60+ community topics to try to work this out. Teachers giving feedback/assessments on the students in their course. Students table, Courses table and the Assessments table linking them with all the questions etc Because of Airtable pricing, I need the teachers to use a Form to enter the assessments and I’m looking to automate the ‘listing of the students in their course’. So, ideally a picklist from the student names in the record cell. I have a form where they add the Course-ID and then enter the Student Name. With 10 students in a course they have to do this same step 10 times so I’d like to prefill. I can do the course ID ok but not do the Student name (because it’s multiple names). Q: I’m now trying to use a button to open the form prefilled but would be happy with the button opening 10 forms at the same time

Can anyone help provide a solution to this formula? I’ve tried it with ABS and IF. (2*{width}) + (2*{height})

I am including a currency field in a text formula. How do I get the string to show the currency formatted properly? Example: The currency formula field shows: $116.67, but when I include that field in a text string it shows up as 116.66666666666667. Of course, I’d like to look as it does in the currency formula. Thanks for any help!

Hello. Not sure if I have chosen the right forum (formulas) but here’s hoping someone can help me. I work for a charity that holds a number of different training sessions with the local community. We use a standardised survey for gathering feedback for all the different sessions with Survey Monkey, and then use Integromat to import the results into the table you see below. The results are then grouped by the training session that respondents indicted that they attended in the survey in order to make them comparable to some degree. Each quarter, we will filter responses from the last 3 months and then use the feedback to report on our progress. This information is for funders and obviously I can’t share with them a full set of survey responses. Therefore, I want to somehow turn this data into informative data at-a-glance without using the Charts block because that doesn’t display info in an appropriate way and it also can’t be downloaded. The core issue I am having is one of converting

Trying to automate a Field input based on the 2 previous field inputs. (In the same row) If (Field X = True) AND (Field Y = True) THEN (Field Z = True) (another example would be:) If (Field X = False) AND (Field Y = False) THEN (Field Z = False) Can this be done? Many thanks!

I’m not sure what the best way to describe this would be, but I hope this example table will get my question across. I am looking for a way to count up the number of Hat Tricks a Player gets automatically based on them having 3 or more Goals in a single Game. Each field of the same name is link across tables. The trouble I’m running into is that I’m limited to one field of reference (I need both Game and Player to be considered) when trying to use a Count function. Any help would be appreciated. !

Hi, I have a formula I am trying to get to work that I took from another forum post and it’s not working, not sure where the syntax is broken. I am trying to mark a single select status field good or bad if the date of a last modified field is great than a certain number of days. IE. If the date of the field “Date of last check” is older than 7 days, change the status field to Bad Can someone tell me what I’m missing? IF( AND( DATETIME_DIFF( TODAY(), {{Date of last check}}, ‘days’ ) >= 7, {Status} = “Bad” ), “Good” )