-
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…
-
Add Dates into Summary Rollup Values as Text
Hi! I have a sheet with a bunch of summary data at the top (which will be used in reports and Control Center). Two of the summary fields I want to capture are project Start Date and project End Date which are based on the start and end dates from the plan. However, my summary fields are text fields instead of date fields…
