-
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…
-
Pulling Multiple Rows of Data into 1 Cell
Hello, Does anyone know how to pull multiple rows of data into one cell? I'd like to build a cell that look-up the date "05/20/20" and brings all the values into one cell. I'm thinking vlookup: =VLOOKUP("5/20/20", {Test Range 1}, 2, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 3, false) + " - " + VLOOKUP("5/20/20",…
-
Avoiding #INVALID DATA TYPE when cells are blank
Hello, I am setting up a staffing leave Smartsheet for my team and unfortunately I am getting the #INVALID DATA TYPE error message. There are 2 formulae set up within the sheet: Leave Remaining (the dark grey row) is calculated as follows: =[Leave Allowance] - Taken + [Leave Bought] + Carried + [Long Service] Taken (the…
-
Auto check box using =IF(Primary@row="California",1,0)
This formula works as typed. However, I need your help writing this formula such that it will auto check box if the word is "California", or "Florida", or "Arizona".