Formulas

I'm trying to write a formula that will count days between two dates (assignment_start_date and assignment_end_date) that overlap the current week, not counting weekends.First example:current date is 4/5/2024assignment_start_date =  3/25/2024assignment_end_date = 4/2/2024The result of this would be 2. As there are two days (4/1 and 4/2) that occur during the current week (3/31 - 4/6) and are in the range of 3/25 - 4/2Second example:current date is 4/5/2024assignment_start_date =  3/25/2024assignment_end_date = 4/15/2024The result of this would be 5. As there are 5 days (4/1, 4/2, 4/3, 4/4, 4/5) that occur during the current week and are in the range of 3/25 - 4/15I've tried several versions of this with ChatGPT, however, I have yet to have any luck. The closet I've gotten is the following. However, sometimes the days are correct, but sometimes they are anywhere from 1 to 3 days off.  IF( AND( assignment_end_date >= DATETIME_FORMAT(DATEADD(TODAY()

I have a switch formula setup to calculate Experience Brief Due Date and Opportunity Case Due date based on the Estimate size of the project, when an item was added during Quarterly Planning.Is there a way to add onto this formula to return the "Created" date if "When was this added to the Plan?" does not equal "Quarterly Planning"?Current Formula:IF({When was this added to the Plan?}="Quarterly Planning", SWITCH( {Estimated size},"Small  (2-4 weeks)", DATEADD({Target in market date}, "-35", "days"),"Medium (4-6 weeks)", DATEADD({Target in market date}, "-49", "days"), "Large (6-8 weeks)", DATEADD({Target in market date}, "-63", "days")))

I have a switch formula setup to calculate Experience Brief Due Date and Opportunity Case Due date based on the Estimate size of the project, however I only want these values to pull in for items that are added during Quarterly Planning (not Intake-Ad Hoc Requests)-- Is there a way to set up the Switch Formula to be conditional on the value in the far right column (when the project was added)?Here is my current switch formula:Experience Brief Due Date:SWITCH( {Estimated size},"Small  (2-4 weeks)", DATEADD({Target in market date}, "-35", "days"),"Medium (4-6 weeks)", DATEADD({Target in market date}, "-49", "days"), "Large (6-8 weeks)", DATEADD({Target in market date}, "-63", "days"))

HII face an auto-categorization issue with no solution so farI update a table with hundreds of credit card transactions, with lot of them recurring with 95% of the text string similar from a month to another month.Is there any chance there is a solution that would save me from categorizing 10 times the line 'Airtable' as 'Online Services' , or even better, that categorizing once 'Airtable November 2022' would allow to automatically categorize in the same way 'Airtable December 2022', 'Airtable January 2023', 'Airtable February 2023', etc...?Many thanks for your help

This might not be possible (especially since our nonprofit is on a free plan) but we have a number of projects that require review by our general counsel before they can finish. We have a tab with the start and end dates of each project. Our General Counsel just sent us several ranges of dates when she'll be especially busy and need more time than usual to provide her review.The base started with just the "Projects" tab, with each project's start and end date. I've created a new tab for "Compliance Challenge Dates" with a name, a start date, and an end date. I would like to create a new field in the Projects tab that:Gets the End date of the project.Subtracts three days.Uses the range of dates between three days before the end of the project and the end of the project (let's call it the "last three days of the project")Checks all "Compliance Challenge Dates" tab entries and sees if there's any overlap between the date range of any of the entries and the last three days of the project.I

I need next result - Text & Date. Date should be in format MM/DD/YYYY. But now as a result I have time also. It is not expected result. Formatting tab is not available for this case. Below I attached the formula I use. I would be grateful if somebody helps me to remove time from my formula.   

I have a table linked to itselfA Lookup field type still works fineBut a Count field type always returns the value 1 when in some cases it should be more than 1

I have a field where people can enter a birthdate. I want that to show up on a calendar view in Airtable. Unless the year is the current year, it's not showing up on the calendar. I tried using a formula field but then I can't get the formula field to show up on the calendar. How do I get it so people can enter the actual birthday say 2-6-68 but have it show up on a Feb 2024 calendar view in airtable? Also, do I need to do anything different so that in 2025 it would show up in Feb calendar view?

Hi all. I was wondering if there is a possibility to use a formula to have the quarterly date shown automatically. I want to link it with automations, to send reminders each quarter in the year.Specifically I need this function, to be reminded to collect invoices each quarter. In my case the quarters are as followed:Q1 Jan - MarQ2 Apr - JunQ3 Jul - SepQ4 Oct - DecSo the formula field should automatically show ex. March 31, 2024. With the automation that I would then add it gets triggered once this date is reached. As a result then, once March 31 was reached, the field should update to the next quarter June 30, 2024 and so on. Which should make it easier to track and remind myself/the team to collect the quarterly invoices. Hope this makes sense.

I am trying to figure out how to create a formula that would remove the text before a URL in a field.Example:Website: http://2pillarschurch.comHow do I remove the Website: portion so it just shows the URL?

Good Day, I need assistance with a formula, I would like to count the status of registered learners for a specific project. I tried the Counta formula but the numbers does not add up correctly. It shows for example 2 learners registered against the project code, but none of the learners are registered yet, therefor should show 0? COUNTA({Learner registration status},"Registered") 

Hello,I have 2 columns; 'First Name' & 'Middle Name'I have a 3rd column with this formula which extracts the first letter of the above 2 columns.SUBSTITUTE(REGEX_REPLACE(({First Name}&{Middle Name}), "[a-z]", ""), " ", "")Example: Jon Reginald Mayo = JRMHowever, I want to add a space in between each character, any ideas on how to do this?

