-
Day counts
Hello, I am working on formulas to gather our end date/past end dates for example I want to capture 45-day, 30-day, 15-day, 10-day and 5-day I do not care if it is in a pie or metrics just not sure the best way to gather the data with there already being a formula in the column to calculate those days then present them on…
-
Why am I observing this odd behavior with a "Created (Date)" column?
I think I've found a bug. I have a column that is of type "Created (Date)" called "Date" and another column called "DATE TEXT" which is a text column. DATE TEXT = Date + "" DATE TEXT is calculated by appending a null string to Date. I have noticed erratic behavior in another sheet that references this column, and I believe…
-
Nest IF Statement
Hello, I could sure use your help. I have multiple nested IF statements that I cannot seem to get to work correctly. Here is what I am trying to achieve: IF Date is Blank, Leave the cell blank IF MONTH = 1, return Jan IF MONTH = 2, return Feb, etc, etc. I can get the blank part to work but it won't seem to go past that to…
-
Database Duplicate entries (Date & Item Based)
Hi All, I am struggling with a Item & Date based duplication formula. 1) For ease of reference I have filtered the database on Item "WL04". Various "ITEMS" with unique id's gets submitted into this database. 2) There can only be one entry per "ITEM" per "DATE" 3) As can be seen on the screenshot there are two entries for…
-
Return a Minimum Date with A Condition
Hi there I am trying to adjust summary formulas so that they only return information from rows considered In Scope (so that data does not have to be removed from Not In Scope rows). Can anyone help with a formula that would return the MIN date from this group, only for the rows In Scope, ie Out of Scope is checked / true.…
-
Better Understanding of formula
I have the following formula in one of my sheets that I modified from one of the templates. It works just fine but after review the IFERROR information online I am still confused. At this point I don't really understand how the "52" plays a role in the formula other than knowing there are 52 weeks in a year. Can someone…
-
Converting text to a numerical value
I suspect there is a very simple solution to this. My knowledge of formulas is minimal and I want to make sure I am setting things up to work as I want them to work. In the example below what I want to do is to assign a numeric value to each of the text responses. (Very low = 1, low = 2, etc.). I would do this in both…
-
Formula to add "No" to a blank cell, but leave value if not blank
On a Smartsheet project intake form, I have several Yes or No dropdown selections. If Yes, the user must answer additional questions. If No, they do not and those cells are left blank. Auditors do not like Blank cells, so I'm looking for a formula to replace those blank cells with "Does Not Apply" but leave a value there…
-
Calculate the time between two dates by days, hours and minutes
Hi, I am trying to implement the following formula from my excel spreadsheet into my smartsheet: =INT(O2-Q2)&" Days "&HOUR(MOD(O2-Q2,1))&" Hour "&MINUTE(MOD(O2-Q2,1))&" Minutes" Basically, i need to calculate the time between two columns (date received) and (date documents issued) in days, hours and minutes. Cell 'O2'…
-
Weighted Average With a Condition
Hi Is it possible to gather a weighted average, with a condition? My starting formula is but I would like to build in a condition. In other formulas this has taken the form of I can't work out how to bring these together / if it is possible. Grateful for any thoughts anyone may have! Emma