Formulas

I am doing a rollup on a field that has text (2-letter US State): ARRAYJOIN(ARRAYUNIQUE(values),"\n") I am wondering if there is any way to alphabetize the result output?

Is there a wide to hide data in certain cells using a formula? I am trying to work with date data coming from Memberstack that appears in UNIX form. I have a column converting the UNIX data to readable data. DATEADD('1/1/1970',{UNIX Renewal Date from Memberstack},'seconds') I have a column for Manual renewal dates. Some people are outside the system. I have a column that merges the two. IF there is a Manual Date, use that, if not, pull the Converted UNIX renewal date. It works mostly fine. It is setting the manual renewal date back a day, which is weird, but not the end of the world. IF({Manual Renewal Date}, {Manual Renewal Date}, {Converted Renewal Date From Memberstack}) Now the issue is that the UNIX baseline of December, 31, 1969 is showing up anywhere there is a blank cell. Is there a way to get rid of that? Essentially I am looking to either have a blank cell of a simple N/A instead of the 1969 date. Screenshot attached for reference. Thanks in advance!

how can I do an if statement in a rollup? so I can check a value before the rollup is completed thanks

I’ve seen this asked a couple places on the forums, but I haven’t seen a response yet. Looking for a way to track streaks for a habit tracking base I’m building. As you can see from the screenshots, I’m using one table for habits, and one for days (called Log). I’m assuming the streak field (consecutive days done) would be on the habit table, but that it would likely have to be some kind of fancy rollup formula that references the Log table. Any ideas on how to accomplish this?

I have a “Tasks” table with a checkbox (Open OR Complete). I want to list (Rollup) all “Open tasks” in one field and “Complete tasks” in another field on a different table (Clients) What should the formula be?

I’m looking for a formula that where I can have a field (1) with a start date in, a second field where I can add a number to represent days (3 days, 4, days, 10 days etc) and then another field where the formula is to calculate the specific days before field (1). Please tell me is exists! :winking_face:

Hi All, I have a Linked Field containing multiple records. One benefit of having a Rollup Field vs a Lookup, is when constructing JSON data the Rollup Field will provide a comma for empty/null values (where a Lookup will only return an array with less elements for any empty lookup returns). However, it seems my observation is only true for a Rollup Field referring to String data, but not a Numbers field. In my example above, how can I get the Rollup to return [7, 11, ]. Or for example, for even more linked records, a Rollup to return [1, ,2, , ,3,4,5] ? I am thinking that, for a workaround I need to create a Formula Field, that contains my Number field data as a string, so that the Rollup Field can then ArrayJoin(values) the data as a string and include the null fields, and then my JSON parser script re-convert that string data back to integer? :sob: Tell me that there’s some kind of Rollup Trickery that I can employ here so that null/blank() number fields return to a Rollup.

Hi there! I am looking to add to my formula (below and attached) that when the date is blank or 0 for the field to say “due today” CURRENT FORMULA DATETIME_DIFF({SHIP}, NOW(), ‘days’) & " " & IF(DATETIME_DIFF({SHIP}, NOW(), ‘days’) > 0, “days remaining :hourglass_flowing_sand: ”, “days overdue :boom: ”)

If I have a table as follows : Date/Time. Numeric value. Gain Since Last. Usage per day Record 2. 10/1/2022 100 50 10 Record 1. 1/1/2022. 50 Is there any formulae that would calculate the two fields in italics please? Thanks in advance!

Hi! I created a field that concatenates the last name and full name in a table. I am trying to write a function that takes the 2 letters first letter of the first name and 2 last letters of the last name. But it only returns the 2 first letters of the first name. Here is the formula CONCATENATE( UPPER( LEFT( {Full Name},2), RIGHT( {Full Name},2) )) How can we make it happen ? Thank you

Hi all, I need to create a formula column to calculate price of shipping equipments. I have multiple equipment types in a single select column e.g. (20DV, 20OT, 40DV, 40HC, 40OT) For all the “20” regardless if it’s DV or OT (same for the 40) the price is the same. Therefore I need an IF statement kind of like IF Equipment contains “20”, price is … thanks in advance Matt

Hello everyone, I am trying to create a scoring system for our small team but one of the formulas I used is not working properly. Here’s the formula I used: and here’s the result: I would be grateful if someone could help me with this. Thanks in advance!

Hello! I am building out a new base for my department which runs a variety of Programs throughout the year. Thus far I have set up some dummy data (i.e the “steps” you see in the screenshot) to successfully link my tasks to projects. There is a flow to how we approach the different programs we offer in terms of our when open applications, when we contract instructors for a workshop and so on and so forth. I have it set up so that every time I create a new project - based on the project I select, the are a number of tasks that will be automatically generated. I am now hoping to make it so that when a “Program Start Date” is entered, the “Task Due Date” will auto populate a deadline for each task based on a formula that aligns with our overall event timelines. Based on some previous community posts and through general research I see that the SWITCH formula might be the right way to go. However, after trying to create and implement what I thought was a proper formula - there are no tasks

