Formulas

I'm trying to score based on a percentage another field is calculating.If Profitability is greater than 0%, score 2.If Profitability is between -5% and 0%, score 1.If Profitability is less than -5%, score 0.I can get it to work for greater than 0% and for less than -5% but it won't score the percentages less than -5%. I've tried at least 4 variations on this and can't get it right. Does anyone know what I'm doing wrong?

Revenue = Quantity * Price7496.90 = 150 * 49.98Why is the number rounding up to 7497.00????Another Example: $205.80 = 25 * $8.23The value I get in my Revenue Cell is rounded down to $205.75.Here is the revenue formula vs. what it should be. The numbers are rounding weirdly.  

We are a publishing company and we publish individual pieces of content on multiple dates.  I have a different sheet that I'd like to have all of those dates be put together into 1 cell (see attached). Is there a way to do that so that if I fill in a date in the "Publish Date 1", "Publish Date 2", etc. cells, that it will automatically add them into the column on the far right that is referencing another sheet?  I did it manually in the attached screenshot just to show what I would like to happen. Thanks in advance!

Hi I have two fields ie a created time field and a formula field for the time field. I have an issue with time displayed in the formula field. It seems to convert the time I don't know why,, here is the formula: IF( {Posting Date} > NOW(), "Posted: Timestamp is in the future", "Posted " & IF( DATETIME_DIFF(NOW(), {Posting Date}, 'seconds') < 60, DATETIME_DIFF(NOW(), {Posting Date}, 'seconds') & " seconds ago", IF( DATETIME_DIFF(NOW(), {Posting Date}, 'minutes') < 60, DATETIME_DIFF(NOW(), {Posting Date}, 'minutes') & IF(DATETIME_DIFF(NOW(), {Posting Date}, 'minutes') = 1, " minute ago", " minutes ago"), IF( DATETIME_DIFF(NOW(), {Posting Date}, 'hours') < 24, DATETIME_DIFF(NOW(), {Posting Date}, 'hours') & IF(DATETIME_DIFF(NOW(), {Posting Date}, 'hours') = 1, " hour ago", " hours ago"), IF( DATETIME_DIFF(NOW(), {Posting Date}, 'days') = 1, "Yesterday at " & DATETIME_FORMAT({Posting Date}, 'h:mm A'), IF( DATETIME_DIFF(NOW(), {Posting Date}, 'days')

Bonjour,J'ai dans ma base un champ de recherche qui me permet de récupérer les dates de différents enregistrements liés. Parmi toutes les dates remontées, je souhaite définir quelle est la plus petite (ou la plus grande) : comment faire ?Merci pour votre retour.Damien

HiI am a newbie on airtable and want to start with my personal finance overview.The csv. export of my bank statement will be used to feed the base. From this I want to put each transaction into categories (to see budgets etc.). Is there a way in airtable that transactions that are repetitive are automatically put into the category that they were put before? For example: If the salary always comes from the same payee airtable should suggest the category 'salary'.Looking forward to hearing from you.J

Hoping there is maybe native support here now, but probably not.I have a table where all the records are parts, and they have part ID, unit weight, quantity, and total weight. I would like to add a field that shows what % of the total weight of ALL parts that particular part is. So for example if my total weights of 4 records are 100, 200, 300, 400, then for records 1-4 the % in the calc'd value would be 10% (100/(100+200+300+400)), 20%, 30%, and 40%.I know this was limited in the past and the "best" workaround was to link every single record to a single record in another field, then lookup the roll-up sum total, then operate on that. It works but is clunky and gives you extra tables. Is there anything native baked in yet? Seems airtable has made a lot of progress but it still has these weird "create a new table and link to that and then roll-up there and then look-up in the original table and then operate on that" work-arounds that should have been long-since handled

I’m averaging 3 columns, all rollups. Note that I have this formula in them, so certain cells are blank: IF(ISERROR(AVERAGE(values))=0,AVERAGE(values),BLANK()) The 4th column, where I want to average THOSE columns, has NaN if all 3 columns are blank. Which makes sense - I just can’t figure out how to get that 4th column to BE blank if the result is NaN. The column currently has this formula: AVERAGE({Value 1},{Value 2},{Value 3}) I tried to essentially mimic what I’m doing in the other columns but nothing I’m trying seems to be valid. Thanks!

Hello,I am currently running into the base exceeding automation limit and am trying to come up with a way to solve for multiple automations we have created by using a formula field or look up field.Currently, we assign users to certain tasks and we have another field that displays their phone numbers (long text field).  I am trying to set up a formula where IF a user is selected, it would then lookup/display from another table that user's phone numbers that are list on table 2.Is this process possible with Airtable?

How do I write a formula to populate anything that is created in that week, it will add the Monday date of that week. For Example:I have records for 10/7, 10/8, 10/10. I need the Starting Week of to be: 10/7 for all Dates. Then I need records for 10/14, 10/15, 10/16, etc. To be starting week of 10/14.I also have the: WEEKNUM({Created Date}) formula, so if there is an easier way to turn that week number into the starting date of the week, I can use that as well.

