-
How to change status based on start/end dates/if completed was checked or not.
Hi there, I've been reviewing past topics but haven't been able to get those formulas to work. I have a sheet where there are start/end dates, and a completed check box column. I'm having trouble figuring out how to create a formula where if start date is in the past and end date is in the future, having it automatically…
-
I want to do a serial IF this then that formula across more than one column. Please help!
My =IF(Child@row="Name1", "Parent1") and it worked to bring the Parent name into the @row, but I am trying to also add a few other Child/Parent combos into it as well. For example, In the Parent Column, I want to combine =IF(Child@row="Name1", "Parent1"), OR =IF(Child@row="Name2", "Parent2"), OR =IF(Child@row="Name3",…
-
Need to tweak Health Indicator formula based on "status date"
I need to tweak the following formula to use a [Status Date] instead of "Today". We publish status reports each Wednesday as of Friday of the previous week. I have added a Status Date column that I will update each week to reflect the status date. Current formula; =IF([% Complete]@row = 1, "Blue", IF([Planned Duration]@row…
-
Date formula help: return 90 days earlier than start date, on the closest Tuesday
Hello, I'm looking for a formula to pull a date that is 90 days earlier than our start date but on the closest Tuesday. Thank you
-
IF Statement - Checkboxes
Hi there, I currently have this formula (below) for my checkboxes that states if the end date is less than 30 days away and the Status of a project is "In Progress" the checkbox is checked off. Currently, if the End Date field is left blank and the Status is "In Progress" the checkbox is marked off. I want to make it so if…
-
Seven Most Current Entries for each Question for Each Location
Hi, I want to pull the SEVEN MOST CURRENT ENTRIES for each QUESTION for each LOCATION. Here's my Master Smartsheet. This is the formula I started using based on responses in the community: =INDEX({TEST Range 2}, MATCH(MAX(COLLECT({TEST Range 4}, {TEST Range 2}, [Q1]@row)), "Clearfield", {TEST Range 5}, 0)) This formula…
-
How do I find the average approval time?
I have a sheet that our PMs approve invoices and another sheet that copies the rows as soon as they are approved. One column is Date Created (Created Date) and the other is Approved Date (Modified Date). When the row is pulled, it captures the modified date when they approve the row. I need to find the difference between…
-
Report? Summery Count of rows
I have a sheet that bring in once a week that has our available units and extra data. I need a report to look a this data and return a count of how many units per building are available. Data coming in: Building | Unit Number | Rent Ready |.... I need a report to return Building | Number of available units (count number of…
-
need help with formula
I am trying to create a RYG formula Have a drop down column with the choices: 75% staffing 50% staffing 25% staffing <25% staffing I want to say that IF([Staffing Level Impact to MEF]@row = "75% staffing", "Green", IF([Staffing Level Impact to MEF]@row = "50% staffing", "Yellow", IF(OR(([Staffing Level Impact to MEF]@row =…
-
COUNTIFS results in Zero (and NOT for same reason as in rsmith's question)
Why am I getting no errors, but a 0 (expressed as a percent as formatted) when using the following formula? Please note that I am NOT the person who named these columns or the list of values therein, but you've gotta work with what you're given, right? Here's the formula: =COUNTIFS([SF Phone decom'd/removed]2:[SF Phone…