-
Days Remaining until Due Date (minus weekends)
I have a countdown to the due date column, but right now it includes weekends. I would like to have business days only. My original formula was: The formula I thought would work was this, but got a #invalid data type error: Any suggestions? Thank you!
-
How do you create an "AverageIf" formula for more than 1 criteria in the same column?
Hi Team, I am using a formula calculation sheet to try to work out how to create a formula to average %s in a Smartsheet where the Line of Business Column (in some cases) has more than 1 Business unit e.g. Sales and Sales (Local Sales). I have tried Average If functions, AvgCollect, AverageAnd, but have had no luck. I just…
-
Formula works unless there is no date and then it returns the dreaded #Invalid data type.
=IF(Helper@row = "1", "January", IF(Helper@row = "2", "February", IF(Helper@row = "3", "March", IF(Helper@row = "4", "April", IF(Helper@row = "5", "May", IF(Helper@row = "6", "June", IF(Helper@row = "7", "July", IF(Helper@row = "8", "August", IF(Helper@row = "9", "September", IF(Helper@row = "10", "October", IF(Helper@row…
-
How do we apply COUNTIFs with multiple conditions?
I Have 5 Columns namely Phase 1, Phase 2, Phase 3, Phase 4, Phase 5 I need a count of each row item (Less than 100%) with a condition that if a task is less than 100% in multiple columns like phase 3 and phase 4, it should be only counted in the first column (Phase 3 in this example)
-
Latest Revision - without pulling Revision History
Hello! I could use some help problem solving this. I have a smartsheet tracking revision number and date for about 240 rows of submittals. I'm trying to come up with a formula that will tell me the latest revision for each row and will auto-update as new revisions are added. The goal being to have a clean print out which…
-
Does not contain this or that TEXT
Hello, We currently have a formula that works to say if the variation row contains E-Gift, the cell will say E-Gift Card and if it contains Physical Card the cell will say Physical Gift Card. =IF(CONTAINS("E-Gift", Variation@row), "E-Gift Card", IF(CONTAINS("Physical Card", Variation@row), "Physical Gift Card")) This…
-
Formula to retrieve most frequent number
Hi all, I believe I'd use MODE for this in Excel but can't find an equivalent in Smartsheet. I need to build a formula to retrieve the most commonly found numbers from a column in a referenced sheet. Does anyone have a suggestion for how this can be done without MODE? For example, if I have a column in another sheet that…
-
Capture modified date and time stamp
Hello. I have an system column that records modified date and time I also have one that records when the entry was added to the Smartsheet. We have 4 hours to respond to a new entry. What I would to do is when the status of a row changes to reviewing it would record the date and time stamp then we would use a formula to…
-
Formula help for averaging data in 1 column based on criteria from 2 other columns
I need help with a formula that will average the total in 1 column based on 3 criteria of another column and 1 criteria of another column. To phrase better, I have a column of dollar amounts that I need to average based upon the sum of all values in that column. BUT, the values to sum are based upon 3 status values of…
-
Extract date as date from date and time stamp
I am importing a date and time stamp (e.g., 9/3/2022 8:36:19 AM) and need to extract the date into a date field so Smartsheet recognizes the date. I have a formula to extract the date as text (i.e., 9/3/2022) in another column, but cannot find a formula that works to convert the month and day to two digits and converts the…
