Formulas

Hello, I'm creating a fairly simple base to track all of the violations for an HOA board. I'm trying to create a field to calculate the total number of times a house has gotten the same violation in the past year.The Base has 3 tables, "Homes" with address and owner, "CCRs" using 'CCR ID' as the name of the violation, and "Violations" that collects the date of the violation, the 'Home', and the 'Violation'So, I basically need to have another field that counts how many times the same 'Home' and 'Violation' are both in the same row over the past 365 days.I got this to work by using grouping. In the attached picture, the highlighted number is the number I need to get to populate in its own field. Thanks in advance for any help! 

Hi there, Is there any way I am able to sync my Airtable contacts with my iCloud contacts so all of the contacts on my Airtable will also be on my iPhone? And vice versa. Without having to do it manually of course. Let me know, thank you.

Can anyone recommend a license-free icon library compatible with Airtable Formulas and Interface Text fields? I see them used in templates, but would love to have access to a complete library.

Good afternoon! I’m working on gmail automation and inserting $ amount fields into the content of my message. I’m using the following formula and noticed that the trailing zero is missing for an amount: CONCATENATE(‘$’& ROUND({Daily Rental Fee},2)) Missing zero: How can I get the zero to appear?

I have multiple tables that deal with budgeting, and in each one I've got a similar setup. I track the total budgeted and then how much we've spent manually, then a formula determines the difference and another formula looks at that difference and determines the status of the budget. It works great 95% of the time and looks like the below.Total BudgetedTotal SpentDifferenceStatus$100,000$100,000$0Matches Budget$500,000$650,000-$150,000Over Budget$300,000$200,000$100,000Under BudgetThe problem I'm seeing

HI i want to ask some questions i have a two table Product and Category, in product i have linked to another record field with the name 'Categories' and i want to filter the record in Product table with id that i have in Category table i have tried this but it's always return empty records. https://api.airtable.com/v0/<basedid>/<tableid>?filterByFormula={Categories}='recVXBZfPO530BgxK'Do you have any solutions?

Hi,I am trying to extract text and date from subject field in gmail into two seperate fields but I can not get the formula right.The subject will be in this format  "16610 6/9"16610 should be extracted as text into seperate field and 6/9 into date field (september 9)Can anyone help?/Michael     

Hi, I am looking for some help with a formula that assigns a specific shipping box to a product based on product dimensions. I have a table for Products and Boxes. The example dimensions of 2 product are: Product 1 : 50cm x 40cm x 5cm Product 2: 80cm x 60cm x 8cm The products will be assigned to one of these two example boxes: Box Size 1: Fits products measuring (40-60cm) x (30-50cm) x (3-6cm) Box Size 2: Fits products measuring (60-90cm) x (0-70cm) x (7-9cm) I am having trouble with the IF or IF/AND formulas. Any help would be appreciated. Thank you,

Hi,I am new to Airtable and now very tech savvy in general so I'm sorry if this is an obvious question.I have a table with a list of my discovery calls and then I record when these turn into paying clients. I want to summarise how many sale I make per week and per month. When I did this in excel I created a separate sheet that had the weeks of the year listed by date and by week number. Then I set up a formula to capture all sales made in that specific week. How do I do this in Airtable?Thanks!

Hi everyone,I’m encountering some challenges with defining a rollup field that combines data from three tables in Airtable. Here’s the setup:Buildings (fields: name, etc.)Employees (fields: name, hourly_rate, etc.)Timesheet (fields: employee_name, building_name, day, hours, etc.)I want to create a view that summarizes the total salary for each employee in each building. The view should include the following fields:employee_namebuilding_nametotal_salaryThe total_salary should be calculated as follows:Employee.hourly_rate * (sum of all hours in the Timesheet where building_name matches the building in the view).Can anyone provide guidance on how to set up these rollup fields and calculations to achieve this view?Thanks in advance for your help!

I need to calculate the number of days a ticket was open, but excluding weekends.However, Airtable wont let me change my work days to be Sunday-Thursday.What formula can I use as a workaround, to count all days of the week minus every Friday and Saturday of the year?

