-
Need Help with Duration (days)
Hi guys, I am setting up a dashboard and trying to collect data for it. I would like to show average duration for projects, if possible. For example, I have the columns below. Is it possible to create a formula that will tell me an average length from received to approved? Or do I need to add an actual “Days” column into…
-
Help needed with formula calculating open issues based on status, owner and due date
The formula is not pulling all data. Below are the formulas I am using: Overdue: =COUNTIFS({Status}, "In Process", {Assigned to}, "Susan Hacker", {Current Completion Date}, <TODAY(0)) Due in 1 month: =COUNTIFS({Status}, "In Process", {Assigned to}, "Susan Hacker", {Current Completion Date}, <=TODAY(30), {Current Completion…
-
Formula to indicate previous and next months
I'm drawing a blank on how to achieve something to avoid having to manually maintain a sheet. I've got a main data sheet with new requests getting populated. They have dates associated with them and what I'm doing is pulling them into another sheet to summarize that data based on the month. This piece is working great with…
-
COUNTIF / HAS / AND - How to count multiple selections in the same column.
Hello to All, I am trying to count items in a column. I have the formula in place for all inpatient and another formula for ambulatory. I need a count for those that have selected inpatient AND Ambulatory, basically how many selected both locations. This is what I have tried: =COUNTIF(Location:Location, HAS(@cell,…
-
Index and Match formula issue
Hi All, My colleague and I are trying to find the first occurrence of non-zero in a table and then return the corresponding month. Example table The formula that we are trying to work with is below, but whatever we try we keep getting an error of #UNPARSEABLE =IFERROR(INDEX(January31:December31, MATCH(True,…
-
Date Function
Hello, I need to record prior dates in the following 3 date fields which is based on the date column. Can someone kindly share how to write a function for this and explain it? It is a possibility that I may have to included a 90 days prior field and I want to make sure I understand the formula enough to know where changes…
-
How to select column name when typing a formula?
I often use formulas like COUNTIF, SUMIFS etc. where I want to select an entire column as a range, e.g. =COUNTIF([Date of Issuance]:[Date of Issuance], [Date of Issuance]@row) If I was doing this in excel, I would type "=COUNTIF(" and then click on the column heading to populate the range part of the formula. This doesnt…
-
Am I missing something - adding formulas in sheet summary?
When I attempt to set a formula in sheet summary the wizard give one option of ‘Refernece another sheet’ and not the sheet you are in. When I click click on the column header in the sheet (i'm in) to set the range this is what I get this: This is different to adding a formula to a cell in a (metrics) sheet where you can…
-
If is blank and not blank formula
Hi i have two columns with Date as column property “Next PoC Date” and “ Target end date” and i need to have an additional column that show me the following: If Next PoC Date is in blank show me “Target end date” If Next Poc Date is not blank, show me “Next PoC date” All these in the same column. (This column will be the…
-
Complex health status with both gray and blue options
Hi, I am trying to generate a health status formula for my sheet with the following conditions. Ideally I would like to have green, red, yellow, grey, and blue health options but I am not sure if that is possible. If so, my conditions are: Blue: Status column is "Completed" (regardless of start or finish dates) Green:…
