Formulas

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({easy to implement?}=2, 2, IF({easy to implement?}=3, 1))), 0)nor doesIF({easy to implement?}=under 2h, 3, IF({easy to implement?}=up to 8h, 2, IF({easy to implement?}=a day, 1)

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}="Avoidable Loss","n/a",IF(AND({Source of Breach}="Notice of Agreement",{NOA Received Date}>"1"),"Resol

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! 

We have a load of addresses in a database. They are individually pasted into a Long Text field.The issue:Some addresses are in this format - 51A Wigton RoadCarlisleCA2 7AYAnd some addresses are in this format- 51A Wigton RoadCARLISLECA2 7AY So the difference is that the Post Town is uppercase - which is what we want.But a lot of submissions have come through in lower case on that second to last line, which is incorrect. I need a formula to find and replace the all text before the last break and after the second to last break, ie. all text on the second to last line. And replace it with the same text but in UPPERcase.  The addresses are not always on 4 lines, sometimes they are on 5 lines, so we would need to work backwords from the last break. A formula to do this would be much appreciated.  Thank you in advance.

I have two tables:Table one shows a list of tasks done on different computers and when the task has been marked as finished:TaskId, FinishDateTime, Computername.In table two I have information who was logged in when on on which computer:DateTimeStart, DateTimeStop, EmloyeeName, Computer. I want to have in table one an extra column, showing the name of the EmployeeName that was working on that computer while the task was finished. So we need to use the FinishDateTime and Computername to query the other table to find a match with exactly this computer and FinishDateTime in range between DateTimeStart and DateTimeStop. The result of the formular should show just the Name of the Employee. I hope this is clear. How to create the formular? I am familiar with excel, where i would do some kind of nested vlookups, but have no idea how to achieve this in airtable.

I am trying to fix a formula for hours, but I can't find the error. For some reason, the last argument in an OR Formula is ignored.The base has 2 tables (Table 1 and Table 2)Table 1 has one record with the following values:Field 1 (single line text) = AField 2 (single line text) = BField 3 (single line text) = C Table 2 also has one record with a multi-select field. The values areField 1 = C, D, E, F My OR formula should check if any of the 3 values from the record (Table 1) is included in the multi-select field (array) of the record in Table 2.  OR( FIND(Field 1 (Table 1), Field 1 (Table 2)), FIND(Field 2 (Table 1), Field 1 (Table 2)), FIND(Field 3 (Table 1), Field 1 (Table 2)) )  When I add "C" to Field 2 (Table 1), then it's correctly counted.If I add C in Field 3 (Table 1) I don't get an error, and the formula just goes through. It just ignores the last argument, "FIND(Field 3 (Table 1), Field 1 (Table 2))". Does anyone have an idea

Hi Everyone,I am trying to create a forecasting table that will tell me how many months of cover I have based on current stock on hand. In the image you can see the fields Sock Holdings and Outbounds Forecast. What I need is to create a field where I can see for example March 2024, how many future months I have in cover based on current SOH and the figures in the outbound forecasts.Thanks! 

Hi community,I have a formula to combine emails from multiple columns, that generally contain multiple linked records (but not always).  This REGEX formula works really well for the first 3 columns, but is failing on the final.  Any help on what I'm doing incorrectly would be greatly appreciated!Goal: string together a list of email addresses from multiple columns, with a "comma" between them, but if a column is black, don't add ",". Current Formula is: REGEX_REPLACE(CONCATENATE(IF({Moderator Email}, {Moderator Email} & ", "), IF({ADM Primary Email}, {ADM Primary Email} & ", "), IF({Other Panelists Email (Primary)}, {Other Panelists Email (Primary)} & ", "), IF({Other Panelists Alternate Email}, {Other Panelists Alternate Email}) ), ", $", "" ) For some reason, the last {Other Panelists Alternate Email}. is not comma separated.  The result merges all 3 user emails into one long strain...... 

I am trying to calculate the number of months between today's date and the "Test Date" field. When I use the DATETIME_DIFF formula it does not calculate it correctly for all values.  In the Test copy column I have this formula:DATETIME_DIFF({Test Date}, TODAY(), "months")It calculates some of the values correctly but then other ones wrong and I am uncertain how else to do this calculation in Airtable. What I need is that if todays date is Nov 7, 2024 then any dates in Nov 2024 would give a value of 0, any dates in October 2024 would show -1 but you can see the dates on October 16 and October 30 show a value of 0. You can see this issue also on the last row of the screen shot where the date of Jan 2, 2024 should be 2 but gives a value of 1.     

Hi, I' trying to figure out something with the form.I need my salesteam to submit sales info in the form.Lets suppoer we have to submit 2 field:1) sale price2) costI would like that after the user typing the 2 infos above, a field called "profit" will show the result of the equation: "sale price" - "cost"Is this possible?I'm trying to insert not editable fields in the form, but I couldnt figure it..thanks

Hello!I'm using a prefilled form URL field. When a customer submits a button it opens the prefilled form in the corresponding URL field.The problem is that it works perfectly on desktop, but when using App, the prefilled form formula does not work.Is this expected bahaviour?Thanks!

Hi, I want to generate a vcard file from my records automatically. I already connected my Airtable to my app (Adalo) where I receive my users information. If I could generate a vcard file I could export it back with Zapier to my app, but I didn’t find that how could I generate it. Maybe a formula, but I’m not sure about it. Thank you in advance!