-
DATEDIF for %Complete
Hi! I am wanting to write a formula to automate %Complete column. I have the start date / duration / end date. In excel I would write - =DATEDIF(A1, TODAY(), "D")/B2 - to find the difference between the start date and today's date and then divide by the duration. How do I do this in smartsheet? Thanks in advance!
-
How to add date to formula.
Hi so I am using this formula =SUMIFS([Number of Slides]:[Number of Slides], Done:Done, 0, Status:Status, "Not Started"). I want to also only filter by a certain date. how do I add that. There is another column called "due date" and there are dates in it. How do I only look at stuff due this month by adding it to the…
-
VLOOKUP + MATCH on column headers
Hi, I have a reference sheet set up with a list of milestone names/build versions/dates and am trying to use VLOOKUP + MATCH to auto-pull those in a 2nd sheet. For example - Sheet1 uses =VLOOKUP("ZBR", Milestones1:[2.0]7, MATCH("1.4", [Column Headers], false)) Where "Milestones1:[2.0]7" is a table of milestones/build…
-
IF Statement
Hello, I"m trying to setup a formula that if the reference cell = 0, then it prints zero (instead of @INVALID OPERATION) I can't seem to determine why I keep getting unparseable, not matter how I change it around. =IF ([Brand Funded Scanners]@row = 0, "0", [Brand Funded Scanners]@row * 259) Appreciate any assistance!
-
Index Match Issue
Hello, I seem to be having an Index Match formula issue that I cannot figure out. I've used this formula many times and never had an issue. The screenshots attached should explain fully what is going on. For some reason, it seems to be returning the same (wrong) value in multiple cases in the formula. Thanks for any help…
-
Health formula from Status
Hi all. I'm new and trying to tweak a health column formula. We've decided to remove our % Complete column which we'd originally used. Original Formula: =IF(AND([Due Date]@row < TODAY(), NOT(ISBLANK([Due Date]@row)), OR([% Complete]@row <> 1, ISBLANK([% Complete]@row))), "Red", IF(AND(NOT(ISBLANK([Due Date]@row)), OR([%…
-
Error with COUNTIFs and date formula
Hello! Can someone please advise what am I doing wrong getting this error? The formula used is as follows: =COUNTIFS({ref sheet}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2021)) The date column in the reference sheet is set as "Date" and the format is DD-Mon-YYYY (e.g. 19-Jan-2021). I've tried creating a default date column in…
-
Report or formula to show time individual cell was changed?
We would like to be to see when (timestamp) changes are made to individual cells in a row without having to manually check cell history for the cell(s) of interest. Is there a formula we can use to do so? Or is there a report we can export to quickly be able to examine this data point without having to manually check cell…
-
Split a number using LEFT and Right Formulas
I have a location number that is 6 digits long. I am trying to create a formula using LEFT and Right to add a dash after the first 4 numbers. EX: if the location number is 123456, I want to create a formula to show 1234-56
-
Help on IF Formula - Multiple Statements
Hi! I need some help on a IF Formula statement. On my sheet, I have a column that is using this equation - =IF([Total ALL Athletes and Unified Partners]@row = 0, "0", IF([Total ALL Athletes and Unified Partners]@row <= 5, "1", IF([Total ALL Athletes and Unified Partners]@row <= 74, "2", IF([Total ALL Athletes and Unified…