Formulas

I would like to parse a date string of "2023-02-10", with no time information, into a specific timezone (Europe/Malta). We use daylight savings, so right now, 2023-02-10 00:00 Malta time would be 2023-02-09 22:00 GMT.  For this reason, I cannot specifically add a timezone of +2 because this will chagne to +1 later in October. I noticed DATETIME_PARSE() takes a locale as the 3rd parameter. However, this does not seem to be documented at all in the formula reference. I've tried using values like "Europe/Malta", which works in SET_TIMEZONE(), but doesn't do anything.  I've also experimented with SET_TIMEZONE(), but that seems the other way round, to convert an existing Airtable date with a particular timezone.Basically, what I would like is something where for example could parse "2023-02-10" into the Malta timezone. 

I need some help trying to write a conditional formula...The idea is IF any cell in column 1 is duplicative/match entry, then look at corresponding column 2 cells and merge merge into corresponding column 3.Is if A2 and A45 match look at/merge B2 and B45 into C2 and C45Is something like this possible?

I found this formula on the Airtable Developers Page // change these names to pick a view:let table = base.getTable('Invoices');let view = table.getView('Grid view'); let result = await view

I am trying to have different items use different formula code, specifically to modify locks codes that will be different based on date from users and the limitations of each lock. I set up a list of locks and wrote and tested formulas that check for deficiencies in the lock codes that are created from the users phone numbers. An example is if the lock code starts with a "0", the lock with not accept the code. So there is code that looks for a "0" and replaces it with a "1". So if the users code was "0234" the code will change it to.. "1234". IF({Code},IF(LEFT({Code},1)=0,CONCATENATE(1,RIGHT({Code},3)),{Code}),ROUND((VALUE(DATETIME_FORMAT(CREATED_TIME(), '0.smh'))*(9999-1001))+1001,0))Also if for some reason there is no code present, a random code is generated.This code has been tested and lives in a text column in another table, my hope was to use a look up to use this formula code when it applies.  

I have this IF Statement Formula:   IF(  {UNH Facilities}= "UNH Facilities - GH Cleared",    "🟣",     IF({UNH Facilities}= "UNH Facilities - Supplying Items",      "🟠") ) but I want to add one more piece that returns both an orange and purple circle if {UNH Facilities} has both ""UNH Facilities - GH Cleared" and "UNH&

Work with print media plans and I have an Excel workbook I've used for years to calculate costs. There are many variables with cost calculations and I will stay with this workbook however I'd like to export the final costs for each publication at each page size for import to Airtable to automatically calculate the costs based on each buy. Most buys consist of two components. A full page ad and a small page that accompanies the ad for legalese. Attached is screen capture of my media planning table and costs table with how I am trying to map costs. Is what I'm trying to achieve possible and/or is there a better way? I tried searching for a solution first and could not locate a similar request so if you know of link to send me to that would be great. Thank you in advance. 

Hi All - I'm struggling with creating calculations on the base shown below. What I'm trying to do is calculate the % change in total enrollment growth by 1YR, 5YR and 10YR between Fall Years for each university (it would be a changing calculation with each new Fall Year record added for each university).While this is easy to do with a vlookup in Excel (i.e. matching University, Fall Year and Total Enrollment with Fall Year-1, Fall Year-5, Fall Year-10), I'm struggling with a finding solution here in Airtable.I'm feeling like it might require a linked Lookup table (or adding another field) to make it work. But just not sure how to go about it. Or, if it's even possible between records in Airtable. The numbers shown below for the % growth fields were imported from Excel. Any help would be greatly appreciated!Thanks!&

It seems like it should be a simple solution, but my brain is tired! I am using a rollup field to show the total discount so I can highlight it on a quote which I have made with page designer. If there is no discount ($0.00) I don't want it to show on the quote. So I am trying to figure out how to leave it blank when there is result is $0. I tried adding another field with a formula, but then I can't get a positive number to be in currency format, just a string. I tried doing an IF Aggregation formula but kept getting an error. I made sure that the source field (Amt Saved) does not return anything if the result is 0 hoping that would follow through to the rollup, but no luck. Can someone please help? 

I am attempting to make a Travel Sheet my Supervisors access through a Form view, that allows the selection of Employees (linked from our Employee Table) based on their current Employment Status (which is a dropdown selection on the other table). When I select the "Only include linked records from the Employees table that meet certain conditions" and have it to where: Employment - is - "Current Employee", however when the form is being previewed, the selection is pulling all employees, regardless if they're past or current. I have also tried the same conditioning filtering for Projects as well based on their "Status" as well, and it also doesn't work on filtering only what I want it to show. What am I doing incorrectly, as this seems like it should be a very basic and simple conditional request? Thank you for your time.

I have written this formula a few different ways and in each case, I get the result but am unable to format as currency. Error: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.Here the two most successful attemps:IF(AND(OR({Channel} = "Print",{Channel} = "Digital"),{New | Repeat} = "New"),IF({Channel} = "Print","$" & VALUE("450"),IF({Channel} = "Digital","$" & VALUE("75"),"")),"")IF(AND(OR({Channel} = "Print",{Channel} = "Digital"),{New | Repeat} = "New"),IF({Channel} = "Print",450,IF({Channel} = "Digital",75,"")),"")Any insight is greatly appreciated!!!

