-
Expected % Completion, Schedule variance and health formulas
I have set up a series of calculations to determine expected % complete, schedule variance and the health (red, yellow, green) of a task given those variables but I am not sure it is capturing all scenarios that may need to be considered. Expected % Complete formula: =IFERROR(AVG(CHILDREN()), IF(Finish@row <= TODAY(), 1,…
-
How to calculate days between two dates
I'm using NETWORKDAYS to exclude weekends, however, I'm getting an "Invalid Data Type" error. Both columns are date columns. =NETWORKDAYS([Date of Verbal Agreement]@row, [Module Go-Live Date]@row) Thanks for your help!
-
Subtracting Dates - Smartsheet is rounding date/time up
I am trying to subtract two dates to get the duration of time something stayed within a phase. Currently we are trying to use "CreatedDate," the auto-generated field as the "start" and another date field as the "finish", let's call it "Date entered Triage". The formula [Date Entered Triage] - [Created Date] works great for…
-
Overdue, Late Start indicators based on multiple criteria
I am trying to populate indicators if a task is overdue, late start or has a schedule variance based on the below criteria but the formula I have is return unparseable. I think I might need an OR feature? =IF(AND(TODAY() < Finish@row, [Actual Completion Percentage %]@row < 1), "OD"), IF(AND(TODAY()>Start@row, [Actual…
-
Check a box when a match for a value is found on another sheet
Hi everyone, I have a sheet called Parts Database used to track parts as they go through a production process. Screenshot below. Currently, workers check off the status of each part as they go, which is not practical with the quantity of parts. Ideally, I want them to be able to fill in a response form at the end of the…
-
Calculating how many time a value shows up in a column in a certain month
I have tried the formulas on here suggested to figure out how many times the word transfusion showed up in the Code column on a Date column February. I keep getting unparsable or invalid ref# . I even tried the IFERROR in case I had blanks but no go. I was able to figure out how many times the word transfusion showed up in…
-
How do i cross reference several sheets to pull duplicate data?
Hello, We have 4 business units and different physicians and hospitals we work with. I want to pull a report for overlapping physicians and hospitals across all 4 businesses. I can see the overlap in a report, but how can I highlight the overlap in Physicians and hospitals in separate reports? Thanks!
-
Can I use Or more than once in a sumifs formula?
the formula below sums the time served column for just " SPD minor" for January and does not add zeroes to my dashboard. I would like to combine the time served for SPD Minor, SPD Major and SPD continued. =IF(SUMIFS({N TS}, { N Intervention}, ="SPD minor", { N Date}, YEAR(@cell) = 2022, { N Date}, MONTH(@cell) = 1, { N…
-
Help with IF, OR, NOT, ISBLANK
Hello, I'm trying to get it to check these columns and if even one of them is not blank, then populate "1" into the column: =IF(OR(NOT(ISBLANK([WHS: Enter Date Received]@row)), NOT(ISBLANK([WHS: Enter Lot]@row)), NOT(ISBLANK([IQA: Inspector Email]@row)), 1, 0)) I keep getting the #incorrect argument error.... Any tips?…
-
Can't get this nested IF statement to work
=IF([Cost Incurred / Hired]@row = "Remove Cost", IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row = "Red Badge"), -1, IF(OR([Cost Incurred / Hired]@row = "TBH", [Cost Incurred / Hired]@row = "Cost Incurred / Hired", [Cost Incurred / Hired]@row = ""), IF(OR([Spend Bucket]@row = "Blue Badge", [Spend Bucket]@row =…
