Formulas

I have a table that records all of my business leads.I have another table that functions as a report, to provide a summary of all those leads, how much they cost, when they were generated, so on and so forth.These tables are linkedCreated Date column & Created Month column is automated to fill in this linking field when a record is created in the business leads table, to populate the reportHere's the problem, the business looks at weekly data from Fridays to Thursdays. How can I automate the data so that we can look at the report in the week range that is specific to our business?Currently, what I can think of is to create a formula field, {Week Range}The formula would look something like this:  DATETIME_FORMAT( IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Monday', DATEADD(TODAY(), -3, 'days'), IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Tuesday', DATEADD(TODAY(), -4, 'days'), IF(DATETIME_FORMAT(TODAY(), 'dddd') = 'Wednesday', DATEADD(TODAY(), -5, 'days'), IF(

Hi folks,Can anyone help my failing Friday brain out please? I have a lookup field pulling through a maximum of 3 payment dates from a payments table. I need to break those dates down across 3 columns; earliest date, middle date and latest date. I have a MIN and MAX in place displaying the earliest and latest values, it's the middle date value I cannot figure out. I don't suppose anyone can solve this with a formula can they? I'm hoping to keep the workarounds to a minimum if possible.Much virtual thanks in advance!

In a lookup field of a record linked to many records, I have a large string of dates. They are in no particular order.e.g:2024-01-09 4:30pm, 2024-01-04 7:00pm, 2024-01-08 2:30pm, 2024-02-09 11:00am, 2024-01-12 6:00pm, 2024-01-12 1:30pm, 2024-01-09 12:30pm, 2024-01-15 5:30pm, 2024-01-12 12:00pm, 2024-01-10 12:00pm, 2024-01-16 5:30pm, 2024-01-18 12:00pmHow would I use a formula to refer to a separate date field(date only, no time), and create a new string with only dates from the large string that match the specified date?e.g. If I specified "2024-01-09", for the data above this formula should return "2024-01-09 4:30pm, 2024-02-09 11:00am,  2024-01-09 12:30pm"Thanks!

I am trying to use this to automatically add 15 or 5 days to the contract date. The “calculation” field is an IF statement to calculate which amount of days to add. BUT I cannot figure out how to get the formula to add the days and give me a new date.

Hi there,I'm trying to create a field in a table (say Table B), where its output is takes into consideration a condition based on a field in Table B, rather than the original table it is rolling up from (which I know Airtable has a simple filter for).In my "Scheduling" table, I want to be able to enter a single date, or a start and end date. This field will look at all Shoots linked to the Team Member that is linked to this record, and spit back out a list of Shoots that have a start and end date/time that is within the dates I specified in the Scheduling table. [Context: This table is linked to a form, and its purpose is to notify a Team Member when their shoots have been scheduled and a short summary of their earliest and latest by email, triggered by an automated button in Interfaces.]The image below shows the structure I've got going now. Thank you!

Hello all,Hope you all had a good holiday season.I have looked, but can't find anything around this, which I think is simple.  Most of what I am seeing on here looks more complicated than it should be.I have a Date Formula field that I would like to populate with Today's date if a box is checked.  I can get that part working, but the rest of the fields show the #ERROR! message.  I just want to hid the message and show nothing.This is what I currently have:Thank you for any help,Matt 

Hi All, hoping someone can help with this.I have a database of clients for content creation, and there are various deadlines for each piece of content we write (for example publish due date, writer instructions due date, draft due date, etc.) We manually enter a publish due date and all other due dates are calculated based on that date using these formulas: DATEADD({Publish due},-10,'days') or DATEADD({Publish due},-5,'days') etc.Recently we've taken on a number of clients from new marketing sources who will have custom deadlines. We have added a column which specifies where the client came from. I'd like to have the dates calculated as above, but ONLY if the client came from Source A.  If the client came from any other source, the field should be left as a blank date field so we can manually enter dates. Is this possible, and if so what would it look like?Added example:So IF client source = sourceA, then DATEADD({Publish due},-10,'days')but if the source does

I just realised this simple formula, on most fields does not work . Test was done on a Single-Line field. IF(test1 != BLANK(), "Not Blank", "Blank") This will always return "Not Blank".  A workaround seems to be to do the below: IF(Not(test1 = BLANK()), "Not Blank", "Blank") Any idea why this is so? Or is there a simpler work to check for 'Not Blank/Empty'?

Hi, forumla gurus! I’m working on a scheduling database linked to our list of work. I’m a little caught up in the process of flagging when a staff member is on two shifts at the same time. Using @W_Vann_Hall’s formula to count occurrences in a string, using the start datetime and a list of all the staff member’s shift times from a rollup field, I can flag when they have a shift that starts at the same time, as below. IF((LEN(ARRAYJOIN({All Allocated Start Times}))-LEN(SUBSTITUTE(ARRAYJOIN({All Allocated Start Times}),{Start Time},"")))/LEN({Start Time})>1,"Clashing",BLANK()) The next step is adding to the formula so that if the start time is between the start and end times of existing shifts, it raises the clashing flag. Any help in the process would be appreciated!

