Formulas

i’m looking for a way to creat a formula that takesmy “date created field” date and translates it to the following: January = 1 February = 2 March = 3 April = 4 May = 5 June = 6 July = 7 August = 8 September = 9 October = O November = N December = D and then the Year with just the 2 digits. Examples: October 12th, 2003 = O03 September 3rd, 2014 = 914 January 27th 2018 = 118 February 19th 2021 = 221

Hi all! I have a question about a formula field. In one of my bases, I have time sheets with the following fields : Start (with date) - ‘‘Début affectation’’ End (with date) - ‘‘Fin affectation’’ Lunch break (total time in minutes) - ‘‘Période temps de repas’’ Total time (in minutes) - ‘‘Temps pour feuille’’ The Total time field has the following formula : DATETIME_DIFF({Fin affectation},{Début affectation},‘minute’) I can’t figure out what to put in the formula to substract the lunch time from the overall hours. Can someone help me? :slightly_smiling_face: Thank you!

Hi all, I’m trying to calculate a date based off the launch date that doesn’t exclude weekends (sat/sun) and holidays. I’ve figured out this formula that works but it’s not excluding weekends. Is there a way to exclude weekend dates from this formula below? DATETIME_FORMAT(DATEADD({Go-Live Date}, -8, ‘days’), ‘MM/DD/YY’) Thanks in advance! Bri

Hi Community, I want to solve my problem alone, but complicated for me, your help is welcome :slightly_smiling_face: I have a multiple text column with differents options I need to convert this text into table text : [“Bathroom”, “Living Room”]. Can you help me to have the good formula please ? Thank’s for your help. Matthieu

I have this formula to pull the time from a date/time column… DATETIME_FORMAT(SET_TIMEZONE({Submitted at}, ‘America/Chicago’),‘h:mm’) Is there a way to have AirTable recognize this as a number?

I’m trying to create an IF statement that would return a different result even if something matches the original criteria. I have Client ABC that doesn’t fit into the regular payment schedule, so I want the value “123” to be put as their payment and the have the rest of the clients calculated based on the formula. The problem is that Client ABC has values in each of the computed fields, so the value of those fields is being returned instead of 123. I’ve tried a variety of things without any luck. Thanks for anyone who can help! If this: IF({Client Name}=“ABC”),123) If not: IF({Payment Schedule}=“Annual”,IF({Contract Length}>=36, 5*{Field 1})) &IF({Payment Schedule}=“Annual”,IF({Contract Length}=24, 4*{Field 1}))

Hi Everyone, I am trying to build a simple PTO tracker for my team. I have my list of employees and I would like to add a column with a formula that create 1 PTO day per month but I dont have clue on how to do it. Any help on this please? Thanks!

Hi, I am trying to work out a way to make 3 tables to reference one another but can’t get it to work. I have 3 tables; People Planner Key Dates The ‘People’ table is a list of key people, each of which are tied into group (numbered 1 - 12). As a side note this is a single select as I want to be able to move the people around into different groups as I need using a Kanban view. The ‘Planner’ table has a Link to ‘People’ and pulls across various fields which are relevant to the planning of my project. At this point all is fine, everything updates accordingly in the planner table e.g. if I move person A to another group within the ‘People’ table that field automatically updates in the ‘Planner’ table. I have done this by having a formula in my primary field in ‘Planner’ which references the linked group field (the 1 - 12 I mention earlier) from ‘People’. The problem I have is Key Dates. Again they are organised by group (1 - 12)

Hi all, Quick one. I’m trying to build out a timeplanner to estimate when tasks are estimated to finish based on a start date/hour, and an estimated number of hours to complete. The formula I have so far distills down to days to make it compatible with the workday() function, but the function doesn’t seem to accept anything after the decimal point. i.e. 1.5 days as an input only increments by 1 day, only the whole integer value is used. Is there something I’m missing here, or is there another way to configure to correct this behaviour? Thanks in advance.

Hello :slightly_smiling_face: I have this formula to calculate Manpower/Days/Hours and a Rate into a dollar amount here: IF({MP/Day} > 0, {MP/Day} * Days * Hours * Rate,“0”) and it works! However it’s not being recognized by Airtable as a ## therefore I cannot get a sum, and it also doesn’t show commas when I’m using the field to generate totals in an interface. I figure I could play around with the formatting to get commas etc. but for the life of me I cannot figure out why it is not being recognized as a number that I can add up a total of? Thank you!

Hey AT Community! Is there a way to remove the tail ends from formulas in bulk? Example- I have 3 urls in 3 different rows: https://google.com/index?test https://yahoo.com/example?test https://apple.com/ending?test Using a formula (or something else) I’d like to get rid of the tails so that I am left with: https://google.com/ https://yahoo.com/ https://apple.com/ I feel like this should be possible? Appreciate any pointers!

Hello, I wonder if someone could perhaps help me with a formula I’m stuck on. I have a single select field with a number of products: Product 1; Product 2; Product 3; Product 4 etc. I also have a Premium (currency) field and a Commission (percentage) field. I’m looking at adding a Commission (currency) field with a formula that shows me the amount. However, some products are subject to tax at 12%, whereas others are not, and i want to get the commission amount after the tax has been deducted. How can I create this? e.g. IF Product = product 1, product 2, product 3, then (Premium * 0.88) * Commission, IF Product = Product 4, product 5, product 6, then Premium * Commission Thanks in advance