I'm a bit stuck. &#x1f605 The end goal is that I can determine how much everyone should be paying in a house depending on the conditions of the room, the minimum price of payment, and the number of people within a room. --Information: I have 1 airtable with different tables. Table one is called: Room (https://airtable.com/appLy4v2lQcr2yxf8/shrBKTdZxtE96rQOr)Within this table, I have 7 rows with different rooms. I have another table that has different conditions where I can rank the importance/benefits of a specific room. This eventually ends up with a total number of conditions which I then have a script that makes a ranking out of that in depending on the specific rooms.Within the Table Room I also have a column that shows he number of people that stay in that room. Then I have another table called: %(https://airtable.com/appLy4v2lQcr2yxf8/shr2ZXmJPEn7Te53l)Where I have a table that only shows the percentage a specific ranking should be paying for. So if a roo

Hello all,I'll be inputting a cost figure in one column, how do I automate a calculation in another column (ideally the same table) to work out the cost price inc VAT (20%)? 

Does anyone know the formula to subtract 3 hours from date and time field but... show just the time please? This formula provides the correct answer showing date and time but... DATEADD({Departure Date & Time},-3,'hours') ...when I add this to the formula to they and show just time -- the time is completely wrong. DATETIME_FORMAT(DATEADD({Departure Date & Time},-3,'hours'),'h:mmA') My attempts have resulted in a series of #Errors. Any ideas please? Thank you.

Hey guys,I'm having trouble using a valuta like € 1.333,61 in a formula. It's being recalled from another table using a Lookup field. I've tried adding a new formula column/field and reformatting it first, before using it in a formula, but without any success so far. I keep getting the NaN error when using the value in a formula. All other fields/data in the calculation are valuta's like € 1.333,61 as well. Can you guys give me a hint? Thanks! 

I’m trying to make shortenings of company names using a regular expression, by taking taking the first letter every part of the name. Fx ‘Great Garden Centre’ → GGC I’m not sure if this is possible without a loop, but I’m also no buff at using regex. I’ve sofar I can only extract the first letter using this: REGEX_EXTRACT(Company, "\\b[a-zA-Z]") Is this possible without looping through every word?

Hi Community!I would like generate with a formula a random hex color code for each new record in a database. How can I do that?Thank you for your help!Stefan

Hey,Something super basic to do in any excel, python script etc. but how to get in in Airtable?I want to create some kind of ID for each customer. First customer has number 900100, and each new one (this is a child table) should get the number +100, so 900100, 900200 etc.Is there any way to do so? (ps autonumber is not an option as you can edit it, if something goes wrong and you add a row by accident it ruins the sequence)

How do I concatenate these two fields so they show values with not so many digits. I would like to show 2 decimal digits. 

I'm new, so apologies for any blatant errors. I'm trying to create a SKU in the table primary field using the SWITCH function based on other fields in the table. My initial try included a lengthy list of colors (all unique) and the 5-letter results (not unique) for output, and the formula failed to even load. Removing the color section, I was able to generate the appropriate results based on the other columns. However, after struggling with the color field and failing, I reset the formula to the original (no color), and now get #ERROR!. I cleaned up and sorted the color entries to perfectly match the fields, and now get #ERROR!, which is progress, I guess, as at least the formula loaded.Next, I added a lookup field for the color code I want in this table and changed the color SWITCH section to reference the lookup field, but I still get #ERROR!. Here's my new formula:SWITCH({Product Type},"Apparel", "APP","Accessories", "ACC","Blankets", "BKT","Brock", "BRK","Other", "OTH") &

Looking for some help regarding a formula. Here's a little context of what I am trying to do. I am working on importing my teams marketing budget plans into Airtable, and so far everything is working well. As a final step, and in order to avoid have two column with repeated numbers for Actual Variance to Budge and Planned Variance to Budget, I wanted to create a formula combining the two numbers and then group by that column (and hide it), so that it is always present but it doesn't need to be repeated. The look that I am going for is ($ x,xxx,xxx.xx | $ x,xxx,xxx.xx)I ran into the same issue that I found a lot of people here have faced. Once I use the currency values in a text formula, I automatically lose the "$" symbol and the number separators. The symbol (even with my limited knowledge) is easy to put back, but the separators were not. I found this solution in the forums. I can't take any credit for it because I simply do not understand it, but it seems to work for a rou

I have a rollup cell that is pulling a range of data from another sheet.  Is there a way to:1) Format these so it's easier to read : MM/DD/YY-MM/DD/YY 2) Format these so I can create a calendar view that would show these dates in a row on the calendar?Thanks!!

I am a beginner and wanted to make a few custom changes through formula’s and other customisation’s. I need help with the two doubts below. Also take reference from the two photos. 1. How do I setup the “Hours” formula field such that even if its 10 mins more than a perfect hour it will round it off to 2 hrs and so on for any more amount of time.2. In the first photo, is it possible for the “End Time” to solely be a time field Instead of Date and Time.Would be really helpful I could get some solid advice on this!

Per day, the employee works 8 hours and can be distributed in one or more projects (tasks) in this period. If he worked on only one project it would be 8 hours allocated to the project (easy), however if he worked on two projects it would be 4 hours each and so on. I need to generate a report of each person’s allocated hours by project. The challenge is that the tasks from different projects overlap for a period of days and I don’t know how to evenly subdivide those 8 hours by the amount of overlapping tasks.