-
Less Than Date Comparisons with Blank Cells
I have a column "Deliverable Past Due" that should return a Yes if any dates in the stated columns are less than today. The issue arises when many of the date columns are blank. The formula recognizes blank cells as being in the past and always returns a "Yes". I tried adding ISDATE to the statement, but then it always…
-
Any way to use a cell value as a row number in another cells formula?
Hi all Anyway to get the row # from a cell value and use it in a formula? Example this works: IF(COUNTIF([Assigned To]$974:[Assigned To]@row, [Assigned To]@row) > 1, "DUP”,1) Now let's say Column1 row 23 contains the value 974 I would like to do this: IF(COUNTIF([Assigned To][Column1]23:[Assigned To]@row, [Assigned…
-
Automating a way of understanding time difference when start and end time are in the same column.
Hi everyone, We are looking at using a smartsheet solution to understand how much time employees on our production lines are spending working on certain lines. I created a separate sheet for each production line (we have 12 production lines) and I am using QR codes scanned via a smartsheet form (Quick for employees to…
-
Status Report based on certain conditions, i.e. dates, status etc
Hi Smartsheet community, I'm trying to come up with a status report to display information on what has been done in our projects between certain dates. I would really appreciate your expertise and help!!! For example: Project A Status Report for the period 04/Nov/19 - 17/Nov/19 What I want to show is: 1. Completed…
-
Date Modified - Copy of column creates different date
Hello, I am working to create an automation off of the date modified field. This is to alert users if they have not updated a row in more than 4 days. Since the date modified field is not recognized as a date field I created a copy of the column using the basic formula: =[Last Modfied]1 My results are not what I would…
-
formula that checks if a project happened during the current month
I'm trying to create a formula that checks if a project happened during the current month. Example: Project Start: 09/09/19 | Project Finish: 12/13/19 Here is what I have that kind of works: =IF(AND(TODAY() >= Start@row, TODAY() <= Finish@row), 1, 0) However, this formula does not work if the project happened during the…
-
Nested If with Multiple Check Box to Status Type
I was hoping I could get some help here. So I've decided to create a status for each of the tasks my employee is working on. Categories include: Done On Deck In Progress Not Started There is a checkbox column for each category. IF([Done]@row=1,"Done" IF([On Deck]@row=1, "On Deck" IF([In Progress]@row=1, "In Progress"…
-
Automating RYG balls to change colors
So i have parent cells and dependencies. The dependencies have color balls (red, yellow, green, grey). Here is what I want: If one of the dependencies has a red color ball, I want the parent ball color to be red. If all dependencies have a green ball, I want the ball for the parent to be green If all dependencies have a…
-
Lookup Table Question
I'm trying to figure out a way to apply my vlookup or index/match combo as a dynamic formula across my sheet. I have a set cell that I can use as the beginning of my array of cells to reference but I want the formula to update the final cell based upon the row I'm at minus 1. There may be a much better way of handling this…
-
SumIf with Date Range
I created a project management financial sheet (client / projects / deliverables & invoicing). I am trying to sum the value of invoices between a date range (screen grab). This is what I thought would work: =SUMIF(DTI:DTI, AND(@cell >= DATE(2019, 10, 1), @cell <= DATE(2019, 10, 31), InVAT:InVAT)) and it returns "0"? The…