-
Calculate with dates
Hey smartsheet-community, I am trying to create a sheet where the end date is fixed. From there, I want to subtract different number of days (at best only working days) based on the complexity of individual steps and thus get the date at the end on which the project should start. Here an example that hopefully helps to…
-
Add days to a date
Hi, I am trying to create a column that adds a certain number of days to a date. When I use =[Task start date]@row +7 . It doesn't add 7 days to the date, instead just adds the number 7 to the end of the date. I have also tried to use the WORKDAY formula as an alternative, =WORKDAY(Task start date]10, 5) . This results in…
-
Formula to populate a "Year" if Create date falls between 2 column dates
I created a form that request the Calendar year for funding. They range from 2022 to 2028 and each year has Quarters. Sample quarter is between April 15 - July 15 so if I want the formula to populate me a Calendar year 2022 if the create date falls between the April - July dates. Here is the formula that works but is…
-
Count Days Since Ticket Opened
Hello, Needing to calculate number of days since line item appeared when Date Submitted column is "Auto-Number/System Generated Column Created (Date)" -- and then display "Closed" when Checkbox is checked in Complete column. Can't get the "Complete" to appear -- just goes blank. =IFERROR(IF(Complete@row = 0, TODAY() -…
-
Using Index Collect formula when there are multiple matches in the source sheet. (#Invalid Value)
Hello, My goal with using INDEX(COLLECT)) was to obtain information based on 3 different criteria, but in my source sheet there are multiple matches for the three criteria specified. What do I need to add to the Index, Collect formula to overcome this or do I need to use a different formula combination? Below is the…
-
Total sum of results of a formula?
I have a formula to calculate payouts as follows: =IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 75000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <= 250000), "$2500", IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 251000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <…
-
Red Flag for Overdue Tasks, without also Flagging non-dated ones
We are trying to use a formula to do the following: •tasks that have been cancelled are gray •tasks that are past their due date are red •tasks whose due dates come up within the next 5 days and are not yet complete are in Yellow •tasks which are already completed or whose start date hasn't hit, and are due in 14 days are…
-
Combined IF, AND, & OR Statments
I am having trouble with a combined IF, AND, & OR formula. The formula is to check five columns for a Yes "Y" value. For two of the columns only one value has to be Yes. If all are true then return a 1, if not then return a 0. Where is my formatting wrong? =IF(AND([SPC Received]@row = "Y", OR([SDS Received]@row = "Y",…
-
Formula to create symbols based on parameters
Hello Community, I'm attempting to create a formula that will create a green, red, or yellow dot symbol based on parameters and values within given cells. The formula I have thus far is below but I continue to receive error messages Any help would be appreciated. Thanks!
-
Dashboard with Filters - Best Practice
Hi all, I am pretty new to Smartsheet and will need your help to build a dashboard. What is the best practice to create one? GRID I have multiple salespeople in my team, each of them have their own grid. The grid looks something like this: ID | Date | Stage | Comments I am creating forms for them to fill up on a daily…