-
SUMIFS to Calculate Values for a Specific Date Range, using cell value for Date Reference
I have a formula that sums multiple statements, but the one I have I need help with is the one that references a range in dates. =SUMIFS({Reference Hours}, {Reference Drafter}, [Primary Column]@row, {Reference Rework}, Rework1, {Date}, >=DATE(2023, 10, 26), {Date}, <=DATE(2023, 11, 1)) I want to be able to make the dates…
-
IF formula for conditional formatting
Is there also a way to do an IF statement for if I have conditional formatting active in the row (highlighted yellow in my example), that it will set the Project Status field to Submitted? I have conditional formatting to go yellow for a new submission (from my SmartSheet form) into my tracking sheet, where the Status…
-
Using IF(LEN) to add proceeding zeros to a number based on length
I want to add a set number of zeros to an employee ID based on the employee ID length to make all IDs 7 digits. Some long tenured employees only have a 4 digit employee ID where as a newly hired employee have a 7 digit ID. Ex. Employee 0002709 vs New Employee 0148749. All employee IDs are entered without the preceding…
-
Auto checkbox formula when cell contains multiple values
Hello! I am trying to create a formula that automatically check the box if the "Year upgrade or addition" cell contains the year 2012 or later. In my screenshot, you can see this cell contains the years 2007 and 2016. Since 2016 matches my criteria, I need the ADA Complaint? column to be checked. I tried =IF([Year upgrade…
-
IF statement question
Hi! I'm trying to use an IF equation and am running into unparseable. I want to say if my Project Status column says "Submitted," that the column next to it, which is called Status Details, automatically is filled with "Not Yet Triaged"
-
How to determine if more than 1 or 2 years between two dates (accounting for leap years)
We issue certificates that expire the date they were issued + 1 year (or 2 years). For example, a 2-year certificate issued 6/1/23 will expire 6/1/25. Occasionally, we get requests for special certificates that may be valid for less than or equal to the 1 year or 2 year term. We have a separate Smartsheet for approving…
-
Find Unassigned Team with JOIN(COLLECT(
[Allocated Team] is the team that the project has said they need and at what allocations they need them, JOIN(COLLECTed from the children of this parent row =JOIN(COLLECT(DESCENDANTS([Employee Name]@row), DESCENDANTS([Start Date]@row), @cell < TODAY(), DESCENDANTS([End Date]@row), @cell > TODAY()), CHAR(10)). [Assigned…
-
I have a problem with getting a formula to deal with the difference in GMT and NZDT
I have tried to add a date only column =DATEONLY(Created@row) that feeds off the Created column, but am finding that any entry prior to 1pm is showing the previous day's date. We are GMT + 13 at the moment in New Zealand as it is Daylight Savings but would normally be GMT + 12. I am not that good with formulas so any help…
-
Adding an OR to an IFERROR
Hi Community, I have a formula that looks up an ID I enter in a new sheet and pulls in its title from an existing sheet. =IFERROR(IF(ISBLANK(Task@row), "", INDEX({Master Range 1}, MATCH(Task@row, {Master Range 2}, 0))), "") This works great, but now I want to tell it to search 2 different sheets to search for the title.…
-
IF Formula to give me a $ amount when criteria's are met
I am working on a calculation model for my company and essentially I am looking for Smartsheet to give me the following information. I currently have the formula for the first part figured out, I need help with the 2nd part. If the "Quantity is <= 10 the amount should come out to $13,500.00 I need this formula to multiply…