-
Counting unique dates
Hi I would like a formula that just counts the unique days when multiple dates are entered, they don't always overlap so I can use the min and max function. Like below there's no "active" for the 1st & 2nd Feb so these dates shouldn't be counted. I've added the column Unique Hold Days as this is the result we're after.…
-
Struggling to pull info from one sheet into a consolidated summary on another
Hi there - I'm really struggling to do something that I think (?) should be fairly easy. I have a main sheet with names & number of kilometers travelled by day. I'd like to create a separate summary that shows an overall view with EACH NAME listed and the relevant total number of kilometers. I can do it in Excel but can't…
-
I want to return a negative number as this sheet will be used for our accounts payable team.
I'm using a simple Sum formula. I want to display this as a negative. conditional formatting to change the color will not work. It needs to either be -$192,000 or (192,000) =SUM([2021 Total]1:[2021 Total]46) = 192,000 I've tried sumIF with no luck too. it's really just adding up all the values but I need the total to be…
-
Formula for replacing specific characters
Hi everyone! I am struggling with the formula language for this. So in Column 1 I have the file names with their file extensions. In column 2 I'd like a list of the filenames WITHOUT the extensions (see the top row) Is there a way to write a formula to replace the LAST 3 characters with nothing? Thank you!
-
How to set up 150 character limit to a field in a form?
Is it STILL the case that there's no way, using Smartsheet forms, to impose a character limit on a form field submission? I would like to set a 150 character limit to a field in a form. Is it possible? If so, how can I do this?
-
Formula for symbols time and text dependent
Hello! I have a problem, maybe you can help me! What I want is that if in column "1" it says " RCA started" but the date from column "2" is greater than today's date, then I want a red symbol to appear in column "3", but if in column "1" it says " RCA finished" then I want a green symbol to appear in column "3". If date in…
-
SUM of the past 3 years
=SUMIFS({Contract Value}, {Exp.Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()), {Area}, "South") This is supposed to COUNT the sum of the past 2 years, but when I changed it from 2 to 3 to find 3 years, it remained the same. How do I change this so it counts the past 3 years?…
-
Counting dates in a column
Hi, I am trying to count cells in a column to determine: (a) which cells have dates in them? and (b) which cells have dates in the past (older than today) Would appreciate if anyone could provide some guidance. Thanks.
-
I'm getting a formula error on half of this formula
I need to evaluate how many days off a person is going to get depending on their location. Offshore or Onshore. We have Onshore employees and Onshore contractors as well as Offhore employees and Offshore contractors. The formula below evaluates if someone is an Onshore or Offshore resource then uses a range from another…
-
SUMIFS With multiple criteria
I am having problems wrapping my head around this particular formula. =SUMIF({Follow Up Contractors}, Contractor@row, {Follow Up Lighting}) is what I have so far, which works but it isn't drilled down like I need it to be. Logically, I am after SUM (range 1) IF (range 2) matches Contractor@row AND is within date range…