Formulas

Hello everyone, :wave: I’m having some odd behaviour I can’t figure out. I have a formula rolling up invoices linked to a case. IF(Statut = "Clos et facturé",MAX(values),"Dossier ouvert") It does not work. The cell shows up empty when it should show the last invoice date. But if I try: MAX(values) It works. And if I try: IF(Statut = "Clos et facturé","test","Dossier ouvert") It works too. I’m puzzled as to why my first formula does not work. Thank you for your help, Joachim

I have two tables: Table A (i can modify it) Todo text (just a text field) Related Issue Title (linked table field) Related Issue Link (the field I’m questioning) Table B (can not create new fields) Title Description Status I know that there is a function RECORD_ID() which returns the current row’s record id. I also know that the link to the record looks like that: 'airtable.com/appxxxxxxxxx/tblxxxxxxxxx/viwxxxxxxxx/recxxxxxxxx' So I need to generate URLs to the Referenced Issue in a separate field in Table A I also can not enable the scripting app for this field because it’s disabled for security reasons by our admin :slightly_smiling_face: I expect the answer to be “it can not be done” but asking just in case…

Please look at my screenshot. The entries come in with the most recent at the bottom (date order). Is there any way to reverse the order so the most recent entry appears at the top? Thanks for any help!

Hi Community - I have a formula that performs a simple calculation by summing a series of cells. The challenge comes in that some of these cells contains text, rather than numbers. I would like the formula to ignore the text so that a total can be calculated. Currently, a ‘NaN’ is being returned. I attach an image of the cells that I am trying to calculate and the simple formula is: {1-Jul}+{4-Jul}+{4-Jul}+{5-Jul}+{6-Jul}+{7-Jul}+{8-Jul}+{11-Jul}+{12-Jul}+{13-Jul}+{14-Jul}+{15-Jul}+{18-Jul}+{19-Jul}+{20-Jul}+{21-Jul}+{22-Jul}+{25-Jul}+{26-Jul}+{27-Jul}+{28-Jul} Thanks in advance

