-
Formula needed that will generate quantity in one column against date in another column = how much a
I have the following columns and need to return the value of something from a date through the end of the year. 1. Est Mo Contract Count - A number 2. Ave admin - Dollar Amount 3. Estimated Value - 1 x 2 4. Start Date - Start Date that 1 actually goes into effect 5. Weighted Value - Sum of 4 through end of the current year…
-
Determining duration in Hours:Minutes
Hey guys, I have read several posts with several workarounds for calculating a value between a Start Time and an End Time where the input and result need to be in HH:MM. I have an idea and a beginning: 1. I created two System Columns. [Start Time] as 'Created (Date)' and [End Time] as 'Modified (Date).' 2. I created a…
-
COUNTIF with dates and years
Hello, I am trying to write a formula to count executed leases for 2019 and 2020. I do not have a lot of experience writing long formulas but basically the logic is count if there is a date in the Lease Signed field AND if grand opening is between 1/1/19 to 12/31/19 or 1/1/2020 to 12/31/2020. Can anyone provide some…
-
JOIN text function over multiple ranges
I am trying to create a formula that will join a group of element IDs from two entire columns. My latest failed attempt is below: =JOIN([Element 1 ID]:[Element 1 ID], [Element 2 ID]:[Element 2 ID], ", ") I'm not sure if I just have the syntax wrong or if the JOIN function is not capable of including multiple ranges. I…
-
Calculating Month from Date Column
Hello, I am looking for help with the formula below. I am referencing another sheet and trying to calculate the number of rows that fall within January in the 'Milestone Due Date' column. The dates in the 'Milestone Due Date' column displayed in the following format: mm/dd/yy. =COUNTIFS({Project}, [Primary Column]@row,…
-
My first automatic RYG/RAG schedule status formulas
Hi everyone. This is my first new discussion. I wanted to share some automation I put into my schedule that made it much more friendly. First, I have a few key columns: * Status (Drop-down, manual entry, "Not Started" or "In Progress" or "Completed") * % (Manual, progress on completing a task) * RAG (Automatic, shows Red,…
-
Formula for scores between two numbers
Hello I have been using Smartsheet for quite a while however I am really stuck on this formula. I've been searching and reading and none of the things I have found work and I'm thinking it should be this hard. I'm obviously missing something. I have 2 columns with scores in them (that had been calculated using another…
-
Complex IF AND Formulas
I'm trying to automate the RYG circles according to several columns of a Smartsheet grid for project management purposes. The logic is below (I've also attached a visualization of the logic): IF [Proposed Product Launch Date] <TODAY, "Gray" IF [Proposed Product Launch Date] >=TODAY AND [Deviation from Proposed Launch Date]…
-
Why won't my Nested COUNTIFS work?
Hoping the community can assist! These two COUNTIFS work separately. =COUNTIFS({Finish}, >TODAY()) =COUNTIFS({PctComplete}, <1) But why isn't it working when I nest them together? (it returns #Incorrect Argument Set?) =COUNTIFS({Finish}, >TODAY({PctComplete}, <1)) If you care to, I'd appreciate answers to a few additional…
-
Multiple If Statements
I have found quite a bit of useful information to make Nested If statements; however, they are set-up to be "if this, then that or if this, then that". What I need is: "If this is equal to this, and this is equal to this, then this" Here is what I have come up with: =IF(AND(Firm2 = {Building Code}, Firm = {Firm},),…
