Formulas

Hi, I would like to divide my week number field by 2 in order to group time entry by pay period. Our pay period is two weeks starting on sundays. Here is the formula I use to get the week number: DATETIME_FORMAT(Date,‘w’) And here is the formula I use in the pay period field: {# semaine}/2 I get #ERROR! in all the cell of the pay period field.

Hey Everyone, Would love some help with something from the forum! I feel like this is something I should be able to do but am stuck! I have a table in a base that simply put a user enters a score from 1-5 (dragged in from a survey). The table can have multiple users and each user may enter that score multiple times eg once per week for a year. What I am actually trying to do is track a users sentiment toward something over time. So it basically the table looks like this: UserID : Date of Entry : Score User 1 : Monday : 2 User 2 : Monday : 4 User 1 : Tuesday : 3 User 2 : Tuesday : 3 User 1 : Wednesday : 4 User 2 : Wednesday : 1 User 1 : Thursday : 5 User 2 : Thursday : 1 Thats all fine but what I want to do is do is add a third column to the calc that shows a rolling average for each user sentiment over time. So in the above example User1 sentiment goes 2,3,4,5. If 5 is good and 1 is bad that means their se

I’m stumped on how to add up duration fields (segment run times from different tabs) to add up a total run time comprised of multiple segments. I’m working with 3 “segment” types here. The shortest segments, “Tab A” are linked to the medium one “Tab B” So, records from “Tab A” are added together to get a run time for some “Tab B” segments, but not all. Some “B” segments have no elements from “Tab A,” So I created a function field that adds up “Tab A” durations, along with a duration field just for segments unique to “Tab B” Our longest, “Tab C” are made up of records form both “A” and “B”. But for some reason, the total run time for “Tab C” is not adding up! What I want is a total from A-Unique TRT, B-Combined TRT and B-Unique TRT to add up to a total duration time in the “Total TRT” field. In the screenshot below, the first record should total 10:00, but it’s showing as 0:01. The formula I am using is “SUM({B-Combined TRT}+{A-Unique TRT}+{B-Unique

I have a Rollup field that takes a series of decimal percentages from another table (e.g .68%, .72%, and .55%) and then calculates the average (.65%). I have the formatting of this field as Percentage and precision of 1.00. Unfortunately, when Airtable passes that result to the 3rd party app I am using (Stacker), it includes more than 2 decimals. I want to stop the number of decimals at 2. I think I probably need to convert this percentage to a string and then concatenate it with %. Anybody know of an easy formula to do this? Or a different solution?

Hello, I have created a fuel log form using Jotform and airtable base to record the fuel usage which should match the meter reading at the physical fuel tank. There are 2 types of fuel; Diesel and Gasoline. Both are require to record the following details: Diesel Tank = This field shows the remaining fuel in the tank. Physically the fuel tank can holds 500 gallons. Diesel Start = This field shows the beginning of fuel reading. The start reading should begin with fuel tank 500 gallons = 0. Diesel End = This field require the employee to enter the ending of fuel reading after she/him done pumping the fuel into a vehicle/equipment. Diesel Total Gallons = This field shows the total gallons that’s has been pumped. (I already got the solution for this field). My problem in sample use case is — the first employee record the fuel log as following: Diesel Tank = 500 (Auto calculate/ formula) D

Hi! I’m using airtable to track my daughter’s food, medication etc. as part of her complex healthcare needs. The table’s primary field is datetime for each unique record, and I have created a formula to calculate only the date to allow me to group by day and analyze a day’s activity. The formula works for some but not all records. For some records, the date calculated is a day later than it should be. I’m thinking there is a timezone discrepancy (as the incorrect dates are later in the day and I am in GMT -0700) but I’m not sure how to address this with formulas. Current formula I’m using to pull only the date out of datetime: DATETIME_FORMAT({Date and Time},‘MMM-DD’) Screenshots of the mismatched records, and field customizations for the two fields I’m working with are below. Thanks community for any assistance you can provide!

I am using Stacker as a front end for my Airtable base. In Airtable, when I use the rollup and formula functions to calculate or show currency data that I format with precision of 1 show no decimals are showing. Also, i do the same with integer data that I format with no decimals. However, when the data is pulled into Stacker it shows the decimals for each. So the value of 54,528,713 in Airtable displays as $54,528,713.48 in Stacker and the value of 180 is Airtable displays as 180.32 in Stacker. Does anyone know of a way to fix this or use a formula (like the TRUNC function in Excel)? If so, I figure I could add a field next to each field that needs the decimal removed and use the this function to create the value with no decimals and then pull that into Stacker. Thanks for any insights.

Hi all, I need a formula for anonymizing names in a column. Instead of just hiding the column I would like to have the name anonymized in a different column like this: Matthias -> XXXXhias In this case the name could still be verified but is not traceable for third parties. Is this possible and what could a formula look like? Have a nice Weekend! :slightly_smiling_face: Dan

Hi all, thank you in advance for any help! I have 3x columns of interest in my Airtable which contains TransferWise statement information which is copied and pasted by me (from TransferWise to Airtable) on a regular basis. These columns are: {TransferWiseID} {ExchangeRate} {Amount} I also have a formula column which is: {ExchangeRate*Amount} Below is the formula that I am currently using with some success. I am happy that if {TransferWiseID} contains “BALANCE-” then 0.00 is populated into the formula column. This is working fine. Where I am stuck is, is if {TransferWiseID} contacts “CARD-” or “TRANSFER-” the formula rounds to 2 decimal places as it should, but only if it is not a round number. What I really want it to do however, is round to 2 decimal places consistently. E.g., right now, if a total is 10, then the formula colu

My first table is a list of orders with a date and a customer name that is linked to the customer table I need to display the date of the last order on the customer table.

I’ve got a column that has data like this: [{"Tags":"action"},{"Tags":"sci-fi"},{"Tags":"violence"},{"Tags":"youngadult"}] I’d like to just extract the words, “action”, “sci-fi”, “violence”, “youngadult” so I can turn this into a multi-select column. Any idea how I can use a formula or automation to do this? Thanks in advance!

Hi all, new to AirTable, and this one has me stumped! I’ve built a simple table that a client will submit to via form. He’ll make multiple records per day which will include several fields with numerical data. What I’d like to do is to be able to collate each day’s data and present it in a separate table, performing some calculations on that data. A simple example: the data-entry table has a date field and a won field (checkbox). If I have 5 records on 17/11/2020, 3 of which have the ‘won’ field checked, I’d like the summary table to have one row for the date 17/11/2020 with a formula that gives me the percentage of ‘wins’ for that day. It’s got a bit more going on than that, but that’s the general idea! K

It’s pretty simple, but I’m new to this. I need a column that changes the age with the date of birth. So I know I have a columnn called AGE which should change when the child reaches a birthday in a roster. Any help so I can copy and paste a formula would be appreciated. Thank you!

Hello Guys! I need some help here, I am using javascript fetch call to get values from airtable, I want Specific field Values using filterByFormula filter… I want only those fields where field name {TaskDate} = CurrentDate and field name {FK_UserUsageID} = PK_UserUsageID… I see many topics about filterByFormula but i can’t understand them because of the high level formulas using in them. I try code: fetch(airtableURL + baseInfo + tblSchedule + apikey+"?filterByFormula=AND({TaskDate} = “+ CurrentDate +”,{FK_UserUsageID} = “+PK_UserUsageID+”)") .then(res => res.json()) .then(res => { console.log(res) }) but response is: Object { “error”: Object { “message”: “Authentication required”, “type”: “AUTHENTICATION_REQUIRED”, }, } I also use my apikey and all that needed. Please figure out my mistakes and tell me the solution. Thanks in advance…

I am brand new to Airtable. I would like to compare a product grade to a supplier grade. A Supplier with an “A” grade can make “A”, “B”, “C”, or “D” product. A Supplier with a “B” grade can make “B”, “C”, or “D” product, and so on. I am pulling from two different tables; the product grade from a product table, and supplier grade from a supplier table. This third table would simulate a Purchase Order, where a single supplier may be making multiple products. I was able to create a formula to check if the supplier is allowed to make that part, however if I add multiple parts, the formula doesn’t work. Is there a better way to approach this? IF(AND({Product Category}=“A”,{Supplier Category}=“A”),“OK”,IF(AND({Product Category}=“A”,{Supplier Category}=“B”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“A”,{Supplier Category}=“C”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“A”,{Sup

Hi! I’m trying to backtime from our due date for some reviews. Here’s the formula, which is working fine: IF({Real Time End Date},DATEADD({Real Time End Date},-3,‘days’)) But I can’t seem to figure out how to add in the WORKDAY command so I am able to skip days off to determine the resulting deadline. Help? Thank you!

Dear all, A person, completely zero at coding here. Please help! Made a database for all the company’s contacts. Have a date field “Birthday” which is in a Date format (but with an actual year of birth) (I dont have birthdays for all people by the way, so sometimes this field is empty) Then used a formula (see below) to change the year of birth to the current one so I managed to create a calendar view for birthdays. DATETIME_PARSE( DATETIME_FORMAT( Birthday, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMMM D YYYY’ ) But now I need to make an automation that would send an email to me if there is a birthday coming 2 days from now. Ideally to look it like Reminder! “Name Surname” has a birthday on “date of birthday” I tried to use a trigger when “formule to calculate birthday” (it is a formula field) is within 2 days and action “send an email” But it doesnt seem to work: the automation only sees on

Is there a formula where I can convert a date in a date field to a string of numbers? For example, for today (2020-11-20) I would like it to return “201120”

Hi all!! I have a list of participants in a project, but as this list of participants is a lookup from several tasks linked to the project, it has duplicated items. I want to count the number of unique participants. I’ve tried transforming this list of participants into a string, I know how to count the number of participants in that string but I need to remove the duplicates. Does anybody know how to remove duplicates from a string? I know how to remove completely a participant from the string (using SUBSTITUTE), but I don’t know how to make it appear only once. SUBSTITUTE({Participants}, Find, “”) (Find is the name of the table with the name of the participant I want to remove) Thanks in advance!!!

I’m not sure the best way to ask this question, but here goes! I have the following formula… Model & " " & VAR_Color & " " & VAR_Type & " " & VAR_Weight_Name & " " & VAR_GRIP_Circumference Some of my “Models” are missing one or several of the VAR (fields) and when that happens I’m left with a double or even triple space. I want to concatenate the formula in such a way that IF a field/VAR is empty that the VAR and the space in front of it are omitted, but if a VAR exists, then the VAR and the space in front of it are displayed. Thanks in advance for any advice on how to revise/simplify this formula!

I need a formula to round seconds to the closest 15 second mark in the hh:mm:ss time format… i.e. say 1:01:10 needs to be rounded to 1:01:15 , say 0:34:20 rounded to 0:34:30, say 1:02:55 to 1:03:00 Hope someone can help as in excel I can use the MROUND formula but this is not in Airtable.

We have been running into a snag with the simple formula of 3 columns adding together and getting off by .01. This normally is not a big deal, but in a contract this can be a very big deal. I have attached the pictures of what I’m talking about and the formula I use.

I hope that this is not a topic that has been answered already but in my searching what I found is that there are several ways to filter out empty fields but in my use case I want to keep the records that are empty in certain fields. I have had success using NOT({Field Name} = " ") and some success using IF and SEARCH in various forms but they are all getting rid of the fields that are empty rather than keeping the ones that are empty. For clarity’s sake let’s say the records have a likes icecream and the people who don’t like icecream have nothing in that field. I want to be able to filter out the ones that do have something and keep only the ones that don’t. If anyone has any suggestions it would help a lot (also I am doing this via API).

Hello - I’m looking for help creating a formula that will insert quotation marks around names in a string that are separated by commas. The number of names in the string will vary. For example the string Jane Doe, John Smith would become “Jane Doe”, “John Smith”. Is there a formula that I can use to do this?

I am using an Airtable base for Billpay synced with Calendar view. I am having trouble setting the date to recurring. For example, X bill is due on 10/1/2020, I need it to sync with the calendar so that the bill appears on the 1st of every month.