I need a simple formula that will find a specific date AirTable Date Field is formated as ‘2021-06-10’ or ‘YYYY-MM-DD’ I can’t seem to get the right formula to find a specific date IF(Date = 2021-06-10, TRUE()) IF(Date = '2021-06-10', TRUE()) Neither one works. Please help. Edit: I think I found the solution IF(Date = DATETIME_PARSE('2021-06-10'), TRUE())

Hi guys,I have a column with the time sheets of a meeting, for instance "9am-12am// 1pm-4pm" or in european terms "9h-12h//13h-16h". I would like to find a formula to transform those numbeers into decimal hours in a new column. In this case, it would give 6. Is it possible with a formula ? Or by any other mean ? Thanks a lot,Greg

I have a formula that works as is today, but I'm trying to add a condition to it and it keeps erroring out?  Was hoping to get some extra eyes on it?What we're doing today is taking 3 different date fields, convert them to a #, and then display the max of the 3 fields.  This might be a weird way to get where we need to go, but it has worked for us.  All the "correct" fields are date fields in our base.Formula today:IF(   OR({Correct Lift + 45}, {Correct Clamp + 45}, {Correct Date Mod}),    DATETIME_PARSE(      MAX( &nb

Hi there, We use airtable to track employee hours and payroll. A former employee set up our base so I'm a bit unfamiliar how to make changes to the boards within airtable. However, I have two new employees that I'm trying to generate a random employee ID number for and can't get a number to generate. I'm not completely sure where to go to make the changes. When I go to add a new employee to the base, a "number" generates but it has both numbers and letters, I'm looking for a pure number. Screenshots attached. Any help would be extremely appreciated 

Hello everyone. I just started my journey with air table but I am facing a dilemma with hidden field formation on a form that i am tryng to create. Here is the background of my issue. I have two tables Delegates and Summary: The "Delegates" section contains details such as names, businesses, and dates of birth. The "Summary" section is linked to the "Delegates" table and presents information in a structured manner. It includes not only names, businesses, and birthdates but also 80 additional fields representing steps that delegates need to complete in a program. Delegates can complete these steps in any order; for instance, Delegate 1 might finish steps 1 and 3, while Delegate B might complete steps 3, 4, 6, and 7. Each step allows the delegate to select whether it was completed before the bootcamp (BF), after the bootcamp (AB), or during the bootcamp (BT). This selection is indicated in the respective cells, distinguishing between AB and BT.

Hey folks,I have two tables:Table 1Table 2On table 1 I would like to get a count of the number of records that satisfy certain criteria from table 2 and return one of two values: Limit or Not Limit.The conditions I'm looking to count with are as follows:1. Records created with a specific timestamp range. (For my use case it would be records created in the current month).2. Records created that are associated to values across one of three columns. (For my use case we would be Baisc OR Pro OR Premium. So in the screenshot below we would count the number of values in the "Basic Cover Design" column that have a timestamp within the current month for instance.)3. Run this counting logic/formula against each row in table 1 so in order to determine if the user has reached their limit or is not at their limit for the current month. (I m

Hello, how can I rename all the "Web" addresses to a "Link" for a cleaner user experience? Thanks!  

Hello,I can't find the issue here that is preventing what i thought would be a very simple table calculation.I have a table called Deals with many different fields but one of them is a currency field that i want to use a rollup field in another table to calculate the sum of the values in this currency field.I'm stumped as to what i am missing here but all i get are zero values. I tried to workaround the currency field by making a formula field that just turns the currency values into normal integers but no luck.I attached a few screenshots for context, hopefully someone can see what i'm missing.Thanks,Alec

Hi everyone,I would be grateful to everyone who offers solutionsI have two linked tables: Problem 1  ✅ ResolvedI need a formula for the "Status" column that will display such statuses: Problem 2When I add a new record to the "Sales" table, I need to make sure that the product does not appear in the list if the entire quantity is sold: Problem 3When I select the quantity of products in the "Sales" table, I need to make it impossible to select more than the quantity in "Purchase" table:

How do I create a table that shows the number of emails sent per day?

Looking for a simple formula that spits out that last Friday of the calendar year. I'm trying to avoid needing to make this a manual update so would love to find a formula that just spits that out without me having to update it at the beginning of the year.  Thank you! 

Hi there, I am trying to calculate a formula for a discount. The discount category is single select (10% or 20%). I want to calculate what the total revenue will be with the selected discount. The current formula for revenue is (price * quantity) 

Hello community I have been trying to solve an issue. I am looking for the correct formula to automatically fill the columns, (CCC Loads, variable expenses, fixed expenses) in the cost per mile calculator listed below. Those entries will be pull from 2 other table pictured below as well title (operating expenses & ccc loads). Please let me if anyone has any input on how I may be able to solve this issue. 

Im not sure how to ask this in order to look up suggestions. I have an order form that I created on a workstation. Based on the photographer, the client is given the options offered by that photographer. Once they select the options/services they would like I am hoping to have a field that auto adds the selections they made. Is there a formula or script I can create where each selection they choose can be given a designated price that can be added up for a grand total much like an invoice?I am attaching an example of one of the photography options 

Hello friends of the community, I'm facing a problem when trying to filter the date in the interface. Whenever I select the month it shows me the total, the same happens when I use the specific date filter. Do you know any formula or trick to solve this problem?