Formulas

Hi, I’m trying to set up a sheet to manage attendance for a preschool center. Kids have a different “weight” depending on their age, which I successfully set up. The weighting is used to determine how many kids we can have at once. What I now need to do is show a daily breakdown of those weights, because some children don’t attend preschool every day. I need a calendar where I can look at any given day and see how many total kids will be there. I’m trying to combine the weight formula with START and END date fields, but can’t make it work… Any advice is much appreciated!

Hi! Would anyone know how to prefill a form using multiple linked record field? The prefill form works with a single linked record but does not work when multiple linked records are selected :frowning:

I’m using formula to create a date field, which is used in Calendar view as a calendar’s source. My problem is to create empty date, if certain criteria is occured. I try to use BLANK(), but this returns it in non-date format. Here is the formula: SWITCH(UPPER({ :bellhop_bell: Agenda Status}), “PENDING”, IF({Scene Setup date}, {Scene Setup date}, {Due Date}), “SETUP”, IF({Scene Draft date}, {Scene Draft date}, {Due Date}), “SKETCH”, IF({Scene Draft date}, {Scene Finalize date}, {Due Date}), “DRAFT FOR REHEARSAL”, IF({Scene Finalize date}, {Scene Finalize date}, {Due Date}), “FINAL FOR SHOW”, IF({Scene Show date}, {Scene Show date}, {Due Date}), “CANCELLED”, BLANK(), “COMPLETE”, BLANK(), “REISSUE”, TODAY(), IF({Due Date}, {Due Date}, TODAY()) ) Note, formula works, but this does not allow to set field formatting as date. Is there a way return empty date in formula, so that field is in date format and not string ?

Hi guys!! I need to summarize the percent of a group over the total as shown in red in the example. Any suggestion? Thanks

Hello, First challenge…I am trying to show when a number is input to the RECEIVED column, that it subtracts itself from the ORDERED column and puts the result in the BACKORDERED column (however if the RECEIVED column is blank, I need the BACKORDERED column to be ‘0’ or blank as well). Second challenge…If the RECEIVED number equals the ORDERED column then the STATUS column should show ‘complete’. If the RECEIVED number is less than the ORDERED column, the STATUS column should show ‘in progress’. If I was to say this in one sentence it would be “If received is not 0 or blank, subtract it from ordered and show result in backordered AND if received equals ordered change status to complete OR if received is less than ordered change status to in progress” Any help would be much appreciated! :grinning:

Hi All - I would appreciate any support In one base, I have two tables (Master and Prices). I have a list of products on the master table with a field (column) named costs, and in Prices, I have Product and new Price fields. I need assistance (not sure if this is possible) with a formula that will help me update the costs in the Master table if the product in the Master table is also available in the Prices table. Example: In this example, the Master table will only update products A and C’s Cost fields because it has not picked up a product on the Price table. This means that in Table 1, product A will change automatically to 150 and C to 250. Nothing will happen to B because there is not a unit for it in Table 2. Any help will be much appreciated. Thanks

I had searched through the community and seen a post stating about the date difference of a period showing data like 2 months and 31 days or 3 months 30 days. It is worse if it involves February as it will be calculated as 1 month 30 days. Example as below. I’m making all the months defaulted to have 30 days, as per below formula. DATETIME_DIFF(end,start,‘months’ ) & ’ M ’ & ( DATETIME_DIFF(end,start,‘days’ ) -DATETIME_DIFF(end,start,‘months’ ) *30 ) & ‘D’ I would like to know if there is any way to make the difference to be more accurate like those in Excel? I saw that there is a post regarding Switch() formula but I have no idea how to include that into this formula. The Switch() formula is as below. SWITCH(MONTH({start Date}), 1, 31, 2, IF(MOD(YEAR({start Date}), 400) = 0, 29, IF(MOD(YEAR({start Date}), 100) = 0, 28, IF(MOD(YEAR({start Date}), 4) = 0, 29, 28 ) ) ), 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31) Is there a way to calculate the

