Formulas

Hello, somewhat new to airtable and using the Payroll Timesheet Template available in the template library. However, I’m having a bit of trouble figuring out how to properly calculate the pay periods as they’re not every 15 days (like in the template), but rather the 15th and the last day of the month. It currently takes the Start Date formula DATEADD({Start of Pay Period 1},({Pay Period Number}*14)-14,'days') and the End Date formula* DATEADD({Start Date},13,'days') to calculate the Pay Date DATEADD({Start Date},13,'days') I am wondering how best to calculate the Start and End Dates based on the Pay Dates always being the 15 and the EOM (30th or 31st). I’ve been playing with different formulas all day and have figure out how to calculate

Hi,I have a base with a lot of records being submitted that have dates attached such as events. I want to be able to show the next 20 records coming up, keeping in mind this will change any time a new record is created with an upcoming date in the next day or two. So that might make record number 5 in the sequence be pushed out to record number 6 since a new once came in. But I still just want to present 20 records at any point in time.Thanks!

HI All! I'm trying to create a formula but am stuck. A $100 product minus 5%.That total minus 25%.That full total minus $25.For an end total of $46.25Here's what I have as a formula, but it's just not working out! I can't tell if it's the parenthesis or the actual 25%.((({Product}-({Product}*.05))-(25%))-{COGS}) Any help is appreciated!  Thanks

Hello I wanted to create a base that will auto populate Week 1, Week 2, Week 3, and so on for a number of date, Date is linked to another base which is synced to that base with a view.The purpose of this is we have a product that has a weekly interactive virtual sessions that isn't yet finished planned out so instead of putting titles of the event I wanted it to link to our content calendar where there'll be a list of week at that rage of dates and we can add records as we have.. if that makes sense. Thanks

I have a Multiple Select field that has several choices, like Camp, Membership, Donation. I want to create a Formula field that says Member or Nonmember. I know how to make an IF question for blank fields, but not for a specific Select from a field. I tried just IF(Field = “Member”), “Member”, “Nonmember”), but that doesn’t work. Thanks.

Hi, where do I find completed record when it disappeared in the table?

IF({Status}='Complete','✅','❌')IF({Status}='Billed','✅','❌') It does not let me, how can I combine them for one formula?The status section has 15 statuses, but only these 2 can go green. Please help!

Hello! I am trying to figure out a formula that grabs a start date when a previous task ends. So in row 2 the Task Dependency is DEC:Project Setup. In row 1 you see the task is DEC: Project Setup with the task due date being 5/13/2022. I want to create a formula that takes this due date and puts it as the Task Start Date in row 2. Thanks in advance for any help anyone can provide!

Hi there,I am trying to attempt to add a formula, that when a certain hotel name is entered in column A, then in column B, depending on that hotel name, it adds the hotel brand automatically. For example:If Hotel Name is Hyatt Centric, then Hotel Brand is Hyatt. I can get this formula to work: SWITCH("Hyatt Centric", "Hyatt")But my question is can I/how do I have multiple Hotel Names in one hotel brand? For example:If hotel name is Hyatt Centric, Hyatt House, Hyatt Place or Hyatt Regency in column A, then Hotel Brand is Hyatt in column B.How do I write the switch formula to have multiple options?Thanks!  

I am struggling here! I thought I was getting better - but I need help. Thank you in advance!My column is "Sourcing Manager" and I am trying to create another column with a formula to add their email automatically.If the Sourcing Manager is "Monique", then it should list Monique@theroomblocksource.comIf the Sourcing Manager is "Karen", then it should list karen@theroomblocksource.comIf the Sourcing Manager any other name, then it should list hello@theroomblocksource.comThis is the formula I am trying:IF({Sourcing Manager}=Monque, monique@theroomblocksource.com, IF({Sourcing Manager} = Karen,

I currently have a table that has alllll of our data. We want to separate it so it's a bit easier to manage. So, here's what I'm trying to do:Tab 1 - Master Info. This will have a SKU#, a product name, and a materials cost.Tab 2 - Wholesale Vendor #1 - We will type in a SKU, and it will bring in the product name and materials cost from Tab 1. Tab 3 - Wholesale Vendor #2 - We will type in a SKU, and again, it will bring in the info from Tab 1.I don't want the tabs to be busy and have extra columns. I'd like it to be a basic "vlookup" type. However, I can't figure out how to do it without a) having the extra columns and b), going into each and every line to pull out the info.Any tips? Thank you! 

