-
Looking for a way to capture the turnaround time for new requests
I have a scenario where we have an SLA of 24 hours. I can capture the submission time of requests with the Auto-Numbering/System Generated (Created Date). That gives me the exact Date/Time Stamp of the submission. Now the clock is ticking. However, on the backend is where I'm struggling. I tried the Record a Date…
-
What is wrong with this formula?
Hi, I'm trying to count all consultation meetings between 1/1/19-12/31/21. This is the formula I came up with and it does not work. =COUNTIFS([RPP Consultation Meeting?]:[RPP Consultation Meeting?], HAS(@cell, "TRUE"), [Intake Date]:[Intake Date] >= DATE(2019, 1, 1), [Intake Date]:[Intake Date] <= DATE(2021, 12, 31)) Any…
-
LookUp and Match a value located in a cell with multiple values and return single and/or multiple va
Hi, I am hoping someone can help with this formula. I was able to get this working using VLOOKUP when there were fewer complexities. In Sheet 2 when there is a value in the "Lot Number" cell, * The formula should look at Sheet 1 to see if the "Lot Number" matches and exists in any of the cells located under the Lot Number…
-
CHANGE ON SITE AFTER IS CONFIRMED
I have a column “Equipment Order Status” that has a dropdown (Ordered, On Site, Shipped, Delivered) and another column “Equipment Confirmed Arrived” that has a dropdown (Not Confirmed, Confirmed). I would like when I choose from Equipment Confirmed Arrived” column the option “Confirmed” the column “Equipment Order Status”…
-
Formula Help - multiple criteria & ranges
Hi community! I need HELP. I have been hitting my head against a wall trying to write a formula to calculate across different ranges and criteria. See photo for example of data. My formulas are not working out. What I am trying to calculate is: Countifs 1) All PEM except PAC Support Center 2) Do not include rows with…
-
Resource Management workaround for Rooms (not people)
Because Legacy Resource Management can't handle anything that isn't a registered user, I'm trying to create my own Room availability sheet. I've got columns with week numbers. And my formula is successfully looking at a start date, or end date, but can't identify the times in between.…
-
IF/OR/NOT formula: What am I doing wrong?
There are three criteria that programs must meet before we can release the hold on their reservations: 1) Covid safety plan in place 2) Approval to bring minors to campus 3) Housing plans in place Here is the formula I'm working on, which is returning an "unparseable" error message: =IF(OR(NOT([COVID Safety Plan]@row =…
-
Countifs with contains
Hello everyone, I am unable to write a formula including COUNTIFS and CONTAINS. My formula looks like this: =COUNTIFS({Range 1}; Test1; {Range 2}; "Test 2"; {Range 3}; "Closed (WECO)"; {Range 4}; CONTAINS(City; {Range 4})) I don't know how to use the CONTAINS formula well... In the city column we can find 2 types of data.…
-
Complex nested IF, AND with traffic light symbols
Hi All, I'm working on automating a symbol highlighter to indicate project progress as well as alerting if key milestones are being compromised. The formula below is structured based on the following columns: * Finish: The Activity completion date * Start: The activity start date * Date: A column containing fixed dates to…
-
Locking Columns/Drop Down Lists
I need to be able to protect formulas in a column for the parent rows but still want people to be able be able to select child drop down cells. If I lock the column will this stop people from being able to use the drop down lists? If so what other options do I have to protect the parent rows' formulas?