Hi to all,This is my question:I have a table "table#1" whith a list of records. In this table i have a colum named "project" of type Link with 'allow multiple link', linked to another table "Table#2" with a list of project.In another table "Table#3" I have the same column, named "project", of type Link with 'allow multiple link', linked to the table "Table#2".In "Table#3" I want to create a field to collect and list all the related records in "Table#1" that match this condition: The value of column "project" (in Table#3) is equal to the value of column "project" in "Table#2".Is there a way to do this with a formula?Many thanks.

I have a formula that calculates a 2.99% but since January 1 is 2.29%. Actual formula: ({Total due}-(2.99*{Total due})/100)+{Manual Rate}I have a column: Date Booked How can I modify the actual column named 2.99% to be calculated only if the Date Booked is before 12/31/2023. And a second one to calcula only if Date Booked is after 01/01/2024? Any help, please?

Hi Everyone,How can I arrange chronologically a Month Field (Primary Field also) which is group and has this format 'MONTH YEAR'  ?

I want to make sure the cost of the services is split according to the months it's meant to run. For example, a Product is $16,500 and will run for 6 months (Start in Feb, End Jul), so Product Per Month is $2,750. I want to create a formula that puts a $2,750 in each field (image attached of my table). So in my mind the monthly formula should read: If Start is after 1/31/2023, than equal Product Spent Per Month. The formula that I have in each month is: {Product Per Month} and that's it. I don't know how to make the formula for If, than in this case.

Hi, I have a column with a date and time (starting date and time) in one tab and another column (Duration time) with a duration in another tab. I created a lookup in order to use this duration in a formula to get the endin date and time. I couldn't figure out why my formula is not working...Help ? 🙂

Hi All, I am trying to use the DATEADD formula to add a number of years to a date, The date is in one table and the number of years to add is in another table. DATEADD(Date,{Years till expiry}, ‘Years’) This just returns the same date as the date field. Annoyingly if I remove the {Years to Expiry} and just put a number in there it works fine, But i need to pull a different number of years depending on another field. I am trying to produce an expiry date for a qualification which is linked to a Google drive. the qualifications are listed in another table with the years to expiry number linked to each qualification. once this works i will want to run some conditional formatting in a report to see which qualifications are out of date. I have tried formatting the columns to integer and decimal. Thanks in advance Steve

Hi all! I'm trying to extract the ID from a google drives doc link, any ideas on how to do this? Knowing the ID of the file is the one after the /d/?

I have a field that gets the id from a different line, for example: iddepends on id (void if doesnt)statuscan be done1 In Progressyes21To Dono In this table, each task have an ID.This task might be done only after another task is done.For that, I want to if there is a value in "depends on id" and the status of that task is Doneshow "yes"otherwise show "no" As a workaround in the meantime I used a Link to a Table and a Lookup, but I don't want extra columns to avoid clutter if possible. How can I accomplish this?More specifically, how do I reference another column given an id?Thank you for your help!

Hello! I am attempting to recreate some data I have in Google Sheet and improve my work flow. I am currently counting things by hand and it taking to long. What I am trying to do is create a "Master" sheet where all my info lives. But have a "Stats" table that will then automatically aggregate the data by Photographer and month. It would count the specific "Production Type" and sort it by "Photographer" and month. I have attached some screen shots. I have been trying to google it for days with not luck. Here is the start to my "master" tableHere is my current "Stats" tables I have in Google Sheets:By Month:It then feeds into a yearly data sheet

Hi, I have a column where the information is linked to a value in the same tab. I would like the information to be automatically filled with the information on the other line. For example on the screenshot, I would like to have John Doe in the cell for Brandon Walsh when I update the john Doe line.  I tried with the automation but couldn't figure out how to do that.  Thank you very much...

I have a formula for my primary field that looks like this: CONCATENATE({Last Name}, " - ", Type, " - ", DATETIME_FORMAT(Start, ‘MM/DD/YYYY’), “-”, {Job Address}) and it turns out looking like this: Thorton - G, DS, GC, - 08/17/2020- 692 Chateau Ln. Holly Lake, Tx 75461 I would like only the House number of the address to show in the primary field. How do I shorten the address in the formula? Thanks in Advance!

Hi Folks, I'm trying out using the rating field for a ongoing review process and want to be able to roll up an average of those ratings. I've managed to get the rollup to work but can't get rid of the NaN for fields that don't have ratings yet. I've tried a couple different formulas that I've seen for similar questions but nothing seems to be working. Thanks!

Here's what I want to do. I have a field that lists percentages for the fees my company wants to add to a contract. Admin fee 10%, Late fee 5%, etc. If I were to stack these fees, I want to have a field that returns the sum of each percentage multiplied by the contract subtotal field. Is there any way to do this? Like: SUM ( (10% x $800), (5% x $800) ) but where it could recognize the array and do the math for each percentage. 

I have created a formula that gives employees a personalized link to fill out a form so that when they fill out the form, they only need to answer the questions and don't have to put their name, employee id, etc. Is there a way to get around not having them sign in to use the personalized links?I have already turned off the "See who submitted this form" box and the under Share Link settings the "Restrict access to enterprise email domains" option. It doesn't make the person sign in with the regular link to the form, only the personalized one. Hoping there is a way around this. Any help would be greatly appreciated. Thanks!

I have a Table that lists everyone who has taken our leadership courses (Participant table). Fields include Participant Name and FLL Attended.I have a second table listing everyone who is a salaried leader in our company (600 records). I want to have a column (field) in the Salaried Leaders table that shows if they have taken our FLL course. How do I set it up so that the Salaried Leaders table links to the Participant Table AND populates the field with the class they took?  I know I can do a linked record, but then I have to manually select and that is inefficient.I know the answer is probably pretty simple, but I just can't get there on my own 🙄

Hi, I would like to extract only the 2nd character of a data field, to concatenate with another complete data field.Can this be done please? Many thanks.