Formulas

Using the NOW() function in a formula no longer updates the time reliably, even after refreshing the base. It only returns the original time that the formula was created, but then it doesn’t update reliably beyond that. I’m specifically referring to a formula that just has this as its formula: NOW() I’ve noticed that within the last few days, this brand new disclaimer was added to the formula reference page for both the NOW() function and the TODAY() function: ***The results of this functions changes only when the formula is recalculated or a base is loaded. They are not updated continuously. Forget that this sentence has several grammatical errors in it. :stuck_out_tongue_winking_eye: The real problem is that the described behavior is incorrect. (And the behavior has changed on us.) I created this formula: NOW(), and it starts off with the current time. But then, I refreshed my web browser ove

Hi,I want to have in one column the data from the remaining selected text, lookup and line items, fields which will be formatted in html. I managed to partially achieve the desired effect, by using the formula type But unfortunately data is not presented correctly. This is my formula. <li-code lang="markup"> "<table><thead><tr><td>Składnik</td><td>Zalecana dzienna porcja do spożycia: " & zdd_pl &"</td><td>*%ZDS</td></tr></thead><tbody>" & "</tbody><tfoot><tr><td colspan="&"3"&">*%ZDS - Zalecane dzienne spożycie. | † - Nie ustalono zalecanego dziennego spożycia.</td></tr></tfoot></table><h3>Składniki:</h3><p> " & wszystkie_skladniki_pl &"</p><h3>Alergeny:</h3><p><b><u>" & alergeny_pl & "</u></b></p><h3>Stosowanie:</h

I have a formula field containing NOW() and nothing else. The field is showing the wrong time. Right now it's 3:02 a.m. in the desired time zone (Eastern Daylight Time), but the field shows 2:59 a.m. I've attached a screenshot showing the difference between the displayed time and my computer clock.Weirdly, when I first set it up but hadn't yet changed the time zone from GMT, it was 2:59 a.m. but the field showed 6:03 a.m.Is this a known bug? I need this field to calculate the difference between when an item is due and the current time (that is, how many days/hours until it's due), and the time does need to be accurate to the minute.

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?