I'm using a simple formula to calculate the due date difference between today and the project due date:WORKDAY_DIFF(TODAY(), {Due Date})-1Which works for anything due today and in the future, but does not calculate properly for past due things. If today is 11/19, something due 11/18 should be Past Due by -1, but it shows -3. Given that it works for current/future dates, what am I missing here for the past dates?   

Hi all,I want to prioritise datasets in Airtable. For that I've created checkbox fields and a drop down field.I now want to create a formular field and add there a weighted text drop down menu by giving each text option a number. For the checkbox fields it's clear: If it's checked, then value1, if not, then value2:IF(logical, value1, value2) + IF(logical, value1, value2) +  But how can I add the weighted values from the drop down menu in the formular?For example IF option1, then 3, IF option2, then 2, IF option3, then 1, otherwise 0)IF(logical=1, 1, 0) + IF(logical=1, 3, IF({logical=2, 2)) In detail, this is the formular which is not working:IF({easy to implement?}=1, 3, IF

Is there a sequential order when a new record is created through a Form view? Can you define/control the order?

Can you help me with a formula to remove all characters including and after the "(" in Los Angeles (hollywood)?So my goal is to make Los Angeles (hollywood) --> Los Angeles

Hello Airtable world,I need help with a formula. I have a look up field with many options. All of these options share the same data for some field e.g. Pick Up Location.In this case, I have 1 record with 2 options (lookup field) and the look up field extracts: Montreal, Montreal.Is there a formula I can use to only keep what is before the first comma when working stringsThis is an example of what I am trying to achieveThank you in advanceMatt 

I've been scouring this forum for an answer to a variant of "Convert String to Duration" but alas, no simple answers uncovered!In my case, I have a text field that contains duration as as string such as, "1:30:00."I need this to now be a field that is either a number (1.5), or an actual Duration type field.DATEADD(), DATETIME_PARSE(), DATETIME_FORMAT(), etc. formulas return errors and cannot format this particular representation of time as a string.What incantation of formula language is required for Airtable to do this so I can use this data? Thanks!

I have a weird one: How would I even begin to convert a column that contains duration data formatted as a string like this? I want the final conversion to be a decimal number, but none of the standard DATETIME() type formulas can make sense of it. Help!

Hi!I'm trying to create a formula to calculate the number of hours between two date/time fields, excluding weekends. The fields are "Completion Date/Time" and "Writer Claimed Date/Time". So for one example, the Completion Date/Time field is 11/7/24 at 11:10am, and the "Writer Claimed Date/Time" is 10/31/24 at 2:59pm. Looking for the number of hours between the two, excluding weekends. Thanks in advance 🙂

Hi there -  I am attempting to track contract breach status (pending, resolved, or n/a) depending on the source of the breach and whether there is a specific response date. My formula will be 5 if statements, of which 4 are also if/and. I've been able to add the first two successfully but when I try to add the third, nothing happens. Any help would be most appreciated! Thank you in advance! If source of breach = avoidable loss, “n/a- avoidable loss”If source of breach = notice of agreement & noa received date >1, resolved otherwise pendingIf source of breach = repayment overdue & resolution date >1, resolved otherwise pendingIf source of breach = ownership transfers & resolution date >1, resolved otherwise pendingIf source of breach = miscellaneous >1, resolved otherwise pendingIF({Source of Breach}=

Does anyone use an external tool for viewing/creating/editing complex formulas? Is there a tool that you like to copy and paste your complicated formulas into? Notepad++, Visual Studio? I know you would lose the linking to existing fields and the function lookup, but I’ve been struggling with some of my really long nested if’s. Thanks, Mike

I am looking for help creating a formula field to change the format that phone numbers appear in.On Airtable, the phone field automatically formats phone numbers as: (555) 555-5555.I need the phone numbers to be formatted as follows instead: +1 555-555-5555.Please help!

I would like to get parameter from URL?Is it possible to get with script or automation?

Hi all, I am importing JSON into Airtable and parsing it into fields using REG_EXTRACT.Some of the JSON data are blocks of text that have "\n" (without the quotes) in place of carriage returns. Airtable treats the \n as text rather than carriage returns in the output of the REG_EXTRACT function.How can I replace the \n text characters with actual carriage returns?

Hi, is there a formula to pull the date that a specific cell was updated?For context; I use Airtable to confirm bookings for my business, and do weekly reports on these. The reporting is currently based on 'created date' as I only add them to the system when they are about to be confirmed. However, there has become a need to add them to the system as soon as I receive the booking request so I can follow up on any tentative bookings. However, if they aren't confirmed until the following week or later, they will fall into the wrong reporting week. I'd like to change my reporting filters to be based on 'created date' OR a new formula field that pulls the date the 'Status' field was change to 'Confirmed'.I have other automation in play to manage tentative bookings, but it's failing and this formula would be a much better solution (if it exists).Any help would be much appreciated!

I have a multiple select field with four options: 2020, 2021, 2022, 2023. I want a formula field that prints how many of these options were selected. I played a bit with Count and If statements, but couldn't get anything to function quite right. Any help appreciated!