Formulas

Hello, I am trying to calculate night working hours for my employees and I can't figure out the right script. Each day, each employee registers its time hours in a table through 4 fields : (Shift1-Start) ; (Shift1-End) ;  (Shift2-Start) ; (Shift 2-End) With those fields, I am able to calculate duration of work each day, and through a pivot table, I am able to calculate the sum of those hours, compared to the "theorical" hours of each week of the year, hence calculate the real OT. Most of my employees start at 6:00, but some times some of them start a little bit earlier. And sometime they have night work starting at 10 pm and ending the day after at 4 am. In this case, we register 10 pm to midnight on Day 1 and the then midnight to 4 am on Day 2.What I want to calculate now, is the hours worked at night, that means those who are worked between 9 pm and 6 am. Would you have any script in mind?  Thank you in advance! Julien. 

Need Help please.I have decided to record my workout sessions on Airtable, and I want to customize the "Set" field depending on the "Type" field. My goal is to format "Set" to duration when the "Type" is "Dead Hang". Otherwise, "Set" format will be number.I have tried this formula, but I am getting an error message saying "circular reference". Can anyone help me with this?Thank you

Hi All,How would I extract only the end of unique URLs on airtable. complete URL https://lucyshops.shopping.com/#opportunities/12345ad-2gh7-115a-07f569f605dpart I want to extract 12345ad-2gh7-115a-07f569f605dif this helps the formula required is similar on this link but doesn't work because I have a different URL Structure.Solved: Extract a part of a link using Regex - Airtable Community

