Formulas

I’ve seen one or two topics touching on this, hoping to see if there is a workaround here. Using DATETIME DIFF appears to be rounding down the resulting unit by default. For example, calculating the number of months between 1/1/2015 and 12/31/2015 results in “11.0” with no possible means to round up to the more accurate “12” due to the result being rounded down. Is there a possible workaround for this that is capable of producing a value (maybe “11.9”?) that can then be used in conjunction with ROUNDUP to round up to the nearest whole number?

After I set up a default value for regular number field, when new records generated the field still show blank, instead of default value. Am I doing something wrong?

Hello! Do you guys know if it’s possible to create a formula that return a rate (1 to 3) IF: in the field {name field}, there are both “option1” AND “option2” = 3 in the field {name field}, there is only “option1” = 2 in the field {name field}, there are no “option1” OR “option2” = 1 I tried to use IF, FIND, AND, OR but I couldn’t do it :frowning: Thanks for support!

I am creating standard project tasks from a template and am stuck with writing a formula that creates a comma-separated text string from the names of linked records but also adds the project name to the front of each record name in order to make it a unique name. For example: my linked template records are Cat Dog Cow and the projects are Project 1, Project 2, etc. I want the resulting field to be Project 1 - Cat, Project 1 - Dog, Project 1 - Cow, and so forth. That way the “Cat” in project 1 is a unique record from the “Cat” in project 2. Any help would be greatly appreciated!

Hi all! I have a formula field that calculates a variety of numbers into an average and then translate them into star ratings, here: REPT("⭐", ROUND(AVERAGE(IF({Overall Review NUMBER not accepting 0 rating (from Reviews)}, {Overall Review NUMBER not accepting 0 rating (from Reviews)}, 0)), 0)) This works great, except when there is no star rating submitted, then the “number” is seen as NaN, and the formula spits out “#ERROR!” Can anyone help update this formula to say: if result=“#ERROR!” then leave field blank ? IF it really needs to be done in the number sequence calculation step, I’ll give you that info: My formula for creating the number sequence in “Overall Review NUMBER not accepting 0 rating (from Reviews)” from above is this: AVERAGE({Work/Life Balance}, {Pay & Benefits}, {Culture & Valu

IF({ExpireEnd},IF({ExpireEnd} = TODAY(),INT(0),INT(DATETIME_DIFF( {ExpireEnd}, TODAY(), 'days')) ), "") When “ExpireEnd” field condition is equal today formula returns nothing empty, should return 0 INT(“0”) - with quotes same effect nothing. when I put in formula just “0” - without INT() - its ok Also work fine when I use any number other than zero

Hi, struggling with a formula / best approach for calculating dates based on a condition. Fields in scope are as follows: Meeting Type = Single select option Date = Date Meeting to take place Request for information = calculated date based on meeting type selected. So if for meeting type I choose ‘x’ I want request for information to populate a date 8 days prior to the ‘date’ field (which is already populated with a date) If for meeting type I choose ‘y’ I want request for information to populate a date 11 days prior to the ‘date’ field within the request for information field. Previously the calculated dates were identical regardless of ‘meeting type’ and the ‘DATEADD’ formula worked fine. Now I need to make it conditional, I cannot seem to get an if statement formula working correctly. Any help would be greatly appreciated.

There is a floating point bug that I can only get to rear its head in a particular way. I have distilled down to this simple example. Add three particular numbers (haven’t played around with which numbers cause the problem, but these do), subtract the sum from the same number in another field and the result is displayed as zero, as it should. Put an IF statement in another field comparing the result to zero, and the comparison fails. I was able to get the IF statement to display the floating point error by displaying “ZERO” or the calculated result. See below: I have a larger base where I am adding a number of fields and comparing the result to what it should be and flagging the result if it isn’t the same. This bug is causing a number of false negatives, so a fix w

I have Australian mobiles that I need to convert to the following format: 61412826666 (note no spaces and also no plus signals) Here are the examples of mobiles I have: 0439 584 111 +61 448 196 088 4197 118 98 Is there a formula that can convert all of these to the 614… format I specified above? Thank you!

Maybe the solution is a script, but I’m really after is the time in hours between two dates, but only the business hours. So Monday - Friday, 8.30am - 12.30pm and 1.30pm - 5.30pm. just to be clear: 23/6/2022 8:30AM - 23/6/2022 5:30PM. => 8 23/6/2022 8:30AM - 24/6/2022 12:30PM => 12 23/6/2022 8:30AM - 24/6/2022 5:30PM => 16 24/6/2022 8:30AM - 27/6/2022 12:30PM => 12 23/6/2022 8:30AM - 23/6/2022 5:30PM. the result of the formula should be 8 in this case start date and end date have 8 working hours the second one has this working hour: 23/6/2022 8:30AM - 23/6/2022 12:30AM 23/6/2022 1:30PM - 23/6/2022 5:30PM 24/6/2022 8:30AM - 24/6/2022 12:30PM in total 12 hours Do you have a solution for this? Is something like Microsoft project

I have a ‘Contact’ field with a value as follows MICHAEL Ashworth I have a formula field that I want to say the following: Hi Michael, Are you ready for something exciting… (etc.) How can I get only the first name (remove everything after the space) - and then capitalise only the first letter. Thank you!

