-
How can I get (on a separate metric sheet) the average SLA time within a given month?
I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement SLA column and a "date of request" (auto column for when the submission was created), and I'm looking to record the average for each month on a…
-
Using INDEX/MATCH to pull a date and CountIF not recognizing as a date
Hi, I'm using INDEX/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), "CAPA Not Closed") Once the formula is…
-
Checkbox find duplicate in a column, do not check either box if either has a value in 2nd column
I am using a checkbox to find duplicate entries in the column name Email. I can up with this that worked, =IF(COUNTIF(Email:Email, Email@row) > 1, 1). Next, I am looking for both duplicates to not have a value in the Using Generic Account field and then check both boxes. Do not check either box if one of the two…
-
Metrics Sheet Doubling values
Hello, I am trying to solve have a cross-reference sheet problem that is doubling my values in my metric sheet. I am using =sum(children()) in my source sheet to sum the values under each parent/child row. When I reference the destination sheet for metrics it doubles the value because I think I am referencing to sum all…
-
Multiple Checkbox's for sheet summary into a dashboard.
Good afternoon, I am looking to see if this is possible. I am looking to have a dashboard automatically populate with a new row when another is "complete". Which I am able to accomplish with automation tasks. The issue is building the dashboard that has multiple columns of checkbox's. Please see the photos below for an…
-
Help needed for SUMIF, Date Range
Hello I am having trouble with the below formula to return date based on a date range and getting a return of #invalid operation. I checked this out in the community but not able to find a resolution. =SUMIFS({All PM PO Tracker-Assigned To}, "Project Manager", {All PM PO Tracker- Change Order-Submitted}, "Invoiced",…
-
Formula that counts how many rows have a date in the past
Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an "unparseable" error: =COUNTIFS([End Date]:[End Date], "<=" & TODAY()) Thanks in advance for your help!
-
MAX Formula Help
I am working on a max formula that I can't seem to figure out how to set up the criteria. Background: I have a sheet (Intake Sheet) that is gathering project health data on a weekly basis via update request. Each week after it is collected, every row gets copied to another sheet (Historical Intake) so always have a…
-
Calculate the # of months between 2 dates, with 1 date being static
Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…
-
COUNTIF/SUMIF items with number of workdays between a date and a date range is 3
Hi! I would like to count a number of entries in a sheet if their due date is a certain number of work days before a date in another sheet. I tried to use the following formula: =COUNTIF('reference to a list of entries column in another sheet', NETDAYS('reference to due date column in another sheet', 'cell with a date in a…