Formulas

Hi Airtable community! I am planning a conference that has multiple sessions per day over multiple days, and as we tweak the schedule, I don’t want to have to manually compute the session start/end times. The duration of each session is pretty set in stone. I need a formula that will calculate the END TIME of an event based on the START TIME and DURATION of that event. For example, I have a session starting on 10/19/20 at 12:00pm. It lasts 20 minutes. It ends on 10/19/20 at 12:20pm. But that might change, and I’d like to be able to update either the start time or duration and not have to manually update the end time. Any assistance here would be much appreciated! Thank you, Jennifer

I have a field with birthdays and another field with wedding anniversaries. I have both feeding a calendar view but how can I create a single list in grid view sorted by the date that combines both fields. 1/1/2020 Birthday 1/15/2020 Anniversary 2/1/2020, Birthday etc.

Hi everyone! I was wondering if someone may be able to assist. I am quite new to Airtable and just getting my head around formulas. I am looking to create a formula to auto-update the STATUS of a client depending on how many jobs we have done for them. I have created a STATUS column which contains the three single-line field options of HOT WARM and COLD. I have another column which records the AMOUNT OF JOBS we have done as a number. COLD would = 0 visits, WARM = 1-4 visits, HOT = 5+ visits. Is there is a formula I could use for this? Thank you so much in advance :slightly_smiling_face:

I have two fields that use the Collaborator field in a table (Lead & Support). In order to do some filtering, I need to have a field that concatenates both collaborator fields but Airtable doesn’t want to let me. Any workaround? Thanks!

Hi guys! I work at a public mental health service and we use airtable to coordinate our work. I have a patients table linked to a visits table, which in turn is linked to a prescription table, which is finally linked to a drug table. I want to be able to retrieve the last drug a patient has received in the visits table. How could I go about doing that? Thanks in advance!

With my business, I have WAY too many deals that come across that are unique and individual from others. I am in DESPERATE need to find a way to create a formula, (or simply type in a number for that matter) and NOT have it affect ANY OTHER record. Just change the formula on that one particular record instead of every single one. For example. Right now my formula to figure out commissions looks like this: IF({PPW}=“4.35”,500, IF({PPW}=“4.25”,450,IF({PPW}=“4.15”,400, IF({PPW}=“4.05”, 350, IF({PPW}=“3.95”,300, IF({PPW}=“3.85”, 250,0)) ))))*kW This morning we sold a deal at $4.79. So none of the formula triggers did anything and they didn’t calculate. However, I want to pay him $600 at that price. So if I typed in $600, it would change that cell for every single record to $600 and completely ruin all our accounting. This happens multiple times a week, and I’m fear I may have to go back to an excel spreadsheet where I can do individual cells like that. I REALLY don’t want to go back to exc

IF({Gift Credit Count}, IF({Gift Credit Count}<{Soft Credit Count}, "Softs", IF({Gift Credit Count}<{Appeal Credit Count}, "Appeals", "Gifts")))) 3 fields I need to compare. Gift, Soft, and Appeal. I am trying to create a formula comparing Gift to both Soft and Appeal. If Gift is larger, than “Gift” But if the other is larger, than “Soft” or “Appeal” I am totally lost. Because the formula above work when “Gift” has a number above 0 but not when the gift number is 0 and if @Justin_Barrett answers this one too, I am buying you a coffee!

We have a cost for an item {House Cost} We have a margin {Margin} We want {Customer Price} to have this field update as below. Example House Cost $1.00 Margin 40% Customer Price $1.40

Hello, I’m trying to calculate an hourly rate using a duration field and a currency field. I have a formula field that calculates $/hr: ({Earnings}*3600)/{Hours Worked} However, I have a few records where I don’t have the number of hours worked, just the earnings amount. The formula calculates this as “infinity,” which messes with my sum of the entire column. Is there a way to tell this formula to leave the cell blank if there’s no hours worked?

I have a rollup field that gives us a quantity on hand of specific products - referencing an inventory (orders) table. I want to reference that field in the first column - the ID of our product table, so when it is referenced via linked tables, we can see the quantity on hand easily. However, this causes a circular reference error. Is there a way to do this?

what is the simplest way to convert a text field which is the three letter month '-'two letter year (Dec-02, May-18, etc) to a usable date field of the same format. The day of the month isn’t important, could be set to 01.

I would like to capture the record number that appears on the LHS of each record in a particular view in a field (when you hover over the record this number turns into a check box). I want to print labels for each record, and I want to have a field which shows what number the label is out of the total records in that view (i.e. “3 of 10”). Or is there another way to calculate the order of records using formulas? Any help would be much appreciated!

I am trying to create a formula to roundup a number to the next highest multiple of 50. Can help much appreciated.

Hi! I’m new to Airtable, trying to make a monthly budget, and I started with the “Small business budget” template. I want the end of month balance for each month to roll over to the next month, but I can’t figure out how to do that. In Excel, I would use a formula referencing the previous end of month balance for the next one (eg: end of month balance = {Income (actual)} + {Previous end of month balance} - {Expenses (actual)} ) Do I need to create a new table with lookup or rollup fields? How can I reference the end of month balance for each month separately? Thanks so much.

