Formulas

Hi Guys, I need help in developing a formula to calculate days between two dates that’s falling in a particular month. For Example - I am working on task for 3 hrs a day that started on 8th Mar 2020 and continued till 12th Jun 2020. I want to calculate no. of hrs in Mar, April, May and Jun in separate columns. In excel, I can directly derive these value through this formula: =if(max(min(< Task End Date>,< Month End Date>)-Max(< Task Start Date>,< Month Start Date>,0))>0,NETWORKDAYS.intl(max(< Month Start Date>,< Task Start Date>),min(< Task End Date>,< Month End Date>),1,< List of Holidays>),0)<No. of Hours

Hi everyone. I am trying to make a formula that will take two time-only fiends (formatted as) HH:00aa and give me the total time in hours rounded to the nearest quarter. (e.g. 2.75, 1.25). This has been very difficult since all of the help I could find seemed to be geared toward Date/Time fields. Thank you ahead of time for any help you can give me!

Hi All, Im trying to create a formula field that sums all number fields or all fields that start with the letter M. I have nearly 1200 number fields and add more daily. At the moment I am adding in the field name to my formula every time i create a new one. Other collaborators don’t know how to do this and i worry it maybe missed. Any help would be very appreciated.

Hi people :grinning_face_with_big_eyes: I am trying to create a concatenate formula including a date-field. The date i the field is set to ‘european format’. But when I use it in the concatenate-formula, it gives me a different format and the hours as well… Can somebody help?