I have this formula WORKDAY({Release Date},-{weeks out in #days}) it returns the date that I need but if there is a cell with Release Date empty, it returns #ERROR. I have tried this formula IF( AND( {Release Date}, {weeks out in #days} ), DATETIME_DIFF({weeks out in #days},{Release Date},‘date’), BLANK()) it removes the #ERROR on the cells that it should, but it puts #ERROR in all of the date fields, I lose the dates that I need. I have tried this formula WORKDAY({Release Date},-{weeks out in #days}, BLANK()) this one puts #ERROR in all cells. what I need: the first formula that returns dates, but if the {Release Date} cell is empty, I want to see a blank rather than #ERROR.

Surely this is an easy formula, but I just can’t get my brain to bend this way: I am using Airtable for my timesheet for my employees. I would like to improve upon my existing “Total Hours” formula by showing “Total Hours by task” , and each task is a single select item in another field. Here’s my “Total Hours” formula currently: DATETIME_DIFF({Time Out},{Time In},‘minutes’) / 60 Field for the different tasks is aptly named: “Task” Specific Single Select is “Teaching” Please help! Thanks!

Hi All, I am looking for a Count formula to count up the different “Product SKU’s” in the a column. The SKU’s are separated by a comma

Hello, I am new here. I am also new to formulas. Have managed to work out the formula to calculate the number of 12hr shifts between two date/time fields. ROUNDUP(((DATETIME_DIFF({DepartureDate},{ArrivalDate},‘minutes’) /60 ) /12), 0) My struggle is that shifts are from 06:00 to 18:00 day shift and 18:00 to 06:00 night shift. Therefore if a start time is any time between 06:00 and 18:00 I need it to 'snapback to the default start 06:00. And the same for if the start time is any time between 18:00 and 06:00, I need it to ‘snapback’ to the default start 18:00. Bearing in mind that these are date and time fields formatted YYYY-MM-DD HH:mm. Can anybody help with the correct formula? I hope my explanation makes sense!

Depsite @Justin_Barrett’s help last time, I still cannot figure out simple regex patterns. This time, I’m combining in a roll-up year and value and am trying to extract the value based on a date. IF( AND(Date, Values), REGEX_EXTRACT( Values, "(?:" & DATETIME_FORMAT(Date,"YYYY") & " )([^,])")) For example Values: 2023 530,2022 480, Date: 2022 Result: 480 Preferably each string would be Year:Value,Year:Value… but I was afraid the double dot may me some reserved character and dropped it.

Hello, in the following formula the result is false Thanking you in advance for your return Price €699 * insurance : 1.5% = € 10.49 * amount : 2, total 20.98 - The formula gives €20.97

Just wanted to share an awesome exchange I had with the Airtable Support Staff! We have programs with different start dates that meet on different days. I wanted to count the total days since start dates that have passed so that we can have our Attendance System Account for it. This is what they said: Do you have a field in your base that indicates whether a role meets Tuesday/Fridays or Mon/Wed/Fri? If so, you could combine the formula that Kuvonne suggested in the community post you linked with a IF( statement to calculate the number of class instances that have occurred. In the example below, I created a formula that operates under the assumption that there is a field denoting ‘T/F’ for the Tuesday-Friday class and ‘M/W/F’ for the 3 day meeting schedule. If you have a Start and End date that you will be comparing, you could structure your formula like the below: IF({Meeting Cadence}=‘T/F’, SUM(DATETIME_DIFF({End Date}, {Start Date}, ‘weeks’)+ IF(WEEKDAY({End Date}) >= WEEKDAY({St

Hello everyone, Sorry to bother you, but here’s a little question for the formula. Can the selected function be used in the formula? For example: IF(argument 1 selected A, then count as xxxx, argument 2 selected B, Argument 2 Selected C then xxxx) I’ve tried a few different ways, but it didn’t work. Hope anyone can kindly give me some structure, Thank you. Best Regards, K.c

Hello, For my plant tracker I’ve been using a formula to give me an estimated date of transplanting. This has been working fine. Now I want to add a check-box, so that when I plant something in-ground and i check this, the Estimated date of transplanting box will return an N/A vallue. I got all of this to work but I have a problem with how to date is formatted. As you can see the last column has a different date format. This is the code: IF({In-ground} = 1, “N/A”, IF({Germination Date}, DATEADD({Germination Date},{Estimated days to Transplanting}+0,‘days’),IF({Estimated germination date}, DATEADD({Estimated germination date},{Estimated days to Transplanting}+0,‘days’)))) I tried to add a BLANK() after the DATEADD so I can change it in the formatting tab but it doesn’t work. I think I have to add a DATETIME_FORMAT but i’m not sure where to add it in this formula. Thanks for your help!

Hello! I have two fields. One titled: Naming Convention Help w/ this formula: SWITCH({Naming Convention},"Series",{Standard File Name (Series) Template}, "Movie",{Movies - Naming Convention}, "Awards & FYC",{Awards - Naming Convention}, "Free",{Free Series - Naming Convention}, "Series V2",{Series Naming Convention V2}, "Series V3",{Series Naming Convention V3}, {Standard File Name (Series) Template}) and another formula field titled Naming Convention Final: IF(AND({Language}&"" = "en-US", {Naming Convention}="Series V3"),{Series Naming Convention V3},{Naming Convention Help}) I’m completely stumped on this one and need to reduce the number of fields in my base, any way to combined these two fields into one?

I run a career coaching program, and I am trying to automate how we offer people the opportunity to renew their time in the program. We do this manually each month by looking at everyone’s dates and changing a single select field called “Initiate Renewal.” What I want to do, is create a formula that will continuously look at everyone’s end dates and detect when someone’s end date is next month. If their end date is next month, it would update a field “Renewal end date is next month” with YES. And this would then be a trigger we could use in a Zap to send that person to the right email automation in ActiveCampaign (to send the renewal emails). I made a mockup of what I’m trying to do. I’m totally open to better ways to do this, but this is what I’m trying to do on a very literal level :slightly_smiling_face: …

Help please! I want to create a formula that spits out the total amount of hours work less a break time for team time sheets. I’m using time and date field to record when people work and a duration field for breaks. Can anyone give me the formula to use please?