Hoping someone can help, I saw in Airtable's 'What's New' update in July that if a formula returns "a boolean (true/false), a new formatting option lets you output the result as a checkbox instead of a number". This would be really useful for me, but I have tried multiple ways to do this and have not sound it possible. I know Airtable's checkboxes mostly use 1 for checked and 0 for unchecked, so have tried outputting formula as a 1 or 0 for true / false, but no luck (tried "1" / "0", "true" / "false" etc too). Here's the screenshots from the 'What's New' page, if anyone has had any luck with this, help would be really appreciated. Thanks Matti 

Hi all, I am attempting to populate a due date for when corrections on a document are needed to be done by.A regulatory team reviews a submitted file. When it's determined that corrections are needed that team updates a single select field named "Filed by Regulatory" to show as "Corrections Requested" and adds a date/time timestamp in a field named "Reviewed Timestamp". I'd like an additional field, which can include a time or not it doesn't really matter, that will populate when the requested corrections need to be completed by - in this case 2 business days from the date it was reviewed. This is the formula I have so far and it seems to be working to the extent that "N/A" is populating on records that corrections were not needed, but on the records where corrections were requested-i'm not getting a date, instead i'm getting "#ERROR". IF({Filed by Regulatory} = "Corrections Requested",WORKDAY({Reviewed Timestamp}, 2, Holidays),"N/

How can I automatically generate a new record ID in Airtable that continues sequentially from the last number in the existing data?

I feel like what I want to do is very simple, but honestly, I'm wasting a lot of time without succeeding. My level of incompetence is quickly reached.So, I have in my Table 1 a Date field and a field that I'll call field N. In my Table 2, there is also a Date field and a Number field.I want AirTable to find the corresponding Date in Table 2 for each record in Table 1, then return the Number field and enter it in the N field of Table 1.I've been searching for the function, but I can't find it. Does anyone know how to do this?"

Hello, I’ve been trying to work this out for hours but I’m afraid my knowledge of airtable formulas is quite nascent. I’ve got this current formula: IF( {Due}="", “”, IF( AND( DATETIME_DIFF({Due},TODAY(),‘hours’) >= 12, DATETIME_DIFF({Due},TODAY(),‘hours’) <= 48 ), “Due Soon”, IF( IS_BEFORE({Due},TODAY()), “Past Due”, IF( IS_SAME({Due},TODAY()), “Due Today”, “Upcoming” ) ) ) ) Which gives me different values depending on how much time is left in the “Due” column. I’m trying to add another variable that says that if checkbox column “Done” is ticked, disregard all the above formula and return value “Complete”. How can I go about doing this please?

I have been trying to develop a formula to output the amount of work hours between two date/time fields. This is for a base that has Time Off requests for employees.I have a field with a start date like this (9/16/2024 10:00am) and a field with an end date like this (9/28/2024 3:00pm). What is a formula that would calculate the amount of work hours between those two fields, assuming:Work hours are 8am to 5pmExclude 12pm-1pm if the span crosses that time period, since there should only be 8 hours between 8am and 5pmExclude weekendsIdeally, and I realize this is a tall order, it would exclude New Year’s Day, Independence Day, Memorial Day, Labor Day, Thanksgiving Day, and Christmas Day for say, the past 5 years and next 5 years.This has been driving me crazy for a long time, and I cannot come up with a reli

Amazon की वेबसाइट या ऐप पर ऑर्डर की स्थिति देखें। Amazon ग्राहक सेवा से संपर्क करें:0 9934 29 5505 या 0 99-05962458 "हमसे संपर्क करें" या फ़ोन के ज़रिए

Hi everyone,I'm looking to create a formula where, given a specific day of the week, and a time range, the formula will generate an output, and then write a script where it will take the output and enter it into a Linked Field box. For example, on Wednesdays between 7:20am to 8:00am, I want to generate a result that says "Period 1." 8:06am - 8:50am would be "Period 2," and so on, so forth. We have different bell schedules for different day of the weeks. And then, once it generates the result, I want to have a script run to add the "Class Period" into the empty Class Period box. Is this possible? Thanks in advance for any help that could be provided! 