I am trying to make a simple sales table that will communicate with my inventory table to subtract what is sold and show me how much inventory I have left. I don’t understand most of the lingo…

Hey everyone! I’m new here and have searched the forum and google for this problem with no success. I saw the thread about rounding by 0.25, but I am seeing a different issue. It appears that the “significance” value must be 10 or less. If I enter CEILING(4,6) the output is 6 as I expected. If I enter CEILING(12,12) the output is 10, but I believe the correct answer here is 12. This happens any time the second number is over 10 then the result is always only a multiple of 10 instead of the number you entered. If I enter CEILING(27,12) the output is 40??? Is this a known issue? Thanks for your time.

Hi all, I have an AT base with numerous tables, one of which is a “Document Master Record”, with a linked table for “Document Revisions”. At the moment, the revision number is manually entered e.g. 7 for the 7th revision. Ideally, I’d like that to automatically increase as a new record is added (i.e. it is automatically creating Revision 8, the next time a new revision recird is added under the Master Record), but needs to be in sequence for the master record, so {autonumber} isn’t (as far as I am aware anyway) an option. I had a look at automation too to see if +1 could be added each time a record is created, but doesn’t seem to be an option either. Anyone got any ideas? Thanks in advance.

Hey guys, so I have a test which contains a series of questions. Each question has a single select field with 3 answers. Each of these answer has a different value (for example: answer X = 9 pts; answer Y = 3 pts; answer Z = 0 pts). I need to create a field where to insert a formula that generates the exact amount of points with every answer. Something like this: “question 1 > IF answer = X > 9 pts; IF answer = Y > 3 pts; IF answer = Z > 0 pts” Your help would be greatly appreciated, thanks a lot

My use case is that two people share availabilities. When there is a common availability, a calendar invite goes to both people on that common time. Thanks!

Hi there! I’m looking to create a formula that will set a trigger for a reminder in 30 days time from an activity, when it will display “Trigger Reminder” after 30 days have passed from that live date field. I thought this formula would work: IF(AND(DATETIME_DIFF(TODAY(), {Live Date}, ‘days’ > 30)), “Trigger Reminder”) But that just comes up with Trigger Reminder for pretty much everything in my base. Any help welcomed! Thanks! J

Hi folks, I cannot figure out why this formula is not outputting what I need. My current formula for the column is this: IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’)) If there is a date in the ETA AMZ DATE column, mark as Need ETA (AMZ) Date. If there is a date, create new date + 7 days from existing date and to the nearest Tuesday. When I use this formula, the output looks like this: 2021-01-26T00:00:00+00:00 SO, I’ve tried DATETIME_FORMAT IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATETIME_FORMAT(DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’)),‘MM/DD/YYYY’) When I save the formula, it automatically removes the ‘MM/DD/YYYY’ leaving me with IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATETIME_FORMAT(DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’))) This doesn’t solve my problem. What am I missing? How can I add the format formula to my existing formula to populate

Hi, I have a price table with 12 rows per activity that refers to the number of people. I have 4 different column “With transport / without transport” “Transport price for 2 to 4 people” “Transport price for 5 to 6 people” “Transport price for 7 to 12 people”. In “Transport price”, I am looking to get the right transport price depending IF it’s with transport. Then depending of the number of people, I want to grab the right column/price. Could you help please :slightly_smiling_face: I really can’t find the right formula. Many thanks

I have the following formula that spits out a number of days since the last event for an entry and if no event is found, it returns 9999 instead: IF({Days Since}=0,‘9999’,DATETIME_DIFF(TODAY(),{Days Since},‘days’)) My issue is that the formula field doesn’t recognize this result as a number and won’t let me format and filter as such. Is there any way I can return a result that allows formatting as a number? I tried using BLANK() in place of 9999, but the issue is that I need to filter results that return a number greater than say 90 or 180. So blank results get filtered out when I would want them to be filtered in. And this is part of a longer filter so I can’t use an OR statement in the filter.

