-
Need help on a simple IF/AND formula but already spending too much time on it
Looking to show 'Delay' for tasks that have %complete <100% and Finish date < than Today (). Tried multiple variations but some records come right and some just ignore the %complete record. Why? Formula is: =IF(AND([% Complete]@row < "100%", [EMD Finish]@row < TODAY()), "Delay"). This formula is showing "Delay" on tasks…
-
SUMIFS with Date Range & Condition
Hello Smartsheet Community, I am hoping you can help me with this simple formula. =SUMIFS([Start Date]:[Start Date], <=DATE(2024, 3, 1), Timeline:Timeline, "Tender Submitted", [Quote Total]:[Quote Total]) It is coming back as an invalid operation. Can you help? Thank you in advance.
-
Interest Calculation based on multiple column
I am attempting to calculate interest based on a past due value in one of 4 columns. I need this formula to calculate all 4 columns because the amount may appear in one column this week and move to another the following week. I am able to get it to calculate using one column but when i attempt to add more columns i get an…
-
Incorrect WeekNumber or(??) Year when trying to return a 'Week Number of Year' calculation
I am trying to return "Week Number of Year" (I want two digits for the Week Number hence the workaround in the formula) and all my weeks start on a Monday. I have used the following formula which works great until you get to Monday 30 Dec 2024 where it returns "01 of 2024" - clearly wrong as Monday 01 Jan 2024 should be…
-
Formula to subtract one month from a date
Hello. I need to subtract one month from a date. I have tried =Date-30, and then minus 60, etc. By month minus 5, I was quite a bit off from the first of the month. If the Date being evaluated is say 4/1/20, I'd like the first calculation to produce 3/1/20, and the next row to produce 2/1/20, and so on. I did find a…
-
Status Health using IF/AND Statement with Multiple Criteria
Hello, trying to create Review Status Health (R,Y,G,B) colors based on outcome of two criteria: Manger Review and Variance in days between two events. Manager Review column has (4) choices in a dropdown. (Approved, Not Started, Rejected, WIP). If Manger Review equals "Approved", Status Health should equal "Blue", does not…
-
Date Formula- 60 days out
Hi there, I have this formula that I am trying to create on my metrics sheet. Goal: A formula that counts the instances where the state is not empty and is "Alabama", and where the date is before 60 days I have a lengthy archive and want to see metrics from the last 60 days of form submission date. Formula I have:…
-
How to capture Actual Finish Time for a Cutover Task
I have created a cutover Plan in Smartsheet and all tasks in the plan has these columns : Task, Duration, start Date and Start time, End Date and End time, Actual Start and Actual Time, Actual Finish Date and Actual Finish time. I am able to calculate start and end dates based on duration but I’m only able to capture…
-
Organize the returned value JOIN + COLLECT (?)
Hi guys, could you help me with a case? I don't know if it's possible, but is there any possibility of organizing the values returned to the cell in such a way that, starting with a symbol, the rest is organized right below, as in the next paragraph of the cell ? I'm saying this because with the combination of JOIN +…
-
Formulas Not Automatically Updating
Hi, I have a sheet with formulas in multiple columns. Non-licensed staff are able to add rows of data via a form, and also make updates to existing rows of data via a dynamic view. They view the data via a report. I have this same set up running for 33 separate locations, however one location has been indicating for the…