Is there any way to use the FIND function to search a field backwards (in reverse order)? In other words, I want to find the LAST occurrence of a certain letter/symbol in a field. So, let’s say I had this text in a field: cats_and_dogs_in_playground_part2_lg600125X7aspUT I want to find the position number of the FINAL underscore character so I could then use the LEFT or RIGHT functions to strip out all of the text before or after the position number of the final underscore character. The problem is that all of the fields will have a varying number of underscores, and all of the fields will have a varying number of characters both before & after the final underscore If the FIND formula enabled us to search BACKWARDS in a field, I could simply tell it to stop at the very first underscore character that it finds when searching in reverse. Or — if Airtable gave me some way to COUNT the number of underscores within the field (which would

Hi everyone, I’ve a request, my situation is : I have a table with my customers database with name, phone number, etc. I attribute to each of them a partner. And I have a table for each partner. So I would like to know if it’s possible to put each customers in the right table of the corresponding partner automatically (using only the customer’s table). Thanks a lot in advance!

I have been making some attempt to create subroutines to no avail. Would anyone have any suggestions?

I am currently trying to get by without a dedicated TIME field with no date attached. I am currently trying to convert a text field into a timefield with the following code: DATETIME_FORMAT(DATETIME_PARSE({Start Time}, ‘hh:mm’), ‘hh:mm a’) The focumula is working fine, however when I write in a time, such as 3pm, it still parses it in as 3am. I note that if I put in 1500, it correctly parses the time to 3pm. I have worked in systems where you could put in variations of (3pm, 3p, 300pm, etc) and it would always know that meant PM. Is there a way to make airtable do this, and of course the converse with AM, so that I do not have to use military time?

Hi all! I have two fields, both filled with features. I want to see which features are repeated in both fields. For example, I want a column that would tell me that ‘Feature2’ is in both columns of row 1 (record1), Feature3 and Feature4 are both in the columns of record 2 and 'Feature1 and Feature3 are also both there in record 3. Maybe a formula like: If the text in column A is similar to the text in column B, then insert it in column C? I hope this is clear! Thanks in advance for your help!

Hi All!! So I have 3 groups (A, B, C) and I have features associated with each group. To put you in context, each participant (we had 5 participants in each group) had to associate 4 features to its group. So now I have a record for each group and within that specific record, all the features that were associated to it. I can have the same features multiple times. What I want to do, is count how many times each feature was associated. For example, how many times I have ‘Feature1’ in group A. Please help!! Thank you !!

I have a number for every record in this format P01384. It starts with P00000. There are several records missing and I would like to see if there is a way to write a formula that would give me the numbers of the missing records in the sequence. Is this possible in Airtable?. I have found a way to do it exporting a .csv of the table and then, using a macro I found for Excel, find the missing numbers. A very convoluted way. Would love to just have this info in Airtable. Seems like it should be a simple thing but guess it’s not. Any guidance would be greatly appreciated.

Hello! I have built a database to record a patient’s treatment. I have shared with him a form to introduce the pills he takes… .but I still have to add up the number of tablets he takes in a day, a week or a month…. I have tried with sum and rollup formulas … BUT I DON’T KNOW HOW TO DO IT! Could you help me get it? What steps should I follow…. What can you think of? I could even share my database with some of you to help me make this column, sum, sum or rollup…. THANKS A LOT.

Hello, We have 2 Row From and To and its datetime ! How may i find the diffrent time from ( from ) until To in the Same day ? please check the image

Hi guys, Actually I want to use this tool - https://statically.io/favicons/ which automatically grabs and display favicon of a any website if I write the URL in the format - https://cdn.statically.io/favicons/domain.com For example - https://cdn.statically.io/favicons/google.com Now, I already have a formula column which has multiple domain URL as the row cell value. Now, I want to create a new column with the following cell structure as shown above. but here the domain .com should be replaced the domain URL written in that column. Now , what formula should I use to create a URL like this for different values of domain .com Any help will be

Im a little confused why this isn’t working correctly… I have {Job Date} field and a formula field with this… IF({Job Date}="","-",DATETIME_DIFF({Job Date}, TODAY(),‘minutes’)) This formula field does initially show how many minutes before my {Job Date} but it doesn’t actually count down. I assumed with this formula it would actually count down every minute with the new correct number of minutes before the {Job Date}. Am I missing something? Any ideas how to make this work? Thanks in advance!

This one has me stumped… Please help! I have two tables in my base. Table A has a field called ‘Brand URL’ Table B also has a field called ‘Brand URL’ I would like a formula field to find matches between Table A and B based on their Brand URL values. When a match is found, I would like to automatically link the records together. What’s the best way to do this? Thanks!

Another challenging one… Any advice appreciated! I have a long text field containing multiple customer reviews from Amazon, separated by strings of special characters. For example: " //Lorem ipsum lorem ipsum// //Lorem ipsum fake ipsum// //Lorem counterfeit lorem ipsum// //Lorem ipsum lorem ipsum// " I want to write a formula that will search this field for specific keywords such as “fake” and “counterfeit”. When the formula finds a match to these keywords, it will grab all the text between the special character markers wherein the keyword was found. So, for example, using the above long text value, my formula would output: " Lorem ipsum fake ipsum Lorem counterfeit lorem ipsum " Thanks for your help!

How can I edit this formula to leave the field blank if the number returned is negative? Thanks! IF({Update Cycle}=‘1’,({Days Since Update}-30), IF({Update Cycle}=‘2’,({Days Since Update}-60), IF({Update Cycle}=‘3’,({Days Since Update}-90), IF({Update Cycle}=‘4’,({Days Since Update}-120), IF({Update Cycle}=‘5’,({Days Since Update}-150), IF({Update Cycle}=‘6’,({Days Since Update}-180), IF({Update Cycle}=‘7’,({Days Since Update}-210), IF({Update Cycle}=‘8’,({Days Since Update}-240), IF({Update Cycle}=‘9’,({Days Since Update}-270), IF({Update Cycle}=‘10’,({Days Since Update}-300), IF({Update Cycle}=‘11’,({Days Since Update}-330), IF({Update Cycle}=‘12’,({Days Since Update}-360), “” ) ) ) ) ) ) ) ) ) ) ) )

This is admittedly an aesthetic concern, but I feel like it should be possible. I have a formula that tests a checkbox field, {Guild?}, to see if I owe fees for a given project to a guild I work with. If it is a guild project, then the formula should return a currency value equal to the amount received for the project, {Rec’d}, times the Cut field, which is a percentage. Currently, I have it written like so: IF({Guild?}=1, {Rec’d}*{Cut}, " ") This returns the right numbers for guild projects and leaves non-guild projects blank. Fine and good. However, I cannot use the field formatting options because the values are not all numbers. Is there a way to tell Airtable to return {Rec’d}*{Cut} as currency?

Not sure if this is possible, or if I’m even in the right topic. But essentially, I have set up a table for material cost for wood, but I want to use it mainly as a tool for giving quotes to clients. I have a material field, the dimension, unit cost, tax rate, and total cost after tax. I would like to set up the dimension field so that when I put a specific dimension in, it will automatically populate unit price and all that in the remaining fields. For example: For one project, I want to know how much it will cost me to get 100 board feet of 2x6 American Yellow Cedar. I want to put in 2x6 in my dimensions field, and have it’s unit cost appear in it’s appropriate field, and let the formula calculate the cost. Then for another project, I need to figure out how much 150 board feet of 2x4 walnut will be. So I’d select my material as walnut, put in the dimension, and then have the rest of the fields auto populate with data I’ve already put into the system. I hope this make

Hi guys, I have a problem with my base. On my base, I have 3 differents tables, bénévole = volunteer, événement = events and inscription événements = events registration. On the first, I have the list of the volunteer. For each volunters, I want that they have the first event that they have participated in Evénement A, the second in Evenement B… The Events are from the second table. On the second table, I have the list of the events with all the information about the event. On the third table, I have two view, the Grid one and the form one. With the form, the volunteer could registrate to the event (column Evenement). They can choose multiple Events. On the Grid view, I have the detail of the volunteers registrations.

Hi! I would like to use Airtable to do my accounting, but I need a formula that is way above my paygrade, maybe someone here can help? I have a (single select) column 'deposit/withdrawals" and an amount column where all amounts are positive. I’d like to have the withdrawal amounts show as negative. So I made a third column where I’d like to say “if column 1 says withdrawal, then please give me column 2 amount as -amount, if it says deposit, then please give me column 2 as +amount”. Maybe that’s not the right way to go (?) but in any case, I’d love some help if possible! Thank you!

Hi there, I have two Currency fields and a third field where I am concatenating the entered values in currency fields. But after concatenation, the format of currency disappears. eg- Col1- $12,500 Col-2- $10,200 Col 3 ( Concatenating Col1- Col2)- 12500-10200 what I am looking for is $12,500-$10,200. Is it possible? Thank You

Hi everyone, i feel like this might not be possible but thought i would give it a try. i’m building a library at my business where students can check out equipment to use at home. i will have a specific number of items and was hoping that i can have them check out the equipment using a form view. i cant seem to figure out how to build an inventory system that will stay up to date with the equipment that currently available. any thoughts or samples out there? appreciate any help on this!

Hello! I am trying to write a formula for tracking what month something was checked as billed. I want to be able to group by month billed to show income by month. Currently I use this Formula for date billed when checked. IF(Billed=1,TODAY()) This shows me the date it was billed. But for grouping purposes I would like a formula that would say when billed has been checked it will show month checked. I tried this formula IF(Billed=1,MONTH()) But it does not work, it shows #ERROR!. Is there a formula I can use to state only the month when billed is checked?