Hi all, I'm fairly new to Airtable formulas so not sure if this is actually possible.I'm trying to create a base for employees to view leave balances and submit new requests. I want to be able to show the hours used between the start date and end date but only the hours on working days excluding bank holidays. A working day would be 8 hours between 9am to 5pm.If possible I'd also like to calculate that against a persons FTE % so if a person only works 4 hours per day the working hours would be from 8am to 12pm. Requested leave from the 2nd of September 9am to the 6th of September 5pm duration 40 hours. Requested leave on the 2nd of September from 9am to 11am the duration 2 hours. Requested leave who works 50% from the 2nd of September 9am to the 6th of September 5pm duration 20 hours. I have had quite a few attempts now and have managed to get it to show 40 hours but the formula

Not sure if this the right sub-forum to ask (or if this belongs to base design), but I do have a problem. Context is I am making a HR Leave Management System for my team and am trying to calculate Hospitalisation Leave. It is different from leaves like annual leaves where it is based on the years of service and that it resets every year. The way this leave is calculated is if you are entitled 2 hospitalisation leave for every month you have worked in the first year, and 4 hospitalisation leave every month after the 12th month(from the 13th month onwards), with a cap of 120 days of hospitalisation leave. I can easily count how many days each employee currently are entitled with these 2 formulae in 2 different fields: To calculate the amount of hospitalisation leave IF({Number of months} <= 12, {Number of months}*2, IF({Number of months} > 12, (({Number of months}-12)*4)+24, 0)) 120 days limit

I am trying to extract the first 6 digits from a rollup so that the main column is automatically filled with the resulting ID, but when I use this formula, I get #ERROR in the fields.

I recently had to figure out how to get the fiscal year from a date field. Surprisingly, there wasn’t an explanation and ‘copy and paste’ formula already on here (or I just couldn’t find it). Here is my take on the challenge: To use this - Make sure you have a date field to reference labeled ‘Date’. Add 2 new fields, one labeled ‘Fiscal Year’, and the other ‘Year’. Make the one labeled ‘Year’ a formula field and copy and paste the code below into the formula area. DATETIME_FORMAT({Date}, ‘YYYY’) Make the one labeled ‘Fiscal Year’ a formula field and copy and paste the code below into the formula area. IF(IS_BEFORE({Date}, DATETIME_PARSE(CONCATENATE(‘06-30’, “-”, Year))), CONCATENATE("FY ", Year), CONCATENATE("FY ", VALUE(Year)+1)) That’s it! The end of the fiscal year in this formula is Jun

Hi Airtable-Community A record is always automatically created in this table if the kick-off or planned completion date changes. At the top is always the data that was entered when the project was created. Below that, an entry is created every time something changes in the timeline.Now I would like to have an additional field that only shows the changes to the timeline.Example:Initial project timeline at creation: 01.09.24-31.12.24Change of the timeline to: 01.09.24- 30.01.25Displayed date field 01.01.25-30.01.25I would then like to display this in a timeline: At best somehow on top of each other so that you can easily see how the timeline changes.best regardsMirko

We have formulas to check character counts and they're returning with "over character count" for long text that is actually not over character count because they're adding characters for line breaks. Is there a way to adjust the formula to not include line breaks as a character?For example, this would return 338:Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce ac magna volutpat, pretium arcu non, ullamcorper erat. Donec dictum eros in tellus elementum sollicitudin. Morbi sit amet justo purus. Phasellus interdum eros eu quam euismod, sed efficitur lorem fermentum. Sed neque metus, dapibus at sapien at, pellentesque placerat ipsum. But the same text with line breaks would return 342 and if the character count formula were set to 340, would show as over cc:Lorem ipsum dolor sit amet, consectetur adipiscing elit.Fusce ac magna volutpat, pretium arcu non, ullamcorper erat.Donec dictum eros in tellus ele