Formulas

Hello, I am having issues with adding additional advertising week options to the formula below. This formula works well:IF(AND({Advertising Plan}, {Live Date}),SWITCH( {Advertising Plan},"4 weeks", DATEADD({Live Date}, 27, 'days'),"52 weeks", DATEADD({Live Date}, 364, 'days'))) But as soon as I try to add additional week options (I have already added them to the Advertising Plan single select field), I get the 'formula not valid warning'. This is what I am putting in:IF(AND({Advertising Plan}, {Live Date}),SWITCH({Advertising Plan},"2 weeks", DATEADD({Live Date}, 13, 'days'){Advertising Plan},"4 weeks", DATEADD({Live Date}, 27, 'days'),"8 weeks"({Live Date}, 27, 'days',"52 weeks", DATEADD({Live Date}, 364, 'days' ))) Please let me know where my formula is mucking me up, I feel I am going cross-eyed after looking at it for so long. ThanksP

Hey there!I am an Airtable novice looking for formula help. 🙃I have a table with columns: Year, Candidate, Entity/Org, Side, Race Level, State, DistrictRight now, my formula is:IF(Candidate !="", CONCATENATE({Candidate},", ",State," ",District),CONCATENATE({Entity/Org}," - ", Side))which will pull the Entity/Org & Side when the candidate field is empty. However, there are instances wherethe District field is empty (in which case I want to pull in the {Candidate},", ",State," ",{Race Level})& instances where the District field AND the Candidate field are empty (where I want to pull in the {Entity/Org}," - ", Side))Any help is greatly appreciated!Thanks!

I know how to make my date show correctly using DATETIME_PARSE(DATETIME_FORMAT({EDate}, 'MM/DD/YY'))And I know how to create a new string by using this formula{EDate (from Events For Sync)}& "- "&{EID#}But what I can't figure out is how to make the first formula work with the second formula into one formula that works.I've tried different ways but nothing seems to work and I am stumpedThis may seem easy for some - but I am frustrated that the date field when you have it set for one way doesn't carry the content over when used in a formula.  But whatever.

Here's the story:A few years ago I set up a base to capture birthday information for school applicants. Users would fill out a form (everyone using it was on EST), and enter a birthday (date only, no time).A year or so ago I started getting reports that some dates shown in our dashboard interface were off by 1 day. E.g. if the entered birthday was Dec 10, the interface would show Dec 9. (This may have correlated with when Airtable made some changes to the way they handled dates/timezones internally?)I'd like to fix this "correctly", so I'd really like to understand what is happening behind the scenes.When users submit a date with a form, does the user locale or browser timezone have any influence on the submitted date? Did the behavior of form-submitted dates change when Airtable made date handling changes?Are dates still stores as ISO strings (with timestamps) even if we don't want/have any time information?How can I correctly render a date submitted with a form that isn't potentially

When a person joins our company I record their actual start date. However, their annual contract renewal date always starts the 1st day of the following month.I want to be able to record their start date and then have a column showing their next annual renewal date (the first of whatever the following month is) that updates as years go on.Example) Someone starts on 11/15/2023. I want the renewal column to show 12/1/2024.  Once the present date is past 12/1/2024, I would want that to automatically update to 12/1/2025.

I need to add up the total days of sick leave, as well as the total vacation leave. This way I can track if they have reached the maximum # of leaves. Can someone assist me?

Let's say I have a table of Invoices and then a table of payments. Each invoice is an aggregate of payments i.e. one invoice can have multiple payments. How can I get the index of each payment in the list of the invoice?

So I'm tracking vehicles in my base(s), and I typically use a formula for my primary field for ease of search for my coworkers:  CONCATENATE("Branch #"," ",Location," ",Year," ",Make," ",Model," ",VIN) Ends up yielding something like Branch #123 2022 Ford F-150 1FTCR15X0TTA01050 This works really well for us, because we use that primary field in a wide variety of forms and other linked bases, so if someone doesn't know their vin, they can search by branch and narrow it down, etc. What I'm trying to get my head around is how to (if possible) reference a single selects selection in an IF statement re: that primary field?  An order to request a new vehicle adds a new line to the base, and as we process the order at that point we add in make, year, model, etc.  Until we do that the concatenate formula just returns "Branch #123" because the other fields are blank so its hard for folks to track their orders and see any inf

Hi there, I am so ashamed to ask this but I could not figure this one out by myself. I have 2 simple text values and an autonumber. I would like to create a formula where i can have these information but not completely. Ex:  1st vlaue is JOHN Second value is DOE and autonumber 333I would like to have  the result 33JODO Thank you for your help. 

