-
IF Formula for Today's Date between Two Dates
I'm trying to create a formula that is only triggered when today's date is between two dates. Otherwise, I want it to be blank. I want the dates to be in the cells and not just in the formula. I use this year over year and it will be a lot faster to change them for next year. I get Invalid Operation when I use the dates in…
-
Nesting COUNTIF within an IF function (or the other way around?)
I'm trying to get a count by month for two columns if the row matches a criteria. So if the Planning Level Indicator equals "Level 1", count the number of if the date equals January 2022 in the Screen 1 and Screen 2 columns. The answer for the first row should be 1. But I'm summarizing for the month, so I'm using a cross…
-
Help with multiple OR
I am at a loss and getting an unparseable error with the below formula. I have several criteria which I am trying to satisfy with the OR function. =COUNTIFS({Role},"Operator", {SnowShift}, "Night", {Available},"Available", OR({Dept}, @cell ="Airport Field Maintenance", @cell = “Airport Facility Maintenance”, @cell =…
-
Circular Reference with Vlookup (Works in Excel)
I have a project management table I was hoping to make in Smartsheet. Basically, if a task has a "Predecessor", I'd like the "Start" for that task to be the end of the Predecessor task (whichever task has a matching "Task ID"). If a task doesn't have a Predecessor, I'd like its Start date to be the End date of whatever in…
-
Formulas
Good Afternoon... We are building a sheet to include a column must have 1 of 4 different criteria can be met as well as a True/False criteria formula. When completing the form, the fields are not populating correctly and if you copy the formula down the column, it puts False in each field. What is the best way to get…
-
Date (mm/dd/yyyy) from Week number
I am trying to get the date displayed from a week number. For instance this week is the 27th week of the year. I am trying to get it to display 07/05/2021 (the first day in the week). I was thinking of converting the week to day of the year (i.e. 27 * 7 = 189) and using the 189 to convert to a date, but I can't figure out…
-
Average formula - count dates
Hi, I have a column named "Applied" which has dates of when an application was submitted. there is another column called "HR Review Time". the HR review time has numbers. i want to make an AVERAGE of the number in the hr review time column for dates 11/11/23 until 11/10/23 in the applied column I created this formula but…
-
Cross-Referencing Assigned To Error
I am building out a roll-up sheet and have everything working nicely, with the exception of a single formula. The part of this sheet I'm having trouble with is in the image below. What I am trying to do is have this sheet look at the person named in the column "Primary", then check a couple other sheets to see how many…
-
How do I Check/Uncheck Parent if at least 1 Child is checked?
I have been using this formula: =IF(COUNTIFS(CHILDREN(), <>0), 1, 0) but if more that one child is checked in the column it comes up with #INVALID DATA TYPE . How can i fix this. The requirement is at least one Child row must be checked for parent row to be true if not false.
-
INDEX MATCH needs to pull blank if there's nothing in the cell
I'm currently writing a formula to pull the data from one sheet to another. My formula pulls the data in if there's information in the cell. If there's nothing in the cell it returns "#NO MATCH". How do I get it to return nothing if there's nothing there instead of NO MATCH? Here's my current formula- =(INDEX({RPA Last…
