Formulas

Hi All I’ve been working on a simple time off/vacation tracker. My company has a service they use, but it is really difficult to interpret the data displayed. My company allows our time off to be fully available at your anniversary date of hire. We do not use accrual of time as some companies do. I’ve almost hit what I’m looking for, but need one more piece to button it up. I basically have two tables. 1 PTO Data holds the critical criteria - PTO types, alloted, planned, balance etc. There is a column which is linked to the 2nd table pulling in the time off dates requested Linked Calendar - is a table, but I display in a calendar view and I simply select a date in the calendar for the time off. I want the ability to change the time period of how balance is calculated off of the dates requested. Not sure of the best way to do this. Right now I have two columns…PTO Begins and PTO Ends…this is my range of when my PTO is good for the year. Any ideas what I could do when the new year rol

Hello everyone, I order to properly store geographic coordinates in Airtable, I need to create a formula to transform a field like “Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676” into “12.3614676, -1.4934866” I have tried a few formulas but I systematically failed. Anyone could give an hand on this please? I would really appreaciate. Warm regards from Burkina Faso Franck

I’d like to use the cumulative sum of one of my columns in a formula. Is that possible? Essentially, I want to use the sum of the column “material amount” divided by the column “goal” which is fixed at 3,500,000 to determine in the column “% of my goal” the ongoing percentage of my goal I am at in any given time period.

So I have an existing table where I am trying to compile a list of different business chains and their locations (both cities AND counties) I have 2 separate data lists that I am working with - a list of cities where the businesses operate, and a list of counties and each city within them. One of the columns is a multiple-select where I can choose different cities where “Business A, Business B…” have existing chains. “San Francisco, Oakland, etc.” Is there any way, either in the same cell or in an adjacent column that once I add a city “San Francisco” for example I can automatically get “San Francisco County” or “Oakland” and “Alameda County”. The maybe tricky part is that if I have multiple cities from the same county, I don’t want duplicate county names. Thanks!

Is it possible to convert integer values into ASCII characters in Formula columns? Pseudo code example: convertToChar(71) returns the char ‘G’.

I need to filter the output of a table to only include records that contain the word “attractions” in the Categories field. I know it’s not correct but here’s what I tried: If({Categories} = attractions) Can someone tell me where I’m going wrong in the code? Thank you in advance for any help.

Hi All, I’m trying to create a base for managing and tracking my time off requests. The service we use is clunky and difficult to interpret, so I want to keep track of this on my own in the event the service we use does not line up to what I requested. I’m having some difficulty on how to do a formula or at least achieve a particular end result. I have a table called Time Off Benefit I next have created a table for my time off request to include the reason, a column for Type (vacation, sick, remote in and bonus hours), Hours requested and # days (which is hours requested divided by 8 hours), start date, finish date, approved (yes no) and approval date. What I now want to do is figure out a way in another table to calculate time left for each time off type based on the benefit table for the given work year My work year is equal to date of hire…so I was hired on 3/11, therefore my time starts on 3/11 and ends on 3/10 of the next year with my time restarting on 3/11 In the Time Off Benef

