Formulas

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 reliable solution, even with ChatGPT. Can anyone help?

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 wasn't able to handle the partial hours on the same day. Thanks in a

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 IF({days of SL before limit} >= 120, 120, IF({days of SL before limit} < 120, {day

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 June 30th. If you have a different year end, change the ‘06-30’ in the Fiscal Year formula. I then hide the Year field and group by fiscal years. It is great for seeing historical trends.

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 elementum sollicitudin. Morbi sit amet justo purus. Phasellus interdum eros eu

I am trying to write a formula to return the name of the column for each rows highest value. Attached is what my table looks like. So for example the first row should return blue, and the second row should return purple. Thank you!

Hello, I have a multi select field, with 5 different options that by person you can select 1 to all 5 items for.  I am trying to create a formula where it looks at all the people (say 20 people) and sees out of all of them how many have X as an option selected from this multi select field.  For Example:Person 1: Has XPerson 2: Has X,YPerson 3: Has YSo then If I am trying to show what the percentage is of people who know X, it would output _% Does this make sense?  Is this possible?

Hi, I'm trying to calculate the number of days until a date, however the result isn't correct.I think using the term TODAY is causing issues due to the difference of my time zone compared to Airtable's. Is there a way to add the time zone into the formula, or can I add/subtract days somehow?Here's the formula I am using;  DATETIME_DIFF( Date, TODAY(), 'days' )   

Hi guys! I was wondering if you could help me figure out why I get those texts in the progress bar field? How do I get the bar to appear at 0%?

Hi everybody,Let's say I've got 2 tables linked to one another (NUMBERS and OBJECTS)Table 1: 100 NUMBERS ranked from 1-100Table 2: OBJECTS which are randomly linked to these NUMBERS OBJECT A is linked with 6 random NUMBERS: 5, 6, 7, 11, 12, 13In OBJECT's there's a field {ranking} that says 11My question...In OBJECTS I would like to create a rollup field that shows me all the NUMBERS ranked higher then 11 according to the {ranking} field in OBJECTSIt can't be done with the "regular" condintional settings. So I tried to include a formula at the bottom of the Rollup field: IF(value > {ranking},ARRAYJOIN(values, ", ")) I was hoping to see:  12, 13 but i got #ERROR instead... Is there a way to include logical operators and field values within the formula of a Rollup field?Also... working with text fields instead of numeric fields leads to a new problem. Thats only works when the NUMBERS are linked in the right order. For instance if OBJECT A is linked like this: 7, 11

Hello! In my Inventory Tracking table, I want to name each item as: #Order Date-Product-Supplier. Everything looks good in my form when I'm entering the info, with the date format as expected, but then in my "Name" function field, timezone is included as "T00:00:00.000Z" I've tried reformatting the date field, deleting and re-adding, hiding it - nothing works to remove this extraneous info... any ideas? Formula looks like: (((("#" & {Order Date}) & " - ") & Product) & " - ") & SupplierI'm brand new to AirTable and appreciate any help. Thanks so much. https://airtable.com/appog8wBGoDN5Xiet/tblQuZFbeHjodwxpi/viwWiVK7K2wJqOX2E/fldnOc6mpaSiJv9t6

Hello community,I'd like to use a field from a different table in a formula.How can I do this ?--I have a table with a list of attending people to a specific event and how much they paid.I have a table with a list of the different events and I want to display the total payments received. Marine

I have a cell containing multiple dates.I want to have a column retrieving the most recent date from this cell.Which formula can I use? Tried with Max, or even last time modified but didn't do the trick...

Hi! I noticed the following bug in airtable. The formula that I actually paste in a formula field is not getting saved . Here is a proof of this.:https://app.screencast.com/8pYl20QKMc0fM?conversation=yInYcEQeuFQHhsuAgeFxWNIf you have a solution for this please let me know. 

Hi Airtable Fans, what I am going to ask will sound crazy, but bare with me :grinning_face_with_big_eyes: Sofar, Airtable has been a fantastic ace in my sleeve to build prototype and MVP (minimum viable product) applications faster than any of my colleagues can spin off a Java Virtual Machine. Saving, malaxing and serving data with Zapier and co. is absolutely fantastic. Some of those prototypes even landed “in production” and are still in use today because rewriting them “for real” wouldn’t have a high enough return on investment. Big THANKS to the Airtable team for that! Right now, I am facing the next challenge. The next prototype I’d need to validate a business application idea with, would require some kind of user- and user-specific-data management. And I have no idea if and how I could do with with Airtable. To make it easier to understand, let’s take the idea of a todo list. How could I create a distributed, mutli-user todo-list application with Airtable? Many users can add t

Anyone have any experience with geocodeing? I’m working with Airtable and Integromat to try to create a few integrations with location data that I’m collecting in airtable. I’m looking to be able to geocode or reverse geocode any record in a table. I have a webform that will detect and capture gps coords if available, or the user can hand enter an address. When capturing the data I would like to use integromat to to geocode or rervese geocode so that the database contains both gps and physical addresses. Another thing I’m hoping to figure out is how to do route planning, which I believe is possible through the google maps api, given a selection of addresses. Does anyone have any examples or experience here they could share.

Hi,Being European but from Nordics, I want to have our dates as d.m.yyyy. This is not a built-in format in Airtable. I have therefore used DATETIME_FORMAT(SET_TIMEZONE(date, 'Finland/Helsinki'), 'D.M.YYYY') in some places where user friendliness is more important than elsewhere.The problem is, dates formatted this way do not sort correctly in an interface. Here is an example image of that:'Is this a bug in Airtable, or am I doing something wrong?TIA,Björn 

Hi everyone,I have two bases in Airtable: "Employees" and "Projects". Previously, I managed everything in a single base, but I decided to split the data for better clarity. Now, I want to fetch data from the "Employees" table in one base and automatically add it to the "Projects" table in another base using the API.In the "Projects" table, I have an "Employee" field where multiple employees can be assigned to a single project (e.g., Project 1 has Employee 1 and Employee 2). I'd like these employees to be displayed as a list.I already have an API token set up, but I'm not sure how to fetch the data and display it in the "Employee" field so that they can be added to a project. Could someone guide me on how to do this?Thanks for your help!

Hello All,I run a real estate wholesale business and I'm looking to create a new table that will basically just tally all the calls we make every day. I would essentially like to be able to just click a button inside each record when a call attempt is made and have that data go to a new table, separated by date just so we can track daily calls.Any help would be greatly appreciated.

I'm setting up a ranking and league system for our tennis club. The ranking should work according to the following rules:1 – Players in each division will be arranged in order of the number of matches they have won in the round. Win = 3 points, Loss = 1 point, Forfeited/Defaulted player = 0 points.2 – If 2 players finish with the same amount of wins, the higher position will go to the player who won the head to head match between those 2 players (if played).3 – If 3 or more players have the same amount of wins, or if 2 players have tied but have not played each other, then the positions of these players will be decided by the most matches played. If this decides 1 or more of the players positions, but still leaves 2 players equal, the positions of those 2 players will go back to the head to head result.I'm struggling with a way to automate ranking for Rule Number 2. Has anyone done something similar?Table 1 = Players: https://airtable.com/appFy71oMkN3Qrb0G/shr95h5Ingg97O3szTable 2