Formulas

I have a base now where I track events and rooms the events occur in (not that I’m doing much of this during coronavirus). Each event is given a start date and end date. Usually 1 or 2 days. Each event is also tagged to a Room. Like say Room 1 or Room 2. What I’d like to do is essentially track room utilization over the quarter. So if I have 4 events, and each one lasts two days (so the start date is say 1/1/20 and the end date is 1/2/20) and each is tagged to a room, how would I craft a formula to total up the days per room?

I’m trying to create a custom formula, where it takes the first character in each word from another column. Is that possible?

Just noticed this. I have a timestamp field (that is, a date field that also shows time). I want to extract just the time. The result of the TIMESTR() function seems to add 8 hours to the result. For example, MyTimestamp = 01/27/2021 10:12am TIMESTR( MyTimestamp ) = 16:12:32 What’s up with that? I’m in Texas, six hours behind GMT/UTC. (It’s 5:30pm here, 11:30pm in Greenwich.) So if the time zone offset from GMT were involved here, I’d expect the result to be 14:12:32. William

Recently discovered Airtable and love what it can do as I track several things across a good amount of Google Sheets. I have been wanting to consolidate them for a long time and Airtable looks the part. I have a very long and complicated if/else statement that I need help with. I have never written script before and over the last three days I have tried to learn as much as I can about it. With that being said it is still over my head on how to accomplish what I want. I tried to do this with a very long nested if statement with no luck and after some research it looks like a script would easily do want I need. Maybe someone smarter than I can get it done with a formula instead of a script. Just not sure what is the best and easiest way to get this done. Our company pays out a year end profit share bonus based off the number of training hours the teammate(employee) completes. The basic formula is long but fairly easy to follow. Where things get complicated is if the teammate joined us

Hi all, I’m literally just starting out, but trying to make my primary field choose the correct investor (either personal or corporate). Is there a way to make this work in something like this format: IF( {Company Name}!=BLANK(), {Company Name}) ELSE( {Last Name}) The first part of the string works, but I can’t get the second part to work. Any suggestions would be greatly appreciated.

My current formula works. IF({Post Date}<{Today’s Date},‘READY’) But if {Post Date} is empty it is also saying ‘READY’ What should I add to the formula that ignores a field if it is empty.

Hi, I’m trying to come up with a formula ({FIELD} + 30 days - today’s date.) This seems to be an easy one but I’m missing something. Any ideas?

Hi everyone :wave: I’m pretty bad at Airtable but made a worklfow with Calendly and Airtable for a fitness coach to allow people to book lesson Now want to limit the number of booking (at 3) per people in a week. I don’t know want feature to use : Formula (of course), maybe Count…anything else ? Here a screenshot of my database

We have to deliver our assets to our agency 4 business prior to a launch so I’m curious to know what kind of formula I put in to create these dates.

This is handled in two tables Items to order (here I manage the status of whether the product can be shipped or not) Orders What I am looking for is to be able to have the sum of the total amount only of what is going to be shipped. Currently I have a Rolllup type field in the orders table that brings the data of the items to order that makes me the sum of everything (shipment + pending to be shipped). Is there a way to get the calculation only for those products that are marked to be shipped?

Hello, I have a text string of which I need to know how many times a word appears. For this example “Enviar” appears 3 times and “Pendiente” 4. With what function do I do this count? Thank you very much for your support

Excel has the IFERROR function to elegantly catch when a formula fails. Airtable doesn’t seem to have an equivalent. Is there a way to emulate this functionality? I have an airtable with a list of books that I’m reading. They have a start date and an end date, but I don’t write the end date until I’ve finished the book. I group books by end date and would like the group to be named “Currently reading” instead “#ERROR!”. Thank you

I have a formula field that includes a linked record field {Supplier}. The result is the text in the linked record field has quotation marks. Is there a way to remove the quotation marks from my results? DATETIME_FORMAT({Purchase Date},‘YYYY-MM-DD’) & “-” & {Supplier} 2018-03-31-“Supplier ABC - Your Town, WA - Unknown Address” Many thanks :pray:

I have created a concert info collection and attendance tracker. There are 18 performances. For each performance the students use a form to enter their name, pick the performance from a drop down, and enter a unique phrase key of the day. This feeds a table that is grouped by performance and sorted by Name. From here I take the names into another table that has a master list of students names and concert dates. For each concert date it compares the list of those that signed in against the master list of students. If a name is missing, it returns a “NO” to the date/name column. QUESTION: For every concert a student misses, they need to do a makeup paper. In excel I would put a “COUNTIF” statement "COUNTIF(C1:T1, “NO”) at the end of the row, (“Johnny, D”, “Yes”, “Yes”, “Yes”, “Yes”, “NO”, “Yes”, “Yes”, “Yes”, “NO”, “2”) so the students would be able to track how much make-up work they need to accomplish as they are going along. I have spent days looking for an answer. HELP :slightly_smi

Hi all - I’m having an issue when I try to “group by” a formula column in my spreadsheet. The issue is that it’s formatting the same date in two different ways and then treating the same date as different items and therefore splitting the group into two. This is how it’s formatting: And this is how it’s grouping: That should be one group of 57 entries. The formula is an IF formula that grabs a time from one of two columns. One column is a lookup, and the other is a date column. Is there any way to make sure they’re formatted the same way when they come out of the IF formula? Thanks in advance!