I’m using this current Formula and it works fine: IF( {Total Pages} < 12, 1.67, IF( {Total Pages} < 43, 2.00, IF( {Total Pages} < 60, 3.12 ))) I’m hoping to add an element that IF( {NO CHARGE} is “yes”, then $0 What is the best way to add this…

I’m lost! I’m trying to write a formula something like IF({Duration}>=5, “1”,{Duration}>=10, “2”), so when a contestant reaches a certain number of hours, a reward automatically appears in the column. and so on… Where duration is hours. I know AT defaults to seconds and have tried versions of this on my view. Just thought I’d throw out hours if it would work maybe. I’ve tried: IF({Total Seconds Watched}>=18000, “1”,{Total Seconds Watched}>=36000, “2”) with the current config. It seems to save the formula but then 1. only displays the number “1” even if seconds are greater than 36000, and 2. does not save the formula with the , “2” - it disappears each time I save it. Help and thanks for any insights and more elegance to my clumsy formulating!

I have a multiline text field and I would like to use a formula field to take that multiline text and use the existing content but add a bullet point character in front of each new line. Is this possible? I’d like it to happen automatically without manually having to run a script Part 2 - If that works, I’d like to take the bulleted list and split it after 10 lines into a second field. Is this possible?

Hi All, I currently manage a budget on Airtable. I have a list of all of my live expenses (In a table called live expenses) which I upload daily. E.g. supplies expense which the matching GL code. I have another table called actual which I compare to the budget. I’d like to have all transaction from the live table added in to this table. For example all of the Sales income be added up and added into the actual Sales income line. Any insights would be highly appreciated. Thanks

I’m using integromat to automate some data from google sheets to airtable. In this automation flow, I’m using ({record ID} = ‘string’) formula to match the g sheet cell with the airtable record id. For the most part this worked just fine until it came across a record that also had ’ in the record. This, I presume caused an error in the formula to not match the string as it stopped after the ’ character thinking that was the end of the string. Is there an escape character I can use in this formula to solve this issue? Or is this something I have to solve in the google sheet for the matching strings?

I have an age formula that returns an “error” if no birthday is available. How do I remove the “error”

Hello all, I have a range of random numbers. For example: 7, 13, 24, 28, 32, 35, 37, 40, 42. I need a filter that filter numbers into separate section. For example, “1st section” includes 1, 3, 5, 7, 9 then the “7” above will be filtered into that “1st section” and so on. Is there anyway to do this with formula? Like if the value is “7”, it will have the “1st section” text or something similar

I have a table that is being filled via Zapier from a form. I want to be able to go into that table, manually add a name into a column, and based on the name, use an IF statement to send to one of a number of other tables. Any ideas how to do this?

I used a simple formula for column C (it is A-B), it shows up as -0 in one case and 0 for another? Why should zero have a negative sign? In my case although all 3 columns are setup to round to 2 digits, I am certain that there is no third decimal digit in either column A or B. Reason for asking is that it messes my colour codes where I’ve setup anything less than zero as red.

Hi everyone! First time posting. Appreciate your help! I need your help in writing a formula that operates like this: (Assume CATEGORY is a single select field.) If CATEGORY = A, B, C, or D, then X If CATEGORY = E, F, G, or H, then Y If CATEGORY = I, J, K, OR L, then Z I think a SWITCH formula would do the trick, but I’d like the result (X, Y or Z) to come from a single select field. What I want to avoid is having to type out X, Y or Z multiple times. For example, here’s what I have now, which works but is not efficient: SWITCH( {CATEGORY}, “A”, “X”, “B”, “X”, “C”, “X”, “D”, “X”, “E”, “Y”, “F”, “Y” ETC. ) Thoughts? Thanks!

We have done a pantry inventory, and want to make a shopping list sheet. We would like things to auto add to the shopping list when they reach a certain level of stock. So for instance, If we have 12 soy milk in pantry, we want soy milk to add to the list when we reach 6, and we want to buy 12 at a time. I am looking for a way to get a formula to do this. Is that even possible? My column headings are Item - Use by - Location - Stock - Minimum stock - Amount to buy I just need the Item and amount to buy to copy to shopping list when the minimum stock is reached. Am I dreaming?

IF(AND({Total Pages}<“50”,{Total Pages} >“43”,"$3.12"),IF(AND({Total Pages}<“12”,{Total Pages}>“1”,"$1.67")) What am I doing wrong? I’m trying to calculate postage by ranges…stating if Total pages is less than 50 and greater than 43, then $3.12, if total pages is less than 12 and greater than 1, then $1.67 and so on.

I think this can be done, might just need to add a new field! On table ‘seed packets’, I have a manual number field to log how many seeds in the original packet. On table ‘sowing log’ it calulates how many seeds I have sown by N numbers in N numbers of stations (manually logged, auto answer (I didn’t set it up so language may be wrong). On the ‘seed packets’ table I appreciate I shall need to create a new field for the answer. I want it to take off the number of seeds sown from the original packet number I am familer with formulas in excel but not here so please, if someone could give me a simple starter I’d be most grateful; or correct me if I am heading down the wrong path

Can someone help my brain wrap around this type of formula? I’m trying to get different amounts to populate based on what is selected in a different field. For example) Field 1 is State Trying to get Field 2 to calculate: if Field 1 is Missouri, then Field 2 should equal $2.00, OR If Field 1 is Illinois, then Field 2 should equal $1.00

Hi, I’m very new to Airtable. I am crafting a budget base for freelance business. I have an Income table and I am trying to only calculate tax for untaxed income. I have a multiple choice selection with options “FREELANCE” and “PAYROLL” then I have a field for calculating the tax (amount*.25) I want to be able to only calculate tax for the records that are selected FREELANCE… IF multiple select = FREELANCE then (amount*.25) IF multiple select = PAYROLL then 0 I have no idea how to go about doing this, all my attempts have failed.

I’m not quite sure how to go about creating a formula that checks an expiration date (eg date field called “Expiration Date”) against today’s date. I know this formula checks field Expiration Date against another date field called Date, but I need it to always check against the current date, not against another date field. IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),“ :warning: Expired”,“Ok”)

I don’t think I’m thinking about this the right way… I have two tables: Events: hostId, eventname, city 20, event1, helsinki 20, event2, stockholm 21, eventN, toronto Hosts: hostID, hostname, totalEvents 20, biff, count of all rows from event table where hostID = 20 21, biffette, count of all rows from event table where hostID = 21 No matter what I’ve tried to link, rollup, or count, the field in the Hosts table always shows 1. I can easily generate this using a Pivot, but I want to use that summary data in other systems that I connect via Zapier. For example, when a new event is scheduled I want to send a notification to another system to say "congrats to {host} on scheduling their {countOfEvents} event!

I’m using a single select field to note the different statuses in a production workflow. I’d like to create a field to mark the date that the record was set to a particular status, “Ready to Publish,” and keep that date persistent even when the status moves on to another stage. I started with this formula, which does return the date something is set to the correct status: IF(Status=“Ready to Publish”, TODAY(), “”) But then goes blank when the status updates to the next stage. How can I make the date persistent after the status changes? Thanks for any help.