I’ve got a rollup field tied to connected records with dates, and I’m not 100% sure how it works. My goal is to have this rollup field only show the most recent date. So if it is rolling up from two entries, lets say: 1/1/2020, and 10/10/2020, I only want to see 10/10/2020. I’m assuming I use the MAX() attribute for that…but I’m not sure. Can anyone confirm what the appropriate aggregation formula is to display the most recent date of several in a rollup field?

I’m working on a character generator for Dungeons and Dragons and I’m having trouble building a formula to get all the information in one place. This is the view on the form I created: My goal is to create a formula that get rid of the numbers and just displays what Race the player selected (elf, dragonborn, etc.) for easier readability in grid view. When I write the formula to only refer to a single number it works fine. But when I try and have the formula substitute multiple numbers with a blank space it returns an error message. I was using the example formula on the side as a reference which uses => instead of a comma to separate the value being substituted with the new value but that didn’t make a difference. I’m pretty sure I copied the format of the example correctly otherwise so I’m not sure what I’m doing well. I have no experience programming, I’m just teaching myself as I go along so any help/advice would be extremely appreciated. Thank you!

Hi I’m trying to find a way to display the email addresses of the collaborators listed in another field. but I can’t seem to find any way to display this. I’m syncing a Client’s table to my teams table, and the client has a collaborators field, they are fine adding in an additional helper field for us but just can’t figure out what this needs to be. Thank you for any help with is.

I am looking to filter properties for owners that live out of state. I get information from my data source in the format below. What’s a Regex formula I can use to separate out the City, State, and 5digit ZIP?

I have a look up field that contains a list of companies’ names. I want to get the most mentioned company in that list. More details: I have three tables: Tenders Entities Companies Tenders table has a linked record from both entities and companies. From the entities table I created a look up table that retrieves the companies column. I’d like to get the name of the company that was mentioned the most. The idea is to answer the following question: Which company gets the most tenders of each entity? regards, Motlaq

I can’t figure out how to summarize a count of unique records across a grouping, either with a rollup or formula or even with a pivot table. I’ve created a sample base here with creator permissions. On the Interests tab, you’ll see the Categories I’m looking to count unique People across. Each of the groups should have 4 unique People. How do get that to show up somewhere? Thanks!

Hi folks, I’m trying to find a numerical value from the “Title” field and have it appear in the “amount” (e.g. 27.21) I’ve used this formula from looking at a few post’s but it only copies the values before the decimal: MID( {Title}, FIND( “For”, {Title} ) 6, 50 ) Another formula I found only copies the values before the decimal: IF({Title}, VALUE(RIGHT({Title}, LEN({Title})- FIND(“0”,“1”,“2”,“3”,“4”,“5”,“6”,“7”,“8”,“9”, {Title}))), 0) What formula do I need to get the full total which in this example is 27.21? Thanks in advance! :winking_face:

Hi, I would like to return all rows that contain a specific term. For example, I have rows that have two terms, [Default, Bar]. Using AND(term='Bar') will not work, but using FIND('Bar',term) will work. Except that it will also return results that have the term “Barber” as well. Is there a way to select rows that contain a term? Like FIND([Bar], term) for example? Thanks

Hello! I am working on writing a formula, and running into a few issues. To preface, I am tracking employee trainings in this database. We have 3 trainings that expire. 2 expire after 2 years and 1 after 4 years. I am trying to write an if/then statement where if the training name matches one of those 3 and it’s within a specific time frame, it will output “Overdue”, or “Up to Date”. Logical statements that I need: If the training name = “First Aid” or “CPI” AND the Days field is greater than 730 days, the training is overdue. If the training name = “Notary Public” And the Days field is greater than 1460, the training is overdue. I’ve been working on it for a bit, and have a first attempt at the logical statement, but it’s not working. Any ideas would be beyond helpful! IF( AND( {Trainings} = “First Aid”, {Trainings} = “CPI” )& IF( {Days} > 730, “Overdue”, “Up to date” )