Hello All, I have a base with 50 $ fields and I want to total all of them. Is there a simple formula to do this or am I resigned to {1 Cost}+{2 Cost}+{3 Cost}+{4 Cost}+{5 Cost}+ etc…?

Hey guys, I’m hoping someone can help me - I’m such a beginner at formulas! I am creating a table to hold all my email campaign data. At the moment I’m logging all my data fields. What I need is a formula that will take a field make like ‘full name’ and turn it into a personalization tag, so the space needs to disappear, it needs to be in caps and I need a % sign at each end, so it would end up looking like this: %FULLNAME%. I don’t have the very first clue how to go about this so any help you can give me would be pathetically gratefully received. Thanks in advance! Katie x

im looking to Create a date when a status is closed ( to track when a record was closed not modified )

Hi - I would like to be able to sum the individual digits in a date - e.g, a given date = the sum of its individual digits. Some examples: Date: 7/13/1969 = 7 + 1 + 3 + 1 + 9 + 6 + 9 = 36 Date: 12/20/2019 = 1 + 2 + 2 + 0 + 2 + 0 + 1 + 9 = 17 I have some idea that I could convert the dates to text, then do a “value” of each digit, but nothing I’ve tried has worked. I’m grateful for any ideas!

Hi there I’m building a table of URL’s of locations. The Columns are: Location Name Slug Meta Title Meta description I’ve already determined the copy for my Titles and Description, but I want to include the content in the Location Name into that and then do a drag down the column to fill the rest of the entries. Does anyone know how to do this?

Hi! I’m trying to create a new field on my table that is a combination of two different Single Select fields. One column is the Likelihood of an event happen (1- Rare, 2- Unlikely, 3- Possible, 4-Likely, 5- Almost certain) and the other column is the Impact of that event happening (1-Insignificant, 2-Minor, 3-Moderate, 4-Major, 5-Catastrophic). The new column that I want to create will give me a “score” from the combination of the Likelihood and the Impact (1- Low Risk, 2-Moderate Risk, 3-High Risk, 4- Critical Risk). So for example, if the event is Almost Certain (5) and but the consequences are 1- Insignificant that will mean 1-Low Risk. Could someone give me guidance on automatizing this score? Thanks!

Right now I have a basic quarterly reporting function working in one of my bases, but I’m curious how I would improve it. I have a formula that looks at the starting date of a project and uses that info to tag it with the correct quarter and year. Great. That works fine. Where I’m a little more confused is how to then get that info consistently out onto a reporting tab. As of the moment, I’ve created a tab with each quarter of 2021, and I have an automation made for each quarter so that when a record appears with a 1Q-21, 2Q-21, 3Q-21, or 4Q-21 tag, it will be linked to the corresponding quarter record in my new tab. The issue, though, is that I would have to do this for every quarter of every year. Not the end of the world, but a little annoying for the long term. Is there any reasonable way to automate the process of creating the new quarter records for each year and automatically adding any records that should go into those quarters going forward without manually adding each quar

I’m sure I’m missing something simple here, but I’m trying to make formula that outputs the correct Quarter and then Year based on a relevant date field. So like Q1 - 2020 What I’ve come up with so far isn’t working, possibly because I’m missing something or have been a bit too confused by the nested IFs IF(OR(Month({Date Start})=1, Month({Date Start})=2, Month({Date Start})=3), “Q1”, IF(OR(Month({Date Start})=4, Month({Date Start})=5, Month({Date Start})=6), “Q2”, IF(OR(Month({Date Start})=7, Month({Date Start})=8, Month({Date Start})=9), “Q3”, IF(OR(Month({Date Start})=10, Month({Date Start})=11, Month({Date Start})=12), “Q4”," ")))) & " - " & Year({Date Start}) Any guidance is appreciated!

Hi guys im trying to utilize a formula from excel to calculate a score based off of two different fields but I cant seem to get it working. here is the formula =Total*(10^(0.751945030*((LOG10(Weightclass/175.508))^2))) Total and Weightclass are two of my fields and are set to integer inputs. any help is greatly appreciated.

Hi, i have another thread here but it changed into this problem instead. in the end i want a “age” field calculated from a “date” and i want to make a “formula” field that categories the age to groups. so I have a “date” field and a “formula” field that calculates the age in one number “15” lets say DATETIME_DIFF(TODAY(), {Date}, ‘years’) and then have another “formula” field that groups all ages 1-100 in “1-12”, “13-18”, “19-29”, “30-45”, “46-60” and “61-100” Formula found on internet but doesn’t work for me? IF( {Age} <= 12, ‘1-12’, IF( {Age} = 13, ‘13-18’, IF( {Age} = 14, ‘13-18’, IF( {Age} = 15, ‘13-18’ IF( {Age} = 16, ‘13-18’ IF( {Age} = 17, ‘13-18’ IF( {Age} = 18, ‘13-18’ IF( {Age} = 19, ‘19-29’ IF( {Age} = 20, ‘19-29’ ) ) ) and so on… then i could have a “app” with a “Chart” for: “1-12”, “13-18”, “19-29”, “30-45”, “46-60” and “61-100” right??? or use the built-in group feature and group them all.