Formulas

I have lots of dates that are setup as numbers in my excel sheet that I am bringing into airtable. I need to change the formatting of the dates while leaving them as numbers in the original column. Example. 20170112 YYYYDDMM I need it converted to 12-01-2017 MM-DD-YYYY I usually convert it using the following excel formula: =DATE(LEFT(CELL1,4),MID(CELL1,5,2),RIGHT(CELL1,2)) Thanks for any help you can provide.

I have an employee time log database where each employee signs in with a timestamp date field (they can adjust it if they need to). Then they log out, and an additional field calculates their hours: DATETIME_DIFF({Time out}, {Time in}, ‘minutes’)/60 This is working fine, but we’ve had a request to have the date display the day of the week, so instead of showing 1/11/18 9:30 AM, it would display Th 1/11/18 9:30 AM. It is my understanding from reading other forum articles that first I would need to change the field from a DATE field to a Forumula field, and then do something like this: DATETIME_FORMAT(NOW(DD-dd-yy ‘hh:mm-’)) I’ve tried various permutations of this but keep getting an error, so I’d like advice on the correct formula, but then would this impact the formula I referenced above that calculates the hours and minutes worked? Thank you. Otherwise, this database has been a great resource for a small business situation that previously relied

I want to convert a list of words separated by semicolons in one column to a list of words separated my commas in another column. Any Ideas on how to do this in Airtable?

Can multiple formulas be assigned to a field. I have a filed that determines the days between Estimated completion and Date started, Wanted to have the Field change color if late, if not late, or have a label stating “No assigned Date” if no dates were assigned to the task. Can this be done?

Hi I am new to Airtables and trying to create a formula that uses data from another table, is this possible?

Hello, I have a table of courses linked to a table of course runs. The course run table has many records connected to one course. The course runs table has multiple course run dates linked to a single course. I would like to calculate the next upcoming course run for a course in the courses table. I have read through the forums and found the ways that people have found the nearest date for events in the past using the max(values) function, but I can’t seem to find information how to compare all the course run dates linked to a course, to today(), and display the date nearest to today() in the future. Does anyone have advice on how to approach this problem? Thanks, Kevin

I’m wondering if there is (or could be) a way to change a formula for a particular cell or row? For example: I use airtable to track gigs for artists I represent. Typically, we take x% for every gig. In certain special cases, we may take y%. Is there a way to change the formula just for that cell to reflect the correct totals? Thanks!

OK, so, strictly speaking not a bug, but certainly an infelicity. :slightly_smiling_face: Maybe someone can step on it next time something else needs fixing in DATETIME_DIFF… For background, see this post in the Support forum. Specifying 'day ' or 'days ' rather than 'day' or 'days' produces wildly incorrect results: Thanks!

Is there a programmatic way to count columns? I am aware that COUNTA() exists, but I have to manually input the column names into the formula. Is there a way I can access the names of the columns and count the ones that contain the word “Siege” for example?

