-
Aging Date Average
Hi Community, I am attempting to create a KPI for a dashboard that will capture the average number of days a 'something' has been opened, but only if it is still in an opened state. I can perform the appropriate "days open" with "=IF([Closed Count]3 = 1, NETWORKDAYS([Submitted Date]3, [End Date]3), NETWORKDAYS([Submitted…
-
Formula help - due dates falling on weekends
I've got formulas in my sheet that calculate due dates that fall on weekends, and I've used conditional formatting to highlight those dates to alert me to those dates that are on Saturdays and Sundays. Now, I want to move those dates to the Monday next. Is there a way to automate that? Ultimately, I need the best way to…
-
Linking/adding rows using 2 spreadsheets
Hi all, I am having an issue that might be simple to solve, but I can't seem to find an efficient solution. I have 2 spreadsheets: - One with the ID, name, Lot and several other columns with product specification information - Another just with the same ID's, names and Lots, and a few bits more that don't pair with the…
-
Parent status (RYGB ball) changing based on child status (dropdown)
I am looking for help with a formula where I have a parent row and then multiple children rows below. The Parent has an overall status that is a 4 ball symbol of red, yellow, green, blue) My child rows are using a dropdown menu called "Confidence" with percentages as the values (10%, 20%...100%). I'd like to set the the…
-
Sorting Numbers with a "-" in it
Hi all, Probably my incompetence here but i have a confusing issue at the minute. I have a long list of job numbers and am trying to sort them in numerical order. There is a criteria that we use to create these that you should probably be aware of: * Primary number is a 5 digit numerical number (i.e.21495) * If there is…
-
How to Create Tend Arrows with a Function
I have a cross sheet table with data displayed on it that works fine. Just wondering how I can display a trend arrow using a function for Up, Down and Across. Basically, I am measuring KPI performance against the number of days since the last time we had for example a Lost Time Injury or as is known as an LTI. This work…
-
If project status complete - stop calculating formulas & Conditional Formatting
Greetings, Currently at row 1600, waiting for a cell's formula to process the formula may take up to 10 minutes. For instance, sometimes when I change the duration of a Child row by 1 day, it may take 10 minutes for the End Date to advance by 1 day. I use A LOT of formulas, cross-sheet references, as well as Conditional…
-
Nested If Formulas with conditions in 2 columns
I'm trying to figure out how to create a formula that has a red flag appear if the following are true. I can't seem to figure out how to put the whole formula together. Any help is appreciated. Thanks! IF(AND[Due Date]@row < TODAY(), Status@row <> "Complete" 1, 0) IF(AND([Due Date]@row < TODAY(), Status@row <> “Closed”),…
-
Raise flag if due date is within 30 days
Hi There I'm very new to smartsheet so for you this might be a very simple thing. I'll appreciate if someone let me know what'd be the formula if I want to raise a flag whenever the due date is 30 days from the current date. We'll appreciate your help. Thanks Raashid K
-
Counting Dates within a range
I am trying to get a count of the number of rows with dates that meet one or another condition. I know I cant use Countif so I planned on adding a column next to my date column to capture the data. One condition is that the date is either within the next 30 days OR the date is in the past. Here is what I have so far and it…