Hi, This is my current formula for an If not blank > do this OR if blank > do this: IF({Discount}, {Brand} & " Discount Code: " & {Discount} &" off -", IF(NOT(Discount), {Brand}))& " "&{Product Title} But I would like to add another option that if {column} contains “FREE TRIAL” or “FREE SAMPLE” to do the same as the first line but without the '“off -”. So far I have this: IF({Discount}="FREE TRIAL", {Brand} & " Discount Code: " & {Discount} &" - " & {Product Title}, {Brand} & " Discount Code: " & {Discount} &" off -" & {Product Title}, IF(NOT({Discount}, {Brand}& " "&{Product Title}))) But it’s glitching and I know the formula isn’t correct but hopefully, someone can please help! :grinning_face_with_big_eyes: Thank you!

Hi everyone, I’m currently working on a base for my HR using KasPer lite HT base (thanks for creating it). I’ve managed (ish) to calculate the working days minus public holidays for a specified month. That said, I want now to calculate the number of working days - public holidays - employee leave for a specific month. For example, in June, you have 21 working days, employee 1 took 4 days off in June, so employee 1 worked 17 days… But I can’t figure out how to do it. Employee leave are registered in a simple absence table with the following fields : Employee Start date End Date Leave year start Leave year end … Sorry if it’s not cristal clear, I’m a beginner with Airtable, and I do not want to go back to Excel… Thanks a lot in advance for your help.

Hi - I’m looking to do an if and statement with two dates. If between X and Y date, then label it as “A” else “B”. But I am having trouble with finding the date value equivalent in airtable. In Excel I would do: IF(AND(CELL1>=DATEVALUE(“04/01/2020”), CELL2<=DATEVALUE(“03/31/2021”)),“FY21”, " ") But I can’t find the equivilant of that in airtable. I currently have the following where timestamp = my field IF(AND(Timestamp>=DATETIME_PARSE('04/01/2020'), Timestamp<=DATETIME_PARSE('03/31/2021')),"FY21", "")

I have 10 fields in a table, and some contain numbers and the others either a letter or a symbol, in the same table I would like to have another field that sums up the values of the numbers only in that record. Is there a simple way/formula to do this? Does Airtable have an equivalent formula to excel’s SUMPRODUCT & ISNUMBER? Best Ladi

I’m trying to set up an automation that will send an email reminder at a later date to complete a task. I have set up one column with “Due Date” another as “Today” and a third as “Send”. I have set up the following formula that when “Due Date” = “Today” it should put an “X” in the “Send” column. My automation is set to send the email when the “Send” field is updated with an X in it. Here is the formula: IF({Due Date} = {Today},“X”) It did not work. I also tried: IF(IS_SAME({Due Date},TODAY()),“X”) That didn’t work either. Just to test it, I tried: IF(IS_SAME({Due Date},TODAY()),“X”,“Y”) and then all the columns filled with Y, even the one where the Due Date matched Today. ???

Ok this seems pretty simple, but I just can’t get my head around it. I am new to formulas, so get the basics, but want to move beyond. Fields are - Member, Age, Clearance, Expiry, Valid, Current Member, Needs attention. Basically, if the Valid field is ‘not valid’ and the current member is ‘yes’ then the needs attention should be ‘yes’ However, if the member is not yet 18 then clearance etc is not required. So far I have IF( Age<18,“Not Required”, IF(AND(Age>17, Clearance,Expiry), “Valid”, “Not Valid”)) But I would like it to only be valid if the Expiry is after ‘today’. I really don’t know if I am explaining it well. I have created an example table here to help

I have the following formula field that works perfectly to set the Expiry Status. The problem with having a text Expiry Status is that I cannot use it for KANBAN views (and also it does not show in cool colors!). If I had a single select field for Expiry Status, would there be a way to have a formula that can change the value of the single select field instead of the formula that outputs the status as text? If not with a formula, is there a way for Automation to change the value of a single select field based on the logic I am using in the formula? The formula for the Expiry Status field: IF({Months for expiry}=BLANK() ,BLANK() ,IF({Months for expiry}>24 ,“More than 2 years valid” ,IF({Months for expiry}>12 ,“Less than 2 years valid” ,IF({Months for expiry}>6 ,“Less than 1 year valid” ,IF({Months for expiry}>3 ,“Less than 6 months valid” ,IF({Months for expiry}<0 ,“Expired” ,“Less than 3 months v

Not sure how to properly write this out in a formula. I’ve got it working with this one formula: IF({QTY Used in Part}=1000,“ :white_check_mark: Good :white_check_mark: ”,“ :red_circle: Incorrect :red_circle: ”) I’ve tried just using a bunch of IF statements, I’ve tried to make AND/OR work to no avail (probably not doing it right), I’ve read about Switch but am unsure of how to make it work for my needs - I have no idea where to go from here. Please help.

I’m using this filterByFormula which works perfectly fine based on date, but I want it to work based on hour as well. Just keep it the same way it is with hour functionality. filterByFormula=IS_AFTER(LAST_MODIFIED_TIME(), DATETIME_PARSE("6/18/2022", 'locale')) So for example for todays date which is 6/18/2022 be able to do 6/18/2022 20:00 What is the proper format to do it?

I have the following value 2022-06-01, 2022-12-05 and I need a formula that checks whether: 2022-06-01 is after TODAY() or 2022-12-05 is after TODAY() How can I do that? Doing that with a single date (e.g: 2022-06-01) is easy, but how can I handle that for a list? Note: 2022-06-01, 2022-12-05 is a rollup value of a one-to-many relationship, I don’t think I can use the relationship to perform any kind of calculation there, though. (I don’t see how that would help)

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