I don’t quite understand why I am seeing two different kinds of behavior from very similar ARRAYUNIQUE rollup fields. I am making a database of health issues. One table is Substances (nutrients, herbs, medications, etc), another is Issues (diseases, problems, or symptoms), and then I have a Junction table where I can select a Substance and an Issue and add in some other information (how/why they’re connected, along with references). On the Issue table, I want to be able to look at the record for an Issue and see all the Substances associated with it. So I have a Rollup set to ARRAYUNIQUE the Substances it looks up, because while I may have multiple Junction table records connecting the same Issue to the same Substance, I only want to see the Substance listed once. (For example, I may enter in many references in the Junction table for carnitine correcting exercise intolerance, but when I look up the exercise intolerance record in order to see what may be helpful to some

I’m trying to calculate dates based on two values. I have a formula that works for this: IF({Renewal type} = "Annual", DATETIME_FORMAT(DATEADD({Last register date}, 90, 'days'), "MM/DD/YYYY")) If I try to add another IF statement then it fails: IF({Renewal type} = "Annual", DATETIME_FORMAT(DATEADD({Last register date}, 90, 'days'), "MM/DD/YYYY"),IF({Renewal type} = "Semi Annual", DATETIME_FORMAT(DATEADD({Last register date}, 300, 'days'), "MM/DD/YYYY") What am I doing wrong?

Airtable doesn’t seem especially forgiving of formulas doing numerical operations when the original cell contained text. For example, I have a cell describing engine sizes of cars. They’re pasted in like the following: “1498cc”. I’d like to show the engine size in litres, which would translate to “1.5 Litres” What I’d like to do is have a formula substitute out the ‘cc’, then divide the remaining 1498 by 1000 (which would be 1.498), then round the 1.498 to 1 decimal place (1.5), and then put " Litres" at the end of it. The following formula works exactly as you’d expect in Google Sheets / Excel: =concatenate(round(substitute({Engine Size}, “cc”, “”)/1000, 1), " Litres") However, Airtable formulas doesn’t seem to be able to treat ‘1498’ as a number after the ‘cc’ has been removed.

Hi, When there is a field with a pure text value “1 + 1”, I would like to dynamically calculate it and put the result in to the other field. For example: | 1 + 1 | 2 | | 2 * 3 | 6 | Thank you.

Hi, whenever I try to reference a set of results I have in a “YIELD” column I get an error. the yield data is the result of a nested IF formula which itself references a single select field. Is there something I’m missing that means this data can’t be referenced by another formula? The Yield formula is: IF(TYPE = “Firkin”, “62”, IF(TYPE = “Pin”, “31”, IF(TYPE = “20L Keg”,“0”, IF(TYPE = “30L Keg”,“0”, IF(TYPE = “24 Case”,“24”, IF(TYPE = “20 Case”,“20”, IF(TYPE = “18 Case”,“18”, IF(TYPE = “12 Case”,“12”, IF(TYPE = “8 Case”,“8”, IF(TYPE = “6 Case”,“6”, “”)))))))))) Where TYPE is a single select field. But even if I try another formula field with YIELD*2 I get an error. Any help much appreciated!

Hi all – new to Airtable, but over the last 3 weeks have absolutely loved it and now using it for everything. It’s awesome. I need help with something that I’m not sure can be done yet. I have a CRM, with a table that is a funnel, each record being a different lead. I have a separate table that is my business dashboard: a summary of my activity. I want to have on my dashboard the number of leads I’ve contacted per month, based on the creation date of each record in the CRM Funnel. Is there a way to count all the records in a table, determine what their creation date is, and then get that single number and put it in a cell? Thanks in advance, Scott

This seems silly to me, but it appears that any percentage type field used in a calculated field is treated as a number instead of a percentage. For example: A percentage field set at 20% A currency Field set at $500 multiplied together = $10,000 Change the 20% to 0.20% and then it equals $100. Maybe this is intentional, but I cant’ figure out why.

Hello! Using the current formula: IF(DATETIME_FORMAT({Date of Outreach}, ‘DD/MM/YYYY’) = 22/07/2017, “ :white_check_mark: ”, “ :x: ”) But for some reason it doesn’t validate when the date is 22/07/2017 in Date of Outreach field… Any ideas why?

Help! How do I combine first name (column 1) and last names (column 2) in a 3rd column?

I have created a long list of concert dates in AirTable. I group the list based on artist name, and sort by concert date, so each concert date for a given artist appears chronologically in one data set. For an integration with another system, I need the last date listed for an artist to have a check box checked in the same row as that final concert date. Keep in mind also that as concert dates are announced, they get added to the list, at which point the check box for the previous last date must be unchecked, and the new final date check box should be checked. My process now is that I manually review the list and uncheck the previous last concert date, and check the updated last concert date. Any thoughts on automating this process?

Any time I have a field that is a formula then any IF function that bases it’s action off the number returned by the first formula is an error. I’ve tried adding the value tag around it… still not working. It would be something like, IF ({number column}<1, ‘0’, ‘1’) which will work, but If that formula is recorded under ‘{column 2}’ and I then say, IF’{column 2} = ‘1’, ‘yes’, ‘no’) will always be an error. How can I fix this??

So I’m using a formula in a column to pull data for another table. This is my formula: IF({Test Type} = “CAT 1”, {Date Only}) Which is basically: If the test type is CAT 1, then display the date from the ‘date only’ column. My problem is this… sometimes tests are both CAT 1 AND CAT 5, but I still need it to pull the data. So what I really need is IF test type CONTAINS CAT 1, then display the date from the ‘date only’ column. I used Fieldbook before Airtable and in that instance I was told to use a formula like this: IF({Test Type}: “CAT 1”, {Date Only}) In this case, : was used to signify CONTAINS. Is there something like that in Airtable? Thanks!!

Hi. Is it possible to set a conditional formatting with 3 results? If the result of 80 or more, the cell shows “Go” If the result is lower than 80, but higer than 50, the cell shows “Caution” If the result is lower than 50, the cell shows “Run”. Thanks

Hi! For some Uncide codepoints that are encoded as 4-byte UTF-8, such as 🌸, 🎧 and 𐄁, Airtable’s LEN formula function appears to return 2 instead of the expected 1. This is not the case for 2-byte or 3-byte UTF-8 encoded characters as far as I can tell, such as ⛄ or Đ, for which LEN will correctly return 1. I’ve created a sample Airtable here that highlights the problem. I discovered this when using LEN("⭐️⭐️⭐️⭐️⭐️") as an answer to this post where it returns 10 instead of the expected 5. To find characters to test with I used the

Multi-select fields containing a value with a " (double quote) in it yield unexpected results with formulas—essentially because Airtable seems to escape double quotes in arrays with another double quote rather than a backslash or something else. Here’s a series of formula results on a multi-select field named {multi} containing a single option with the value: 6" Bar Pull Formula: {multi} Result: “6"” Bar Pull" Formula: FIND('6" Bar Pull",{multi}) Result: 0 Formula: FIND(‘6" Bar Pull’, REPLACE(multi, FIND(’""’, multi), 2, ‘"’)) Result: 2

How do I reference a second table in a formula? I need to sum all the values of a column in a second table and then divide it by a rollup of a records associated values in that column. What I am trying to do: I have an event where all the people attending are in one table. I have another table where I have all the receipts people submitted for things they had to pay for and there’s a link between the two. (IE, attendee x paid for receipts A, B and F) What I want to do for each attendee on the attendee table is sum all the values of the receipts, divide it by the number of attendees and and subtract the total receipts that attendee already paid for. That way I can get an average cost that each person needs to pay for their “share” and automatically subtract what they’ve already paid. Thanks for any help.