Hey folks, need some help with regex… I have the following information and I just need the first telephone number. Is there a way to extract the information from Tel to Tel-Nr? Only need the first phone number of each record

Hi there Is there a way I can tally the occurrences of a record within a column? For example, if I have a column called ‘colours’ and it has five reds, three blues and two yellows, how can I count these and disp[lay within a record (probably on another tab called ‘Calculations’)? Thank you in advance.

Hello, I am needing to color code based on two conditions. On this base, I have various fields, one of them being a multiple record selection for tutoring subjects required. From here you can select up to 5 subjects. Now I have various other fields, these have the hours that they have been tutored per subject, some can say 0 since they do not require any tutoring in those subjects. Now for the conditioned color coding, depending on the subjects they need tutoring in, I need them to meet 30 hours per subject. So if they meet 30 hours in the subject they require tutoring in I want them to turn green. Here is the tricky part, they can meet 30 hours in one subject that they require but, only 15 in the other, that would yield to the record not being marked as green since they only met 30 hours for one but not the other. Example: Student A requires tutoring in 2 subjects, she met 33 hours in one of the subjects but only 12 on the other su

Hello, I’m trying to manipulate a multi-select field in a formula and I’m having a problem. I have for example: Data1,Data2,Data3,Data4, I would like to be able to transform this data to have: Data1, Data2, Data3, Data4 I tried with SUBSTITUTE, REGEX… Can you help me ? Thanks !

I’d like a formula to remove the [1][2] instances from the column data

Formula is pulling linked input to create a unique name and for some reason, the formula is putting parenthesis around some of the linked names and not on other linked names. When I double check the linked table, the names are clean. Anyone know why this is happening and how I can get the parenthesis to not show up? See line 40 - 43 on first screen shot.

I have a table that are songs that we have worked, the artist, and other information. There are many duplicates as it is possible we work the same song and artist multilpe times. I want to create a view to show unique values only in the artist column. The way we have this set up with the API, Related records does not make sense in this instance. I know I can group by artist, but it is just not clean or prety. How can I create a view that just has unique values of a specific column?

We have the following columns in our database: Date Joined - regular date Last renewed - regular date Next Renewal - formula (IF({Last Renewed}=BLANK(),BLANK(), DATEADD({Last Renewed},12,“months”)) However, I also need to include my month-to-month memberships. How do I add it to this formula? Status of Renewal - The formula should help us calculate the Status of Renewal field as follows: IT SHOULD SHOW: “Active ” if the Renewal Date is in the future with 22 days and longer from TODAY. IT SHOULD SHOW “Close To Renewal ” if the Renewal Date is 21 days from TODAY (the current date). IT SHOULD SHOW: “Needs Renewal ” if the Renewal Date is 22 days passed and longer from TODAY (current date).

Here’s the situation. I’ve automated data coming in from a different platform that has a Single Select List. When the data comes in to Table 1, it’s an ID jargon that I need to have converted to a List Name in another Table 2 where ID to List Name is defined. I then have a lookup field in Table 1 that gets Table 2’s list name, this way it associate with the rest of the data imported, but I noticed it won’t allow me to do a lookup field as a single select so that I can use it as a kanban. Is there a workaround for this?

I use ARRAYUNIQUE in a Rollup to show the latest number for a field in another table. That field is a percentage field (I could make it numeric too, no problem) The percentage/number in the original field is possible to format neatly with 2 decmals. However the rollup ARRAYUNIQE (values) is showing all the decimals, which looks crap to be honest. But since it’s not numbers anymore, is there a way to make it look prettier? Right now my solution (which doesn’t work) was to make a new formula field and do this: "IF( {roll copy} = 0, BLANK(), "% "& ({roll copy} “show only two decimals”) ) Do you get what I’m looking for?

Hello, Looking for a method to count records in another table. In this case, I have a table with part numbers and their associated product. In another table, I want to count for each product, how many parts are in it by counting the part numbers from the first table. TYIA

I have a formula that brings in data from multiple fields, such as the attached. There are around 40 of these formulas, I would like to be able to sort the output by the date shown (highlighted portion of the formula). Is this possible without using a factorial approach as that is not scalable in reality. Looking for more of a ‘sort’ wrapping of the formula if at all possible.

Hello, I am new to using Airtable. I have a question about a formula I want to implement. I would like to be able to do an IF function to check if a phone number is already assigned to the customer. If the phone number is assigned to customer 1, I would like to check if it is also assigned to customer 2, if so, a final check of the phone number for customer 3. If the first condition is not validated, I would like to indicate “Customer1” in the result column, if the phone is assigned for “Customer1” but not for customer2, I would like the result column to indicate “Customer2” etc… If the number is not in the list, and it is not assigned to any customer, I would like the result to be “Not assigned”. The purpose of this formula is to check to which customer a phone number has already been assigned (knowing that it can be assigned to several customers). This formula would be used during an API connection to check for a defined phone numbe