Formulas

Hi there, I do have two date columns {Created Date} and {Last Substantial Contact}. I like to check first if the date in the {Created Date} field is older than 365 days. If it is not then I like the formula to output “covered”, if it is older than 365 days I like to check if {Last Substantial Contact} is less than 365 days. If this is the case then the field should also read covered. If {Last Substantial Contac} is older than 365 then the field should give the output “uncovered”. This is how I tried to implement the above logic: IF(OR(DATETIME_DIFF(TODAY(),{Created Date},‘days’)<365), (DATETIME_DIFF(TODAY(),{Last Substantial Contact},‘days’)<365), “Covered”, "Uncovered”)) I tried several variations but always get “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” Does anybody see where the error in my formula is? Thanks, Marc

I have some tables one of which is contributors and another is contributions. The contributions primary field is the amount of the contributions. One of the contributions field is the Contributor linked field (linked to contributors). I am attempting to sum the contributions from each contributor but it is not working out. Is there a way to sum the contributions attributed to each contributor?

Hi, I am looking for a way to build a formula that goes like this If the date created+timestamp <= to 4PM that day then subtract 10 hours if not subtract nothing. I am racking my head how to do this. Any help would be awesome!

I have a system of daily count and need to check previous day count and today count to get how much used . Is there anyway i cud perform functions between rows .atleast auto copy or subtract for two rows ?

I am attempting to write an equation but I cannot get Airtable to do what I want. What I am asking is that if “Total Time Worked” is greater than or equal to 7 hours and 45 minutes to round it up to 8 hours. However if “Total Time Worked” is less than 7 hours and 45 minutes do nothing and leave as is. I am trying to calculate this for payout purposes at work and making sure that anyone working at least 7:45 gets a full 8 hours but anyone working 7:44 hours or less simply gets paid for the time they worked. This is what I wrote: IF({Total Time Worked} >= 7.75, ROUNDUP(7.75,0)) What am I doing wrong here? Any help would be greatly appreciated. Problem here is once this equation is entered what Airtable is now doing is round all hours above 7.5 to 8.

I’m trying to display the days in which a certain volunteer has come, as a percentage of the days that have passed this year. I’d like that to be shown in a block, i.e. 33.1% of days Volunteer X has been present, (178 days this year, X has been here for 59 of them). How can I create such a block? How can I create a column which counts the days of the year that have passed?

I’m trying to create a formula that I don’t think should be too complex but I can’t seem to figure it out. I have an Agenda table with a list of agenda items, and a To Do column that links to a To Do table. In the To Do table, team members can check off it that thing has been done or not. I would like to write a formula in the Agenda tab that will essentially remove the To Do items once they have been checked off. I’m fine with creating a new To Do column and hiding the original one if that’s necessary, but I can’t seem to figure this out. Any help would be appreciated!

I want a form where the user can select a range of dates. This is for group interviews. So I send every candidate the form, they select a javascript calendar saying they are available Monday-Tuesday 1-2pm etc, and then I can pick a date such that all the form applicants are available. How do I do this?

Hello! I’m trying to write a “Deadline Alert” formula to indicate that a particular task is :rotating_light: PAST DUE​:rotating_light:or :negative_squared_cross_mark: which means on track / not overdue. The conditions for a past due task are as follows: (1) The field “Task Status” (Single Select Field) must NOT be set to “Complete” or “Not Started”---- completed or not started tasks can’t be past due :winking_face: (2) The field “Approval Deadline” must be TRUE aka not blank (3) The field “Days Past Due” (which already has a formula in it to calculate days past the approval deadline using DATETIME_DIFF) must be <1 (a negative number). Below is my poor attempt that has not been working. I’m new to formulas and greatly appreciate the help! IF(AND( {Approval Deadline}=TRUE, {Days Past Due}=<1, {Task Status}!=(“Complete”,“Not Started”) ), ” :rotating_light: PAST DUE​:rotating_light:", “ :negative_squared_cross_mark: ”)

Hey AirTable community! I want to to autopopulate a field (specifically a single select field, but this query could apply more broadly to other field types as well) with “No” if “Yes” is not selected. I have a massive data set with “Yes” sprinkled throughout that field type. I know I could filter to show blanks and manually select “No” and click+drag, but I will be continually adding new records and I want the preset for this field to be “No.” Any solutions??

I seem to be in an odd setup … new Chromebook using Chrome OS. I’ve installed an app, but it says its no longer supported, and to upgrade to install the latest version. Not sure what upgrade means in that sense, but I did that … and it installed but now its saying the same thing. Is the app supported on Chrome OS? It’s in the Play Store.

How can I use linked records to create scalable reports? I am trying to build a maintainance databases with “assets”, “maint types” and “log”. For example… Assets: red truck, white truck Maint types: engine oil, etc Log: date + asset (link) + Maint type (multi-link) I would like to create a report for each asset & maint type combo when the last date of maintenance was. I am aware that I can use logic formulas on the “log” and use rollup on the asset to find the latest date in a specific category, but that requires unstable and manual formula creation for each maintenance type. Goal: Item, Asset (link), Maint Type (link), Latest Service Date (for this combo) Here is a read only version of the base… @W_Vann_Hall I thought this might interest you. I looked at several of your examples and posts, but could not find my solutions… perhaps you have already solved it and I

Thanks in advance for your help here! I have created a formula field that has nested IF statements that multiplies a Currency field by either a 12, or a 4, depending if the payment is monthly or quarterly. The result displayed is numerically correct, but when I try to format the number shown, the Formatting tab in the Customize Field Type dialog displays, “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” The greater issue for me here, beyond the formatting, is not being able to sum or do other calculations on the field. IF({Payment frequency}=“Monthly”,{Price AUD}*12,IF({Payment frequency}=“Quarterly”,{Price AUD}*4,IF({Payment frequency}=“Annually”,{Price AUD},""))) It doesn’t make sense to me that the calculation works fine, but the returned result is, it seems, considered to be text, not a number? Thanks in advance for your insights everyone! Cheers MC

Hi, When I try to save my images from Airtable into a folder and change the name, it converts the image file into a file that I can’t open. Any suggestions.

Does anyone know how to delete a Category. I accidently created one. My other question is how to rearrange them. I don’t see any functions on how to. Thanks again, and I apologize if I am using the wrong terms.

I need to make a formula. In it, I will have a report due date, then the date the report was submitted. I want to make a report progress column that states if the report is ‘overdue’, ‘in progress’, or ‘up and coming’. However, here’s my problem: Report Due Date will always have a date Date the report is submitted may be blank How can I make a formula based off Report Due Dates saying the status if the date in the “Report Submitted” column is blank?

How do I get information from one table with a certain status (on schedule) to automatically update to another table? All of my tables are linked, but I cannot get the IF formula to move the information to another table automatically.

I use Airtable to keep track of my service tasks at work. We are watchmakers and we repair watches. So, I would like to record the date when a job is finished by us, and another when the customer picks up the watch. The intervals between the start, finish and pick up are very useful to us! I have used Zapier to export a field to a new Airtable form, via a Google calendar event, to make a time stamp. This works well, but adding another time stamp to the same original listing has proven very difficult to accomplish. Any help in much appreciated!

Hey Airtable Kinds and Queens! Need a bit of assistance for a mere airtable peasant. I have 2 tables. One table has: rep ID and other sales data. This data is updated monthly with the amount they sold that month. ID#, Name, Retail Sales, Date 343, John Doe, $5,000, 1/1/2018 343, John Doe, $2,000, 2/1/2018 Second table has the contact details including email, phone number, etc. Here is where I am struggling. I want to create a column that will show the Sales Persons last months sales, previous 3 months average sales and average sales for all time. I have the tables linked by the ID# so the data is linked. I can rollup the average for all time, but I’m not sure how to put a date function into the rollup or other field to only give me the average for a specific date rang. Any ideas? Thank you so much!

Hey folks! I’m moving all my finance to airtable, so far so good. Couldn’t figure out how to generate a cashflow view. In each record, I want to see the sum of the previous records. The Records are sorted by date and i want to see every day the cash status. I didn’t see any option of doing it… Thank you!

Aloha! Looking for help. Need help with a formula. I tried this: Here’s an example: On 6/21/19: Clock in at 6:47 AM One 6/21/19: Clock out at 11:00 AM On 6/21/19: Clock in at 11:30 AM On 6/21/19: Clock out at 1:00 PM On 6/21/19: Clock in at 1:15 PM DATETIME_DIFF({Time Out (1)},{Time In (1)},{Time Out (2)},{Time In (2)},{Time Out (3)},{Time In (3)}, ‘minutes’) This is what I keep getting back in return: Time duration: -63142200000 Help Please! I am at a loss!!!

Is anyone using Airtable in a Healthcare Organization? If so, do you consider Airtable HIPAA Compliant and secure for the loading Protected Health Information, PHI, into an Airtable database.

Hi folks, I’m trying to create a formula that will return a date and time that is a pre-designated time the next day. For example: Jan 12, 2019 @ 2:19 PM AND JAN 12, 2019 @ 4:00 PM Would both return a date of JAN 13th, 2019 @ 5:00 PM I can add 24 hours with the DATE ADD formula, but is there a nested formula I can use to modify all times to a predetermined time? The purpose of this formula is to create a due date field, that is a set time one day after all form submissions that populate my table. Thanks!

How do I delete multiple duplicate data from the sheet?

Hi, I’m trying to calculate if {Table 1 Field 2} (date field) is before {Table 2 Field 2} (rollup field to a date field). The structure is: Table 1. Field 1: Student name Field 2: Date student enrolled into college Field 3: Subject name(s) (linked record to Table 2) Field 4: Date(s) (rollup field through Field 3) Table 2. Field 1: Subject name Field 2: date of class Field 3: Student name Basically, any formula cannot work around the rollup field because 1) it’s an array and (usually) has more than one date as there is more than one linked record… so I get something that looks like this: date, date, date, date. Does anyone know how I might calculate if the Date student enrolled into college comes before Date of class, given that there might be more than one class? Alternatively, creating a bridging table to capture both of these so that there is no use of a rollup that is showing more than one date. T