-
Formula to show whichever is less
Hello, I'm working on a formula to show the remaining duration of a task. I'd like to calculate workdays using the planned end date minus either today or the planned start date, whichever is closer to the planned end date. For example, if the planned start date is 10/29/21 and the planned end date is 11/05/21, then the…
-
Is there a formula/function to help count number of children assigned to specific parent tasks?
Hi all, I'm trying to put together a table that lists the number of sub-tasks (children) in each respective Workstream (parent) of a specific Project Plan, and also breaks these down into their respective Health Status (Green, Yellow, Red). I am aware that I can apply a "COUNTIF" formula and set the range to encompass the…
-
Numbers separate by commas read as thousands
I have ONE cell with various numbers/text that users get to input via a form. The input varies from all numbers separated by commas or can have some text as well. If it is just numbers separated by commas and there are no spaces in between those numbers and commas then cell is read as number. I need some sort of a way /…
-
Time Tracking - Hours and Minutes
I was working on a way to track some task hours and minutes... came up with this: One of the problems I ran into is that if you put "00" (double-ought) or any minute with a preceding zero, it would error. You can see the work-around I made, not the most elegant, I admit.. If Smartsheet could ignore preceding zeros, it…
-
Help with Formula
Please help! I've been struggling with this for a while. I'm trying to get a formula to return "Yes" or "No" based on several variables. If the start date is less than today and status is 0-Backlog, Yes End date is less than today + 30 and status is not equal to 4-Closed, Yes Start date is less than today + 30 and status…
-
How do I do a min/max output based upon a specific criteria?
So I think I've come close to this in the area of indexing, matching, etc., but I can't quite get the formula to work the correct way. I have a sheet setup to where it calculates the average number of days that specific jobs have been open and working within several different groups in my organization. Each organization…
-
Combining two IF functions
Hello! I am trying to make is a formula that says "If there is a complete date then calculate the # of days that that task took from approval to completion. If complete date is blank then calculate how many days has passed since the approval date". I have two formulas that work independently but I can't figure out how to…
-
Count of Multi Select Options Across Entire Sheet
I can find some reference to this that others have asked but it seems slightly different then what I'm looking at. Consider the following: Column: Type of Issue A, B, C A, B A, D A, C, D C, D I'm looking to place a count of each of the items above on a dashboard across the entire sheet. Any insight would be appreciated John
-
Issues with CONTAINS using Cross Sheet Reference
Hello, I've created a COUNTIFS formula referencing another sheet. Each section of the COUNTIFS references the same sheet so that isn't the issue I believe. Everything works up until the CONTAINS portion. Is there an error with my syntax? =COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", CONTAINS("Onboarding",…
-
Leave blank if SUM is zero
I found a few answer that I though would work for us, but I keep getting an Incorrect Argument error. I want to sum numbers in columns for a total, but leave the total blank if the sum is zero. Here is what I was trying: =IF(COUNT([MD FTE]:[MD FTE] > 0, IF([APP FTE]:[APP FTE] > 0, SUM([MD FTE]:[MD FTE], [APP FTE]:[APP…