Hi,I'm terrible at the formula stuff, but basically I have a Publish Date in my Airtable view for when a piece of content should be published. I want to add the Day of Week as well so we can clear see which post is which day, as well as the actual date (as some gets scheduled a bit further in advance)The formula I'm using to display the day is:  DATETIME_FORMAT({Publish Date},'dddd')All I need to do is display nothing if there is no publish date set

I have a column with the title 'Country', and I am trying to add a new formula column which identifies the Country and creates an entry for the Region automatically. I can get the first IF function to select one region, but when I try to add the second, it fails. Here is what I have for 3 (of the shorter regions) of the 8:IF(REGEX_MATCH({Country}&"", "Belize|Costa Rica|El Salvador|Guatemala|Honduras|Nicaragua|Panama"),"Central America")IF(REGEX_MATCH({Country}&"", "Canada|Greenland|Mexico|Saint Pierre and Miquelon|United States"),"North America")IF(REGEX_MATCH({Country}&"", "Argentina|Bolivia|Brazil|Chile|Colombia|Ecuador|Falkland Islands|French Guiana|Guyana|Paraguay|Peru|Suriname|Uruguay|Venezuela"),"South America")Thank you.

Hi - I have a table with a list of cash flows, dates. These are linked to another table which contains individual deals. I want to calculate, for each deal, the IRR - in Excel this is using the IRR function which takes a list of dates and a list of cash flows and returns a single IRR number. Can this be done in Airtable? Rollups seem to only allow calculations involving one linked field only - whereas I need two: both date and cashflow. Also, there doesn’t seem to be an IRR function. The calculation is relatively straight forward, can I code my own function to support this? Secondary question - right now I have to manually for each date/cash flow record link it to a given deal. Is there a way to link all cash flows where dealID equals x?

Hi folks, I was able to extract initials from full name (thanks to the Name demo base from @W_Vann_Hall ), but because of duplicate names, I added the email as part of the customer identifier (full name + email), which lives in my Customer table. Now I can’t seem to figure out how to extract the initials from this new field. When I try to apply the same formulas to the Name field linked to my Customer table, the formulas no longer work (because I guess it’s merely a linked record, not actual value?). So I created a second table called New Name Table, and extracted the initials under that table first, then linked it back to my original table. But the values are not populating when new records come in (i.e. blank records in the screenshot). FYI the field “Calculation” is the first initial formula applied on the Customer Name field. I am completely lost on how to trim everything up to " (" from the right. Otherwise that would be a solution too (and spares me an additional table). This is

Hey folks,I am currently working on a formula to compare the current time against a timestamp that's populating into my table from a separate service.I am also attempting to create multiple columns that iterate on my formula by comparing NOW with DATEADD +5 "hours" to check if NOW is 5 hours since the time in the timestamp.Having some trouble figuring out what I'm doing wrong with this formula and hoping someone can point me in the right direction:  IF( DATETIME_FORMAT(NOW(), "MM/DD HH:SS") > DATETIME_FORMAT(DATEADD({Timestamp}, "MM/DD HH:SS"), +5, "hours", "MM/DD HH:SS"), "yes", "no")  

Hello! I am working with a formula that is computing a rate for each area that is being updated from a submissions tab; however, not every area has data yet, so Airtable assumes 0. This means that in the future records we get the output NaN as we are dividing by 0 at the moment. Is there a function or if statement that can be used to detect if there’s and NaN output, and if so, set it to 0? Thank you!

Hello! I'm slowly learning how to use date formulas, but I have hit a wall in my ability to troubleshoot.I have two date formulas causing me trouble.1) My Rush Due Date is displaying #ERROR! when "Rush" checkbox is not checked.DATETIME_FORMAT(IF(Rush, WORKDAY(Created, 4, '2023-11-11, 2023-12-25, 2024-01-01, 2024-02-19, 2024-03-29, 2024-05-20'), ""), 'LL')2) My "Time Since Ready for Pickup" formula is displaying NaN when a row is not yet "Ready for Pickup"IF(Completed, "Completed", DATETIME_DIFF( NOW(),{Ready for Pickup Timestamp}, 'days'))Are there any heros out there who can help me out?Thank you so much in advance!Devon

Hi everyone, I searched through the community posts but could not find anything.Using a formula I created custom URL for every person in our company that will allow prefills in a form.CONCATENATE('https://airtable.com/appxxxx','?prefill_Your Name=',Name)for example 'https://airtable.com/appxxxx','?prefill_Your Name=',John Doe)Problem is that there are now blanks and therefore only the first part of link is recognised as such. I know that I can use %20 is used in the final URL. But this does not make the URL in my dataset clickableAnyone an idea what I would need to do? Thanks for your help

