Formulas

I’m trying to bring in a spreadsheet into a Base. Each month, we pull a report that tells us how many people, per department, we have. Department 1 = January '19 has 32, December '18 has 28, November '18 has 34 Department 2 = January '19 has 88, December '18 has 64, November '18 had 100 etc. We create a column for each month and put that data in the column 11/2018 32 88 Next column 12/2018 28 64 Next column 1/2019 34 100 We do this every single month, so that means we are adding a new column with the newest data. Issue #1 I need to calculate the difference between the current month - past month. Column 1/2019 - Column 12/18 I then need to calculate the difference between the current month - 2 months prior Column 1/2019 - Column 11/2018 How do I do this, knowing that the column is going to change every single month when we add the n

I would like to add a column that displays a number (e.g. “3”) based on the number of times a word (e.g. “YES”) appears in 5 specific columns of my base. How would I do this?

I’ve been reading the board attempting to properly formulate a nested IF statement, but I keep getting an invalid formula error. Could someone please help? This is what I’m trying to do: each record of my database entries are defined by either being Open, Billed or Paid. I use a multiple select drop down to mark the record. What I would like to do is have a field serve as a time stamp for when the condition changes from one to the other. This is the formula I have so far, but it is not working: IF({Status}=“Open”,DATETIME_FORMAT(NOW(), ‘MM-DD-YY’), IF({Status}=“Billed”,DATETIME_FORMAT(NOW(), 'MM-DD-YY’), IF({Status}=“Paid”,DATETIME_FORMAT(NOW(), ‘MM-DD-YY’)))) What am I doing wrong?

I am looking to get the following to work in one column, so the url link still functions. O Beach Ibiza, gps url link

I have a status field that reflects status based on a date field {Finish} being overdue. The status field returns #ERROR! if the Finish date field is null. Can somebody help me extend this formula to suppress displaying #ERROR! if no date value entered? Here’s the start of the formula, which works if there is a value for {Finish} IF({Complete}=BLANK(),IF(IS_BEFORE({Finish},TODAY()),“ :triangular_flag_on_post: Past Due”,“ :heavy_check_mark: On Track”))

Hi, Need some help. DATETIME_FORMAT(DATEADD({Date},{NumberMonths},‘month’),‘YYYY-MM’) example: Date = Jan 1, 2019 NumberMonths = 3 the result is 2019-04 if I have a value in the record table like Day = 25 I want the result to be 2019-04-25 This is for a payment table that reminds me to pay on the 25th of the 3rd month from Jan. 1st. Thanks

Hi, I have creating a time sheet with the following: Start times in a table at increments of 15 minutes - 6am, 6:15am, 6:30am etc… End times in a table at increments of 15 I have created a form which links to the start and end time tables. I then have a formula which is meant to subtract the input data. My problem is that Airtable is unable to calculated the difference of the end time 17:00 (or 5pm) to the start time 7.00am. The formula works if the field is done as duration. But from the guys who send the forms, they struggle to enter direct inputs as hh:mm. They prefer to have a selection of time in increments rather than putting a time input. Any help would be much appreciated! Regards, Kevin

I need to populate a Lunch Menu Scheduler with a Unique Lunch Menu each week day and I want it to grab the menu item from another table of 10 Menu Items sequentially. So every Monday the “Dinner” field is filled with the next meal in a list of 10 Monday Meals. Tuesday draws from another table of 10 meals…etc. I am so noob with Airtable this seems so simple but it’s giving me a headache!

Hello everyone, How do you remove the last word from within a string in a record so that another record shows the same without the last word?

I am looking to create profit margin and profit margin formulas on a multi-record base that I am using for inventory management. The manufacturing cost is on one page, purchase orders on another page and sales orders are on the final page. I’m looking to create something to show those profit margins and $ on the sales order page to show how each product we carry stacks up against each other.

I’m trying to make a basic set up for switching between currencies when pricing a product I’ve got 2 fields; one for € and one for £ and then a formula field that’ll show the price from the £ field or the € field depending if a ‘switch’ is set to £ or €. I really want to show the displayed price with 2 decimals, but it doesn’t show a price is fx. 2.00. I tried with the ROUND() function but doesn’t work - is there a way for 2 decimals in the formula field. Currently, I’m using the following: IF({Currency switch}=“€”, "€ " & Euro, IF({Currency switch}=“£”, "£ " & ROUND(pounds,2), BLANK()))

I have a table called “Conferences Costs” I have a table called “Conferences” In Conference Costs, I pull in the conference name from the “Conference” table. After that I have a column called “Year in Review”. This is the year that the conference falls under for budget reasons. I also have a column that has a check box “are we attending?”. In the conference table, I want to pull in Year in Review information but only if the checkbox is checked. How would I do this?

I’m using a formula to create a URL that automatically prefills a form field in an airtable form. It uses the title field in the URL and the replace function to replace spaces with %20 But it also seems to be removing any periods that are found in the title. So even though it’s using a formula to create the URL, some of the URLs work and some don’t because it’s removing the periods. Any idea how to stop this?

I have a start date (example 12/25/18) I have an end date (example 12/26/18) I need to find out how many days that is total. I used this: DATETIME_DIFF(Date1,Date2,‘days’) and it came up with 1 (26-25=1, however that is accurate as it needs to be 2, since it is the 25th & the 26th.

I get an error when I use DATEADD with a date I formated in another field with DATETIME_FORMAT. Is there a way to “unformat” a date?

I have a schedule table for a church band. Another table is used for setlists that are connected to every church service. Every record in the setlist table is linked to the schedule table through the datefield. In the setlist table I want to filter the view that every user will see this way: show me the record that is after yesterday before one week from now This filter works fine with a regular Date field, but appearantly not with a date field that is retrieved by linking to another table. The options for filterin this date are very limited: contains / does not contain is / is not is empty / is not empty My question: is there a formula that I can use so the filter will treat this linked Date field as a regular Date field?

Is there any way to concat all the values in a column? Bascially I have 60 email addresses and I want to concat so that I can reference all the emails at once. Need all in one field. Similar to the CONCATENATE(TRANSPOSE()) functions in excel? Any chance?

Expected behaviour: return the number of selected items.

I have a “Rates” table that I am linking into a column on an “Estimator” table. It seems as though I can’t use the linked-in Rate data in a formula on the Estimator table. Is that right??

Hi there, I am trying to extract a string from a field using the MID() Formula. It should be pretty straightforward, but the output is blank for every single record. I have tested this by using several differently formatted fields (i.e. Text, Single-Choice, Multi-Choice), and have not been able to output a result in any of my tests. For example, I want to remove the emoji at the beginning of the string “ :hammer_and_wrench: Service” in the field {Department}. My formula is: MID({Department},2,0) The result is always blank. Is this something I’m doing wrong, or an actual bug?

This is for a registration DB Column 1 - Order Amount (this is a currency format) Column 2 - Amount Paid (currency format) Column 3 - Balance Due (formula with 1-2, formated as currency) Column 4 - Scholarship? (checkbox - if family got a scholarship then all fees are waived and this box is checked) Column 5 - I want this to say 1 of 3 things: If Column 3 is $0.00, then “Complete”, if Column 3 is anything greater than $0.00 AND Column 4 is not checked, then “Pending”, but it if is checked, then “Scholarship” THANKS!!!

Hi, I need a formula to calculate 4 seperate Service level agreements The SLAs are as follows: 4 hours Same Day 48 hours 10 days I need a field to show the “due” date for each record based on its sla. My base creates Opened Date when record is created. I assume I need a nested formula to calculate the date/time when the SLAs will expire. Ive written this but only the first part works IF({SLA}=“same day”, DATEADD({Opened Date}, 1, ‘DAYS’), DATEADD({Opened Date}, 60, ‘DAYS’)) IF(SLA=“A 4 Hour”,(DATEADD({Opened Date}, 4, ‘hours’,IF(SLA=“P 10 Days”,(DATEADD({Opened Date}, 10, ‘days’)))))) Once the completion date is added I will create a formula to comfirm if the SLA is met or not. Many Thanks, Ant

I’m wondering if there’s a formula that will return the date of a specific weekday, such as Monday, relative to a set date field. Any ideas?

Hello, I am looking to write a formula to populate a field based on two separate fields using the IF AND statement. This would be Field 1 = A and Field 2 = B then insert C into the formula field. Is this possible? Thank you. Jason…

Hi, I ran the below experiment with DATETIME_DIFF. It seems the units argument only performs a simple division, but most implementations I have seen of this function always compare the actual units. Am I missing something?