Formulas

Hey all, Looking for a custom script/formula, happy to pay if required. i have a table full of records that are daily jobs. Each job has a value and I want to be able to show a total of all records that match 2 fields (customer and week number). The result should land in 'Total This Week' and the record 'Total in Matching Week (Expected Answer)' gives the expected answer.Hope it is clear but ping me if not. Have tried using various scenarios of roll ups into other tables but cant get it.https://airtable.com/appqWVJT26DBHjzDO/shrXA4B7gcO7XWTiCAny help would be massively appreciated, really get me out of a pickle.  R   

Hey there.I'm trying to get the duration of an activity by calculating the difference between 2 timestamps.These timestamps are the results of a formula that shows the time as a box is checked at the start of the activity and another, at the end. The formula in Start Time and Finish Time fields are:IF({Start}=1,DATETIME_FORMAT(SET_TIMEZONE((LAST_MODIFIED_TIME({Start})), 'America/Sao_Paulo'),'H:mm'),0)IF(Finish=1,DATETIME_FORMAT(SET_TIMEZONE((LAST_MODIFIED_TIME(Finish)), 'America/Sao_Paulo'),'H:mm'),0)Resulting this:The formula in Duration field is: DATETIME_DIFF({Start Time},{Finish Time},'minutes') And the result is "NaN" as seen above.So the question is: how do I make it show time difference of the activity? 🤔 🤔Any help is greatly appreciated.Best vibes.Ed.

Hello! getting lost in the sauce of nested IF formulas and looking for help.I have a CRM in airtable that I'd like for a formula to return one of two formulas based on a single select status for that deal.If the deal is open (any one of 7/10 statuses in the single select), I'd like for it to show the deals age based on today's date  DATETIME_DIFF( TODAY(),{Deal Created}, 'weeks')  If the deal is closed (any of 3/10 statuses in the single select) I'd like for it to show the age of the deal when it was closed:  DATETIME_DIFF({Date Closed or Lost},{Deal Created}, 'weeks')  Thanks for your help!

I am creating a record sheet with computing the monthly accrual of 1.1667?

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 EOM at least, but not how to specify the 15th and EOM as the pay dates and at this point it’s all starting to look like hieroglyphics to me. If anyone has come across a solution or similar use case and could shed some light on this, I would be deeply

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, karen@theroomblocksource.com, hello@theroomblocksource.com)) 

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')&")") Which gets me: "A11.05/003a Joanna Staniszkis (Artist), Marta W. Suchy (Artist); Summer Kites" (Damage Inspection 2023-07-26) If anyone could help, I'd appreciate it much. TIA! 

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"Trouble is my brain isn't doing the work to turn that into javascript.

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?  Thank you in advance! Julien.