I am using Airtable to track hours worked versus workable hours in a month to try and calculate how much overtime there was in a given month. The table below is correctly doing what I want where # Hours Worked is the number of hours a person logged in a given month for different tasks. Total Hours is work days including holidays, Total Work Hours is work days not including holidays. And Hours Worked - Holidays is self explanatory (we don't want to count holiday time towards comp time). Looking at the below screenshot, the comp time would be 1 hour (161-160). The problem is that I don't think you can make a formula that can do math with the summary types. I was trying to do rollups in a different tab but it wasn't working. In excel I would do a SUMIF function where the criteria is that Month = Sep 2023 and Resource = X and have it sum total work hours and another one summing hours worked - holiday so I can subtract them.I'm trying to do this by resource by month. Does anyone know a

Hello,I have a formula field that is supposed to return a value ("updated") when a last-updated field is today.I live in another time zone, but all of my dates are set to Eastern time to reflect my working hours. Some of the posts I have read have said that a time zone error will produce this problem. To show that all time fields are set up for Eastern, I am including screenshots below. You can see that the last updated date/time is today (10/10), and the value of today is also 10/10.The formula field is not showing the value "updated." Here is the way the formula field is set up  Here is how the last-updated field is set upHere is the way the start date field is set up  I have also tried using "now" instead of "today."I am sure that there is an explanation, but I'm unable to find one based upon my searches of the community posts.Thank you for any help you can provide!   

I am trying to incorporate a formula column which looks for entries which may include swear words, but I cant seem to get this formula to work. Here is a snippet (I've taken out a lot of the middle which is just 3230 different swear words) but the beginning and end are there. Any ideas? IF((OR(SEARCH(“2 girls 1 cup”, ARRAYJOIN({Lower})) > 0, SEARCH(“2g1c”, ARRAYJOIN({Lower})) > 0, SEARCH(“4r5e”, ARRAYJOIN({Lower})) > 0, SEARCH(“5h1t”, ARRAYJOIN({Lower})) > 0, SEARCH(“5hit”, ARRAYJOIN({Lower})) > 0, SEARCH(“a fanabla”, ARRAYJOIN({Lower})) > 0, SEARCH(“a pecorina”, ARRAYJOIN({Lower})) > 0, SEARCH(“a tomar por culo”, ARRAYJOIN({Lower})) > 0, SEARCH(“a tomar por saco”, ARRAYJOIN({Lower})) > 0, SEARCH(“a$$”, ARRAYJOIN({Lowe

Hey guys,Just trying to work out a simple Formula to create a day count until a certain date (based on one of our fields) without including weekends. For example. We have a customer due date for an order which is put into a field, just wanting to have a field that calulates how many days we have for production until that date. So far I have: DATETIME_DIFF(  {Due Date},  TODAY(),  'days')Which works fine but is including weekends. To combat this, I did try:WORKDAY_DIFF(  {Due Date},  TODAY(),  'days')But this is throwing an #ERROR. I know I am definetly missing something. Any help is appreciated. 

hi, when i use the field #number, the number always look like this 1.00but i i want to put my numbers as 0.01 how can i do that? because everytime i put 0.01 they change it to 10 

Hello,I program a film festival and we use Airtable to organize the submission we receive. This year we have two different programming teams that should watch half of the submitted projects. I wanted to create a formula that would calculate the total of duration of the films (we have a field for the duration in HH:MM:SS), divide it in half and then assign each half for team A or team B. Is that possible? I have tried to add some simple SUM formulas but I'm still a beginner in all of this. Thank you!

I am looking to remove the comma separating the two lists. Here is a picture to show an example.    Is there a way to do this?Thanks!

Hello everyone,Trying to come up with a formula using Chat GPT (and some tweaking) to create a formula field type that displays a subscription's (record) amount in a cell based on budget status and the payment schedule.I want to create a formula that checks the following conditions:The Payment Schedule is "Annually."And one of the following is met:a. Budget Status is "Projected" and Renewal Date is within the specified range.b. Budget Status is "Actual" and Purchase Date is within the specified range.If these 2 conditions are met, it displays the {Subscription Amount}. If none of the conditions are met, it will display "Conditions not met".Here's the code:  IF( AND( {Payment Schedule} = "Annually", OR( AND( {Budget Status} = "Projected", {Renewal Date} > DATETIME_PARSE("2023-01-01"), {Renewal Date} < DATETIME_PARSE("2023-02-01") ), AND( {Budget Status}