I have an orders table that records all of the orders we get. Each line item on the order gets it’s own record on the table. I have a field in that table that is order ID which is the same order ID for each line item in an order. I’m trying to get a count of the number of times an order ID appears. So if a customer orders three items I’ll have this field return “3” so that I know there are three items on this order. I can’t do a rollup or a count since order ID isn’t a linked field. Basically I just need a count of how many times a value repeats in a field.

I would like to know how to Count records from different table with specific conditions? For example I have a table called “Stats” where I would Like to put some spesific information (automatically) for example number of boys and girls (based on another table called Students with a field called sexe) ?

Hi! I have a rollup field (using ARRAYUNIQUE) that should always produce a single array value. I want to pretty it up and remove the encompassing quotation marks, but I can’t seem to figure it out. I’ve tried all of the following formulas, but none of them seem to work. SUBSTITUTE({Vendor Rollup}, '"', '') SUBSTITUTE({Vendor Rollup}, '/"', '') SUBSTITUTE({Vendor Rollup}, """, "") SUBSTITUTE({Vendor Rollup}, "/"", "") Can anyone help me shed some light on my issue?

Hi, We have a base that, amongst other things, generates requests and records when the request is completed. To provide feedback on performance, we measure the number of working days it took to complete the task using: WORKDAY_DIFF({Request Date},{Time Processed}) We can then use a Summary Block to give an average “headline" of the number of working days taken to complete. We then have then used the following to report the average during the previous month: IF(DATETIME_FORMAT({Request Date}, “YYYY-MM”) = DATETIME_FORMAT(DATEADD(NOW(), -1, “month”), “YYYY-MM”),WORKDAY_DIFF({Request Date},{Time Processed}),BLANK()) So here, during Aug, it will give the average for July. How can we have this a little more “live” by reporting the number of days taken to complete over the past 30 days?

Months Active is a formula that returns what appears to be a number. IF({First Gift Date}, DATETIME_DIFF({Last Gift Date},{First Gift Date},‘month’),"") Number of Gifts is a count field I’m trying to create a field that returns an frequency {Months Active}/{Number of Gifts} But it is throwing an #Error What am I over looking? I’m pretty sure it has something to do with the DATETIME_DIFF returning a date and not a number

Hi All, I am trying to build out an automated-ish roadmap and resource planning base that will help schedule features based on sprint data and resource availability. I used this base as a starting point: I started with this resource planning base: Resource Planning - Airtable Universe Here is the google sheet that I currently use to auto-schedule our roadmap: Roadmap Planning Example - Google Sheets The sheet doesn’t account for headcount, but it is easy to eyeball and pencil in. I’d like to use Airtable to autoschedule the roadmap based on feature complexity and then layer in the resource allocation to match. The problem that I have is Auto-scheduling features based on resource availability. For example, if I’ve planned for Feature A thru E below, and need to plan for the next 5, how could I make Airtable do some based on availability. Showing overlap in availability if an engineer is double booked. If an engineer is overbooked such as Person 2 below, how can I highlight that or bet

Hi, I’m new-ish and can’t figure out what seems like it should be a relatively simple problem. I am using a table to track attendance in a 10-week training program. I created a column for each week/day with a drop-down of selections for our trainer to select from when taking attendance. There are corresponding hidden columns for each day with formulas that convert the drop-down selections to codes used to populate daily attendance records for each individual trainee (in a Page block). I need to create an at-a-glance attendance summary record for the whole class that sums up excused absences, unexcused absences, and partial absences, per person. I started by trying to create a column that would count all the instances of Excused Absences per record using a formula. I have tried the FIND(“Excused”, W#D#) formula - which works if I’m only looking at one field. But I can’t figure out how to expand the range being searched so it looks across all the days/columns. I tried listing them all in

I want to recreate a table in google sheets with Airtable but each row has a different formula and in Airtable you can’t assign different formulas for rows and cells. What are some workarounds if I want to calculate assign different formulas to cell A1, A2, A3 and then calculate e.g. A3-A1?

Trying to automate adding/remove users from systems. Working fine… except I can’t understand how to get a “forever” value to return “KeepUser” while using this formula. IF(IS_AFTER({Time math}, {Now}),“KeepUser”, “RemoveUser”) My select values are all numbers, except “forever”. I’d like to keep it as a text option, but I’m not sure how to get around this in a formula?

Hi, I am a new user… can someone help how to get the amount of the time duration? Thanks,

Hello, Sorry for bad English. I created a payment tracker, which allow me to track every rent from tenants. You can find the sheet here (translate in English). Usually, rents are grouped by tenants, but for the translation I do not have copy them. When I receive a payment, I write on the record the date of receipt, the means of payment and I check the “Done” field. To quickly see what rent I need to claim for the month, I am looking for a formula that shows “LATE PAYMENT” in red for every rent that I do not receive when the “Due” date is over. Exemple : Every rent for September must be paid at least on 5th September. If a tenant do not paid the rent on 6th September, the formula field in “Rent of September” will be shows “LATE PAYMENT” in red. Can you please tell me what formula I need to write to make this happen ? Thanks.