-
SUMIF Formula returning incorrect number
Hello! Smartsheet newbie here:) I was trying to use a simple SUMIF formula to calculate the total number of days for a specific field. For example: I want to add all the days or duration for all projects that were labeled as "Conversion" together to calculate the total number of days spent on all conversions. However, from…
-
Nested IF(ISBLANK & Date Columns w/Progress Bar
Hi Everyone! I would like to know if you are able to use date columns when automating a progress bar. Basically dates are entered as product moves to a new stage in the process. As the dates fill in, the progress bar fills as well. I've used nested IF formulas for automating a progress bar but, for whatever reason... Im…
-
Keeping persistent dates for status changes
Building a kanban board with typical IT cycle states: Ready, Dev, Test, Deploy, Done. I'd like to track cycle times and throughput. I've created (locked) columns to capture a status change for each of the cycle states; e.g. Dev Start and formula "=IF(Status@row = "Dev", TODAY())". That works to capture the date that a task…
-
COUNTIFS a start date is less than today, and is an ancestor level 3
I am currently struggling with the following COUNTIFS formula =COUNTIFS(AND([Start Date]:[Start Date], <= TODAY(), ISDATE([Start Date]:[Start Date]), [% Complete]:[% Complete] <= 0.5, [Helper Column]:[Helper Column] = 3)) It should read as follows... Count a row if it... has a Start Date that is less than or equal to today…
-
Date Stamp
Over the past couple of days, I have searched and read every article and even solicited help from SS and have worked myself in a circle. I have a drop-down list (Submitted, Approved, Declined), I need to "auto-populate" a date column to capture the date stamp when something is selected from the drop-down list. Is this…
-
Formula cross referencing sheet via template
Hello! I have a workflow created using Zapier so that when a new row is added to my master project sheet via a form entry it will update the first row in a template sheet and create a new project sheet from that sample sheet. This all works great with the exception I have one formula that uses a vlookup to tell the status…
-
Lookup on a form?
Hi all, Is there a way to have a date/time field on a form look up data in a separate sheet to populate itself? I'm setting up a form for people to select a date and time slot to attend a training session. I thought about having the list in a separate sheet and then only show on the form dropdown the sessions that are…
-
Calculating total hours worked in a shift
I'm trying to create a weekly work schedule for all of the employees at our company, so that we can see the start and end of shift and total hours worked per day and per week for each employee. I contacted the Smartsheet team only to find out that Smartsheet doesn't currently support time based formulas (which is such a a…
-
If Statement for three columns
Hi Guys! I am working on the following Formula: =IF(AND([Date Completed]1 < (TODAY(-3)), Cause1 = "", Correction1 = ""), "Overdue", "OK") Goal: If the date in a date column [Date Completed] is more than 3 days ago, and the cells for the next two columns are empty - one or both - I want to remind our technicians to fill…
-
if statement
Hi, Can someone help me on this formula. Please check the formula if the % is greater than 100 % its should shows as Yes but in my formula its showing as No.