I am currently trying to format information for later use, and need to keep to a pretty strict format, as seen in the first photo below. The inclusion of P1, P2.... is necessary to differentiate between information that has been provided to us and what returns false. I have managed to format the lookup field to separate into the desired format, but I cannot manage to use my "NumberofValues" field to create the P1-P5 in order for me to concatenate the labels before each separated value. Any help or direction would be greatly appreciated. Please let me know if more information is needed.

I have the following in a field:Value 1, Value 2, Value 3, Value 4, Value 5, Value 1, Value 2I need a formula to show me the repeated values:Value 1, Value 2Alternatively, I have another field with the unique values of: Value 1, Value 2, Value 3, Value 4, Value 5So another option could be a formula to compare this field with the original one and output the differences? 

The Goal is that I will have a new formula field, which gives me the lowest value of all 6, to be more precise a word which belongs to one of the dedicated fields (here it would be value "8" - field "1.6+2.6+3.6 traditio..." matching word which should be shown is "traditional")Best case would be, I also have a second field with the second lowest value of all 6 (here it would be value "9" - field "1.5+2.5+3.5 traditio..." matching word should be "social"

I am creating an asset register with the current values of assets. I have two formula columns in use to output the current data. Wondering if I can combine/nest the two formulas into one? This is what I have now, any advice on how I would combine these into one forumla? Top row is one column, bottom is the final row with current value.   (DATETIME_DIFF(TODAY(), {Purchase Date}, "years") * {Depreciation Percentage} * {Price}) MAX(SUM(Price-{Depreciation}),0)   

Hi all,I have read up that Airtable inserts double quotes if there are commas in the contents of the array, and I have seen reference to removing them by using either Substitute or Regex_Replace, but I have an IF statement and no idea how/where to put either to make it work (I'm new to formulas and they are def not my strong suit!)This is my formula:  IF({Inspection}=BLANK(),{Art}&" ("&{Condition}&")", {Art}&" ("&{Condition}&" "&DATETIME_FORMAT({Inspection},'YYYY-MM-DD')&")")

Can someone help me with this - it is not calculating if over 99 only if over 49 IF({Miles}>49,"0.10", IF({Miles}>99,"0.15"))

Hi there,I'm trying to script something but it's been ages since I used Javascript and I'm getting myself tied up.I have a list of rehearsal  dates,  and next to each date I have a linked record showing the actors required for that day's scene, and a linked record showing the actors unavailable for that day. I would like to script something that checks if any item of the array in "Actors Unavailable" for each record (rehearsal) matches any of the people in the "Actors Required" field. If so, we have a conflict and I need to change something. So I'd like it to then either check a checkbox or colour the record to indicate it needs fixing.To duck debug for a second, what I want in plain language I think is"For each record in table "Rehearsals"Check each item in array "Actors Unavailable"See if it appears in the "Actors Required" array of this recordIf so, change record colour (or tick a box)If not, do nothing"Troubl

I'm trying to use the datetime_diff formula to configure a field similar to the ootb duration field in Smartsheet, but in Airtable.I have a start-date and and end-date field. My goal is to have a duration field show the number of days between the start and the end dates.Here are my constraints. 1) I want to be able to show only weekdays. 2) If the start date and the end date are the same I want the duration to equal 1, not 0.Any suggestions?

Hi all !I've a concatenation formula wich works fine with all number but the 7%...That's crazy.... When I put 7% somewhere my formula return 7.0000000000001%... My fields are all in percent (precision 1%) but the concatenation field is in text format.There's nothing in my formula about the number 7 or else that could launch this error...Any idea ? >Thanks !

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?  

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 #}=

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 🙂