Not sure this is possible but I can’t seem to get this to work when I add the second part. I have a formula that works this way 'If Base Rate = 0 and IF UM= “Pounds” then Weight / 2000 * Price, IF Base =0 and IF UM= “Kilograms” then Weight / 2200 * Price, IF Base Rate = 0 and IF UM = “Each” then “Price” I need to add this: 'IF Base Rate Not Equal to 0, and IF UM=“Pounds” then Set Weight/2000 * Price, IF Base Rate not eqaul to 0 and IF UM =“Kilograms” then Set Weight / 2200 * Price, If Base Rate not equal to 0 and If UM =“Each” then “Price” My Formula: IF({Base Rate } = 0,IF({UM} = “POUNDS”, ({Weight}/2000 * {Price}), IF({Base Rate UM} = “KILOGRAMS”, ({Weight}/2200 * {Price) , IF({UM} = “EACH”, {Price}, IF({Base Rate} !=0, IF({UM}= “POUNDS”, ({Set Weight}/2000 * {Price})… It computes the first part when the base rate is 0, but when the base rate is not 0, that part of the formula doesnt compute. Any insight would be appreciated.

Date fields and formula fields with dates are now formatting to center left of cell? There is no ability to adjust text placement that I can see so it now sits out of place with other cells data visually. its also annoying that some cells format top right, and others top left. Can we have formatting options for columns?

Hello everyone, I’m trying to separate firstname and lastname using formulas I found online. My issue is that some french name are like: John De Doe The first word is always the firstname, the rest is the lastname. I have this formula for firstname: IF({Nom complet}, REGEX_EXTRACT({Nom complet}, ".* ")) And I have this formula for lastname: IF({Nom complet}, REGEX_EXTRACT({Nom complet}, "\\s(.*)")) It outputs “John De” as firstname instead of “John”. and “De Doe” as lastname which is what I want. I need a formula to get first word (preferably using regex) that works with special characters. Thank you for your help, Joachim

Hello, all Airtablers, I’ve created a simple ‘Clocking In/Out’ Airtable for a colleague to track staff signing in and out. It uses a simple form where the user selects their name from a single select field and then selects either ‘In’ or ‘Out’ from another single select field and then submits the form. In the table itself, each member of staff should have an ‘In’ AND ‘Out’ record for each day with the automatically generated date and time. Now is there a way to calculate the difference on the ‘In’ and ‘Out’ times for that staff member for that day as the values are on different records?

Hello hello Airtable Fam. I have a question about formulas. So I have a “Due Date” field that I want to use as my constant to determine what Quarter of the year it’s in over 3 years. So I want a formula that will calculate: if the due date is between 1/1/2022 and 3/31/2022 that I’ll get a value of Q1 2022 OR if the due date is between 4/1/2022 and 6/30/2022 that I’ll get a value of Q2 2022 OR if the due date is between 7/1/2022 and 9/30/2022 that I’ll get a value of Q3 2022 OR if the due date is between 10/1/2022 and 12/31/2022 that I’ll get a value of Q4 2022 OR if the due date is between 1/1/2023 and 3/31/2023 that I’ll get a value of Q1 2023 OR if the due date is between 4/1/2023 and 6/30/2023 that I’ll get a value of Q2 2023 OR if the due date is between 7/1/2023 and 9/30/2023 that I’ll get a value of Q3 2023 OR if the due date is between 10/1/2023 and 12/31/2023 that I’ll get a value of Q4 2023 OR if the due date is between 1/1/2024 and 3/31/2024 that I’ll get a value of Q1 2024

Hello, I’m creating a maintenance schedule but I’m having difficulties create a formula that shows me the “due status” based on how many days ago an item was serviced. Individual item have different frequency of service: Monthly, Quarterly, Annually, and so on. I have a single select field that dictate the frequency for each type or maintenance. I was able to create a formula to calculate cost using a base cost * how often we perform it, but I’m having trouble applying it to idea I’m trying to perform. What I’m trying to do is IF an item was serviced today, and its frequency is MONTHLY, It will be “On Time” for 30 days, by the 25th to 29th day it will “Be Due Soon”, On the 30th day it will be “Due today”, and on the 31st or passing the 31st day will be “Overdue”. This is what I kind of have but it’s probably wrong.

Is there a way to format the items in the rollup field to look like this (including brackets on either side): [“Choice1”,“Choice2”,“Choice3”,“Choice4”,“Choice5”] I have this formula but it does not work in a rollup field: '["' & SUBSTITUTE( Choices, ', ', '","' ) & '"]' Then I found this sample code: IF( ARRAYJOIN(values), "- " & ARRAYJOIN(values, "\n- ") ) But I can’t figure out how to combine them without an error. IF( ARRAYUNIQUE(values), '["' & ARRAYUNIQUE(values, ', ', '",") & '"]' ) All these “,”"’’ are making me go cross eyed. Please help me! :rofl:

I have a multi-select field that has a large number of choices. I have decided it would be better to separate them into different categories (fields). Is there a way to do this? I have a master list of choices in a separate table (if that helps). These choices have a name and a description and a category. Is it possible to do something like this: Category 1 Field Look at the main select fields and find all choices. Look at the master choice list and find the choice name that matches the category. Place the choice that matches the field category into the field. I am on a free plan and have no access to paid tools. Example base for explanation purposes: Airtable Example Base - Airtable Explore the "Example Base" base on Airtable.

Hi - I’m trying to set a due date that is two weeks out from a publish date on the nearest Thursday. Essentially, everything that is set to be published in week 25, should be due the Thursday of week 23. Does anyone have any hot tips on how to formulate this one?

Hey everyone, I was wondering if there is a way to use a formula while rollingup information from a linked table that can exclude certain records that are attached to specific single selects in the table that has the information that is being rolled up? I am trying to extract insights from my projects table into another reports like table, but am running into an issue where projects that I am currently working on that aren’t fully finished with the total time it took for the project/ how much I have made from the project are figured out which are interfering with me creating acurate insight amounts. Any help is greatly appreciated, thank you!

Hello, I’m relatively new to Airtable, so I have a question if something I’m doing can be improved: I have a base where I am counting the number of checked boxes (so far so good) However, I will create a large number of bases where the task name varies and won’t be a simple Task1 etc. Is there a way to count the no. of checked boxes without referencing the Task1, but something along the lines of referencing the cells (like in excel) This way I can just duplicate the base and change the “task” name for each base as needed, without needing to change the formula all the time. But the formula would work regardless of what the task name is. Thanks!

Hello, I’m starting on airtable and I can’t find the right formula to perform an operation. I would like to extract the link in the second column IMAGE to insert it at the planned location in the third column JSON. Can someone help me with this ? Thank you :slightly_smiling_face:

Hi Community, I’m really hoping someone can help me with this, as I’ve tried so many different ways to get it to work and read so many different scenarios that don’t quite fit mine, that I’m now totally muddled and have no idea why it’s not working. I’m trying to have a formula field that looks at the date in the {Date & Time} field of an event and determines which school term or holidays (Australia) the event falls into. The end goal is to then be able to group the events by this formula column. Originally I tried a SWITCH formula, but I couldn’t figure out how to add in the DATETIME_PARSE, so I’ve changed to nested IFs, so it is quite long, but I would really, really appreciate any suggestions as to why it might not be working. Also, if the date doesn’t fall into any of the date ranges, I want the cell to be blank. Thanks so much in advance! `IF( {Date & Time}, IF( AND( IS_AFTER({Date & Time}, DATETIME_PARSE(‘06/02/22’, ‘DD/MM/YY’)), IS_BEFORE({Date & Time}, DATETIME_

Hello :slightly_smiling_face: I have created two tables, one with projects and one with tasks which link to the projects. In the projects table I have the tasks shown as well, but I’d like the tasks to show up as “Task1✅”, “Task2❌” - depending on completed or not, which will be determined in the task table. I know I can’t override a linked column, but I wanna know how I can adjust the formula in the tasks table, if I want the Task name to update according to the checkbox done/not done. I tried various formulas from a past thread (IF({Completed}, “ :heavy_check_mark: ”, “ :x: ”) & " " & {Name}) but they never seemed to work… Anyone got an idea? :slightly_smiling_face:

Notice: I am on a free plan and do not have access to any paid tools by Airtable or another company. I am trying to export airtable data in a csv. The place I am importing to cannot recognize “drop down” menu options. I need to extract about 100 dropdown menu options* and format them thusly in a “text” field ["value1", "value2"] *When I mention options I am talking about all the options in the dropdown menu, not just the ones selected and displayed. I don’t know if this is possible, but if anyone knows a workaround that would be awesome. It would save me the hassle of having to type them all out with that formatting. :dizzy_face:

So I have a multi-select field that has about 50 or so options. I want to take those options and surround them with quotation marks, separated by a comma. I tried using the SUBSTITUTE, but I couldn’t get it to work the way I wanted without duplicating the field a few times. I did something similar to below, but now I have three extra columns in my table…heh. SUBSTITUTE({Calculation},", ",",\\"") I am wanting to take this: and turn it into this: [“Pet Doll”,“Chubby”,“Child”,“Wings”,“Horns”,“Round Face”,“Pointed Ears”] Complete with brackets on each end. How would I go about doing this? I don’t know if scripting will work because I’m trying to import my airtable into another service and in order for those choices to be recognized as tags, they have to be formatted as you see above. So the field has to have that data in it. I have hundreds and hundreds of rows, so doing it by hand is not an option.

I have the following functioning formula set up to generate a MM/DD/YYYY response based on the input from a number in the {Day of Month Due} column: IF( {Day of Month Due}, DATETIME_PARSE( DATETIME_FORMAT( TODAY(), ‘M’ ) & “/” & {Day of Month Due} & “/” & DATETIME_FORMAT( TODAY(), 'YYYY' ), ‘L’ ) ) Result: My goal is to make the formula generate the following month’s due date once the due date has passed. For example: If the {Day of Month Due} is 25, then the due date is 5/25/2022. Then, once the calendar date reaches 5/26/2022, I’d like for the formula to show 6/25/2022. If anyone can give me some guidance on how to create this, that’d be great! Thanks in advance.

I have a single select field, and a rollup field which contains a formula that references the single select field. Now I remove 1 option from the single select field. The formula of the rollup field is now broken because it references this deleted select option. Airtable did not stop me from removing that select option (although it warned me about the dangers of me deleting the option). Airtable does show that the rollup formula is now broken by adding a red indicator to the field. The problem I have is that the two fields where in different tables, so it took me quite some time before I noticed the problem. So here comes the question: is there a way to get a list of broken fields in a base? Either through the web UI or through the Airtable API? That way, I could query this once in a while to verify nothing is broken.

Hi Community, I’m having trouble creating a nested if statement, where the condition is to identify two options from the field “Financial End Year Date” (single select field), and return two different values (for ‘December 31’ data - return ‘8/15/2022’, and for ‘March 31’ data - return ‘11/15/2022’, and if neither, return ‘4/30/2022’. The nested if formula I created and tried is as below: IF({Financial End Year Date}=“December 31”,“8/15”&"/"&YEAR(NOW()),{Financial End Year Date}=“March 31”,“11/15”&"/"&YEAR(NOW()),“4/30”&"/"&YEAR(NOW())) When I run this formula, the field shows either “8/15/2022” or “true”, where the “true” should instead be “11/15/2022”. For the ‘neither’ options, it returns a blank cell. What’s more confusing is, after I save the formula and click the configuration again, the formula is automatically corrected to below: IF({Financial End Year Date}=“December 31”,“8/15”&"/"&YEAR(NOW()),{Financial End Year Date}=“March 31”) Could anyo

Hey there! I’m massively hitting a wall with something that might take a script? I’m creating a Production Planner/Tracker for a YT Channel and want to have a formula that spits out the current date & time of when the {Production Status} is changed to “Post-Production” so that I have a {Post-Prod. Start Date}. That worked fine with: IF({Project Status} = "Post-Production",LAST_MODIFIED_TIME(), BLANK()) The problem comes when I have to switch the {Production Status} to “On Hold” or “Complete”. It obviously removes the {Post Prod. Start Date} Value. And in the case of “On Hold” it will then go on to replace it with a new date and time if/when the project gets set back to “Post-Production”. So my question I guess is, how do I get the formula to only run the first time around - or what other work-around is there? I hope this makes sense :open_mouth: Thanks in advance! Sharon

hello, i am trying to do some basic count using formula (i think its basic). there are 5 columns (named “name”,“rozmer1_cm”,“rozmer2_cm”,rozmer3_cm" and “vaha_kg”) what i want to do is formula, which will get those results: if all “rozmerX” < 50 and vaha_kg * 1000 <250, than its “M1” have found it to make it this way: which is working fine. BUT than i need to do if all “rozmerX” < 50 and vaha_kg * 1000 >250 and <=500, than its “M2” there are many other conditions, but i cannot go over the condition “vaha_kg*1000 >250 and <=500” have read the basics of formula, but i cannot apply it to my case (i know, its stupid). Thanks for kicking in right direction :slightly_smiling_face: