-
Looking for solution to "Parent vs Ancestors" problem
Hello, I have been having tons of trouble coming up with a formula that will give me the desired result. The goal, it to populate a column with two pieces of information from other columns. In the attached image, the column "project name and header" is supposed to take the ancestor of SLS Job Number and the ancestor of…
-
Standardising imported date formats
Hi, I hope you are all well, and someone may be able to assist: On a sheet I am working on, a 3rd party app runs an automation that imports a date into a specified column in smartsheet. The date when imported arrives in the following format: "2023-01-04 12:26:14". There is no way to change the format which this date is…
-
Is there a formula to calculate years, months, and days between 2 different dates?
I found a formula that calculates the years and months between 2 different dates, but because it's not also calculating the days, it is rounding up the months which isn't going to work in the case that I need it. Here is a picture of what I'm looking for (how many years, months, and days) Purchase Date to Expiration Date.…
-
Index Match Else
Greeting All, I have created a simple index match that successfully matches state/province from my reference datasheet to pull in the corresponding district. I would love to indicate "N/A" for the instances when a state is blank but I cannot figure out the argument. =INDEX({District}, MATCH([State/Prov]@row, {Reference…
-
If And Formula for between dates
I'm trying to write a formula to return a "1" if the reference date falls between two dates. The highlighted Q1 should show a result of "1" based on the criteria further down the sheet as shown below. =IF(AND([Planned Date]@row >= $[Actual SIP Date]$177, [Planned Date]@row <= $[shipped On time]$177), "1", "") returns a…
-
#UNPARSEABLE error
Hi everyone. New to smartsheets and im having issues with my formula. Works fine on google sheets but dont know how to get it working on smartsheet. Please see an example:…
-
COUNTIF using 2 criteria
Hi, I'm trying to count the number of accounts using Experiencia with following criteria: Rounding Status is Operational AND EHR Status is NOT Operational. I'm getting an unparseable error with this formula: =COUNTIF([Experiencia Rounding Status]@row = "Operational", [EHR Integration Status]@row <> "Operational") Next, I…
-
Reports - calculating the gross margin rate
I am using Reports to bring data from multiple sheets in a project, but i need to calculate the project GMR (gross margin rate), how can I do this??
-
Countifs with blank "0" Value
Hi I am trying to put a formula together that will return blank if the value is zero based on 2 criterion.. example =COUNTIFS({Range 4}, ="Food & Beverage", {Range 3}, ="Unqualified" > 0 "") So basically if the value of unqualified is zero, I want the cell to be blank.. any tips?
-
#INCORRECT ARGUMENT error
Hi (again!) From other answers I've found here I'm guessing this is a parenthesis issue but I can't work out which ones. =COUNTIF({Clinical Trials : Accepted Range 1}, >=TODAY(), {Clinical Trials : Accepted Range 2}, HAS(@cell, "Blacktown")) Trying to sort current contracts (expiry date today or later) by research site ie:…