-
Help with a date formula.
I would like to enter in a date into the end date column (its currently enabled as a date column) however the formula listed below returns an error of #DATE EXPECTED. The false value "-" shows up but the date does not. Any help on this would be greatly appreciated. Here is my logic written out. if rotation # column = 1…
-
Tracking age in hours
Hello, We are using Smartsheet as a ticket queue (users submit tickets via a Form) and I'm wondering if there is a way to track age in hours as well as hours between updates. For example, if we want to show how many hours it took to assign the ticket and how many hours it spent in a particular status and things like that.…
-
Counting Distinct Contacts in a Contact List Column
Hello. I have a tracker and in it I am using a Contact List for "Assigned To" and I allow for that to contain multiple contacts. I am trying to count the number of distinct individuals in the column to come up with a total number of contributors to the effort we're tracking. I used: =COUNT(DISTINCT(Leads:Leads)) The result…
-
Need assistance with invalid operation result
For the below once I have a date in [In Progress Date] or [Actual Completion Date] it sets #Invalid Operation when I expect a date result. =IF([Actual Completion Date]@row = 0, IF([In Progress Date]@row <> 0, [In Progress Date]@row + ([Estimated Days to Completion]@row * [Resource Time Percentage]@row), "TBD"), [Actual…
-
INDEX, MATCH, COLLECT, CONTAINS ... oh my.
I have two worksheets. Worksheet A ("Verified Content Report") has about 100 columns. On each row are details about a specific asset. One of those details is in a column called "Owners," which contains one or more user IDs (comma-separated) for each individual "owner" of that asset. Worksheet B has 2 columns: user IDs and…
-
Formula to not Count Checkbox
I am working on building a SUMIFS formula to not count a certain row if a certain checkbox is checked in another sheet. I already have it counting everything if the criteria is correct, but having trouble adding the "NOT" if the checkbox is checked. Thanks, Kris
-
Calendar Displaying blank fields
I am trying to create a smartsheet calendar off a workplan, and am running into the following issue. I don't need all the items from the workplan to populate on the calendar. My main column is [task name], and for some rows, I have an associated [event type] in another column. I only want the calendar to populate the rows…
-
Networkdays Formula
Help! I've tried a 1000 different versions and nothing has worked quite right yet! We are counting workdays between a due date (date- due) and date of issue correction (date- corrected). Also, I don't want to receive the #invaliddata if my date- corrected cell is blank. I'm good with: =IFERROR(NETWORKDAYS([Date- Due]@row,…
-
What is wrong with my IF...AND stmt?
=IF(AND([# of Days Open]@row >= 22, Status@row = "Pending Review"), "Red", IF(AND([# of Days Open]@row <= 14, Status@row = "Pending Review"), "Green", IF(AND(Status@row = "Pending Review", AND([# of Days Open]@row >= 15, [# of Days Open]@row <= 21), "Yellow")))) In my calc above, it was working fine until I added the 3rd…
-
Formula for RYG for Project Schedule Status
We were able to create a formula for Green and Red status but have not figured out how we can automatically set the project status to YELLOW. Normally if an activity(s) are slipping that affects the critical path the project should be in a YELLOW state so a return to green plan can be worked on. So what I'm looking for is…