Hello!I am trying to create if statements to categorize products.  For example: if the product id is 02568, it will be category a; if its 12487 it will be category b; if 25777 it will be category c, an so forth.  However, each product id number is not the same.  So, category A would have serial numbers like 03577 as well as 06554 and so forth.  Each category starts with the same first number.  First problem is trying to identify in airtable only the first number to categorize.  I tried doing this: if({Product ID #}=0, "A", IF({Product ID #}=1, "B", IF({Product ID #}=2, "C"))))However, airtable says "Sorry, there was a problem saving this field. Invalid formula. Please check your formula text."  Please help! My second issue that I'm trying to fix is for airtable is to not drop the 0 in the category A product ID.  Please help!   Thank you so much!

Hi guys,I have 2 columns in Grid view, each with date and time. One is a "Created" column, the other is a "Resolved" column.I am wanting to have a formula to put into a 3rd column / field to work out the business hours between the two other fields. However, I need to be able to specify the working hours start and finish in the formula (having the option here to have 2 shifts would be even better Ie 8-10 and 11:30-16:00), as well as have it exclude certain days of the week (Ie Tuesday, Friday, Saturday). I would also like to be able to list public holidays that it will exclude from the calculation.Thanks 🙂

So I found a formula someone else made that does exactly what I need it to do.  I need to display the dates of certain events as "week of" so I can group the event by the week and see how many posts are made during the week.  The problem with the formula is it makes the "week of" appear as Apr 3, 2023, when I need it to say 04/03/2023.  The formula is:  DATETIME_FORMAT(DATEADD(Date,1-DATETIME_FORMAT(Date,'E'),'day'),'ll') Can someone help me out?  Thanks! (Field on the left is the date of event, field on right is the "week" formula)

(Edited)Single Select=Choice AChoice BChoice CChoice DI need to automate an email when a column updates from Choice C to Choice D, which happens the least. Typically, A to D, A to C, or A to B happens, at which point I don't need an email automated. An email notification for entering a "D" view will not suffice because I need to know that it was specifically routed to D from C. I typically use Zapier or Airtable automations, but I ran into a wall with this one. ----------------------------(Original)Hello, I have a single select column titled Status. It has several choices, but I only need to know when it goes from "listed" to "sold," not when the record is updated otherwise. When it does so, I need it emailed to a certain person. I'm having a hard time with each option I've tried mainly because when it changes, it falls out of the views I have created for the automation. I turned to Zapier and Make (Integromat). Zaps were limited so I'm now working in Make. Make requires

I am looking into the possibility to migrate a Google Sheets solution, to Airtable.  One piece of functionality I was doing in Google Sheets was the below. I have a list of bookings. Each booking has a 'ValueAmount', and PaidOnI have an export of BankTransactions. This has information about Date, Description & Total of each transactionI want to be able to locate the BankTransaction automatically, for each paid booking. How this can be found is by taking the PaidOn, and giving it a time window of 7 days, and looking for a transaction matching the exact Value amount. As a condition this would be like: Booking.MatchingBankTransaction = (where Transaction.Value = Booking.ValueAmount and Transaction.TransactionDate >= Booking.PaidOn and Transaction.TransactionDate <= Booking.PaidOn + 7 days.The reason for the 7 days, is because the bank can show transactions later. Using the FILTER function in Google Sheets, I am able to automatically match this with just a formula.

Hi all,Very new to Airtable, needing some help setting up a template.I've attached a screenshot for reference.I am wanting to use Airtable to keep an RFI (request for information) log which can be shared with my clients on a "base" by "base" basis. I will be the only Creator (for the foreseeable future) and will make my clients Editors so they can input their responses into fields left unlocked to Editors. The view layout will be the Grid view.I plan on creating a base per project and inside the base having the 1st column as the RFI question for the client (as a multi-line text box), the second as a selection of either "Outstanding" or "Resolved" (per row / RFI). Both these 1st 2x columns will only be able to be edited by myself as the Creator. Each RFI will have its own row.The 3rd column I will have as the Client's response, this will be a multi-line text field. The 4th column will be for the client to upload any attachments. The 5th column will be a "checkbox", for the Client to sel

Hello, I have this formula, but the result is always in hours. Where is the error?ThanksFabio  IF( Stato = "Campagna", IF( DATETIME_DIFF(NOW(), Chiusura, 'hours') > 48, DATETIME_DIFF(NOW(), Chiusura, 'days') & " giorni", IF( DATETIME_DIFF(NOW(), Chiusura, 'hours') < 48, DATETIME_DIFF(NOW(), Chiusura, 'hours') & " ore", IF( DATETIME_DIFF(NOW(), Chiusura, 'hours') < 8, DATETIME_DIFF(NOW(), Chiusura, 'minutes') & " minuti" ) ) ) )  

Hello how can I use subtraction in Airtable? I do not see Formula in my Airtable. Please help with screen shots if possible to sort out my requirements. Thanks 

I built a task management table that tracks, start, due, and completion dates.The start date is logged by an AT automation that pulls from a field called Todays Date. That field is set to PDT in the field options.The due date pulls a unique SLA from a field using this formula: {Start Date},DATETIME_FORMAT(SET_TIMEZONE(WORKDAY({Start Date},SLA),'America/Los_Angeles'),'MM/DD/YYYY h:mm A'),"")) The complete date is logged using the same automation method for start date.Where I'm encountering an issue, is how early or late a task was completed. So duration of time before or after the due date. I've tried a few different methods, but I feel like this one should work: DATETIME_DIFF({Complete Date},{Due Date},'hours') The DATETIME_DIFF formula produces wildly inaccurate results. I've tried troubleshooting by adding an hours adjustment for GMT to PDT, or adjusting the units of measure down to seconds. The time difference inaccuracy doesn't seem to follow predictab

I've been banging my head on this one for a while so any outside ideas would be more than welcome!We have three tables.Inventory: contains list of materials and their initial amounts. Each material is treated as a batch material so once it runs out, it's permanently out. Recipes: As the name suggests, contains recipes that can contain multiple ingredients. For each ingredient amount (or rather ratio of ingredients...) and few other details are given. Lookup fields bring some info from inventory for each raw material.Production mix: A right recipe is chosen and how much total material is wanted to be produced. Formula fields then calculate how much of each ingredient is needed. lookup fields bring the names of raw materials from recipe tables.My problem is that rollup does not seem to work over three tables . Basically I would need to take the information of how much of each ingredient the production mix uses and then get the info back to the inventory row for each ingredient. Howe

Heya,just a Formula newb here 🙂 Im Trying to set automaticly the Date 3 day before. When i set a Date manualy in a Field, another one should display a date 3 days before excluding weekends.I just only managed 3 days before but weekends are still included.Here my formula (Datefield is my manual selected field)Formula:  DATEADD(DATEFIELD, -3, "day")      Thanks for any advice!

I have 2 tables.Table 1 contains column ATable 2 contains column BNow, I want to check if the text appears in Table 1 also appears in a specific cell in Table 2.For example: Table 1   Table2text         text, text6, text99text3text4So in this example I will get a TRUE only for 'text' since it's located on Table 2

Hi, sorry in advance if this question has already been answered.I would like to compare two sets of date from different table, in the Table A I have a field with single select date that i want to compare with the Table B with multiple select, if in the Table B one of the data is the same I want to show the name of the person. If you need a context, I want to see the mentors that can help a student from a field that they choose, the mentors have given us many area of expertise and the student just one area of expertise, so from those two datas i want to do the matching and see the name of the mentors that can help this student.Thanks in advance!

Hi,I'd like to ask people more advanced than me to see if there is a way to do this.I have 2 tables: a Contact one and an Articles one.Each one has a multiselect field that hold the same data: Contacts select their interest (i.e. Sport, Tourism, etc) and Articles are tagged based on their topic (Sport, Tourism, etc).What I would like is to find the Articles for each Contact: a match would be considered if at least one of the Contact's interest matches at least one of the Article topics.I found a way to do this via code in a script, but scripts have limitations and I have around 5k contacts and around 10-20 articles to search for. Ideally I would like to do this with formulas or linked records or something in the UI.Otherwise I'll just resort to Make.com.Let me know if you have any suggestions or ideas!Thanks,Stefano

Question, I need a filed called START to pull from different fields that have dates:For some reason only the Game Development date is being pulled, any idea's? SWITCH({Stage},IF({Stage}='READY FOR GAME DEVELOPMENT',DATEADD({GAME DEVELOPMENT},'','DAYS',IF({Stage}='READY FOR CREATIVE DESIGN',DATEADD({CREATIVE DESIGN},'','DAYS',IF({Stage}='READY FOR PRODUCTION',DATEADD({PRODUCTION},'','DAYS')))))))

I have a field that shows the week of the year, but I break my records up sometimes by leaving some fields blank. How can I get rid of the #ERROR! message that is shown whenever there is no date (or week) in that field?Here is the formula I use: DATETIME_FORMAT({Date to be Posted},"w")

Hi everyone,I'm trying to solve a workflow/formula problem I've run into.Goal: I am building a task tracker, and it's important we have a history of steps already completed for each task. I have built a table that lists the tasks, and one of the columns is for "Most Recent Update," where team members can enter a description of the current status of the task (e.g., Jon talked to Kathy, and they want to present it during the next meeting).Issue: I want to somehow automate the transfer of any new/edited text in this field to an ongoing list of all the previous "Most Recent Update" edits. In my head, this would be a bulleted list in another column that has a bullet added to it every time the "Most Recent Update" column for that task is updated. I don't want to just have a column with an ongoing bulleted list that folks edit as the interface version of this table needs to be user friendly, and I want to be able to show just the current "Most Recent Update" text without the history in some i

Hello everyone,Looking for some help on a problem I'm trying to resolve in Airtable. Context and goal of the databaseI'm building a database of our members. The first table lists our members and their level of membership and the second table lists the training courses we offer.Back on the first table, I then inserted a lookup column to say which training course the member has taken. So far so good, everything works.Here is how it looks like so far (sorry, all in french) : Our end goal is to send to each member, at the end of the year (when it's time to renew their membership), a list of all our trainings, and for each one, show them which ones they took part of or not. If they took part in the training, it would say "Yes" and if not, it would say "No". We plan on using the page designer extension to build that report.To do that, I added a lookup column that lists all activities offered so I can insert them in the "page designer" extension. Here is how it looks at this point (

Hi,I am trying to check if the Cell "vB" has a value or not using  IF({vB} != BLANK(),"true","false"). But strangely, even blank cells are spitting out the value "true". No clue why. Can anyone help?

Hello Community,I have tried to find this answer but come up with nothing so far.I wish to create an If Statement based off a Link Field which contains words.Below is my issue:'Proposal Type' is the Linked Field which can have multiple different word options.My objective is if 'Locking' is selected in the 'Proposal Type' field, return the value 0, otherwise return the Minimum Freight Total. (i.e. locking doesn't include freight).The formula I believe should work is:IF({Proposal Type}='Locking',0,{Helper Minimum Freight Total}) However regardless of the Proposal Type, the value is always returning "Helper Minimum Freight Total"   Any help would be greatly appreciated. Thanks Paul 

Hi, I have a database for a marketplace and I want to calculate average rating for each service. I have a table called Services, which lists each service, and there is a column called Reviews, which links to a table called Reviews with a primary column that has the formula for listing the number of stars numerically (rating). How would I get the average rating for each Service and have that as a column in Services?

I am hoping there is a formula to achieve this... but not sure. I want to automatically generate the formatting that is in the "IDEAL" field on the right, that way this does not have to be done manually.Any help/thoughts would be appreciated!