-
COUNTIFS and CONTAINS
Hi! I need some guidance for a count analysis of our RAID Log (Risks, Issues, Decision). In the RAID Log source sheet, we have a column where the team selects the Project(s) the risk or issue is tied to. The Project column is set up as multi-select. In a separate Count RAID sheet, I'm using this formula: =COUNTIFS({Risk &…
-
Rollup of contacts into a single cell as a single select or comma sep string?
I have a project plan with 100 tasks. Each task can have one or more assignees in the Assigned To column. I want to have a rollup of all possible assignees into a single cell for project filtering (as opposed to task filtering). I understand i can not do a formula with multiple contacts. I am OK to translate this into a…
-
Yesterday's Data and Rolling 7 Day Avg Data
Hi - I am all-new to Smartsheet and have hunted high and low for the answer to these questions.....with no joy. When I think I am onto something the formula always doesn't work, so i thought i would ask directly... I have a sheet that we update daily and want to create a summary at the bottom with two bits of info. This is…
-
Using WeekNumber formula with autopopulated field
Hello all, I am using the following formula to fill Week Done from an auto populated Date Completed column. The formula will not work. Date Complete is a Date field Week Done is a Text field Formula: I am using a similar formula on a sheet where the date is manually filled in and this works perfectly. Now if I add this…
-
How to calculate average response time in smartsheet?
I have these 2 columns and want to calculate the first time response, which is actually nothing but updated - requested, but its not giving me required output. Pls suggest
-
if statement between 2 times
column 1, auto date with time, "12/09/21 1:07 PM" column 2 is my play column column 3 i do a =RIGHT(([date auto]@row), 8) to get just the time "1:07 PM" column 4 was trying to do a T/F if the time is after 3pm but befor 12am (or can use 11:59pm) if have tried several thing, and can not get it to work. got any ideas?
-
Formula Trouble
I'm trying to write a formula that will populate the status cell in a parent row based on priority of different statuses in the children's rows. Here is a copy of the formula I have wrote. =IF(HAS(CHILDREN(), "Quoted"), "Quoted", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started"))) This formula…
-
Formula if cell is blank then, if cell is not blank then....
Formula gods, looking for help, please. I'm trying to write a formula that says if the finish date is not blank, then the QA status is Green, if finish date is blank, then go to the days open field and if less than >= 60 and <=89 then Yellow, if days open is >=90 then Red. I can write the individual formulas to get the red…
-
Identify Shift from Submission
I am looking for ideas on how to identify & link a submission using the "Created (Date)" system column. We work shifts either from 07:00 - 19:00 (Day) or 19:00 - 07:00 (Night) and I would like to assign the label "Day" or "Night" to a submission based on the above criteria. Any thoughts on the best way to approach this?
-
Status If/And Formulas
I have a project manage sheet I'm managing that I can't seem to get the formulas right in. The first one has two parts. 1) I want the status to be green if the Planned Completion Date is equal to today or the future. The Status to be Yellow if the Planned Completion Date is 1-7 days in the past and the Status to be Red if…