-
RYG based on Done Check box and Due Date
I want to write a formula to turn ball red if done check box is unchecked and the date is less then or equal to today minus 2, yellow if unchecked and the date is greater than today -3 and less then today -5, and green if done box is checked =IF(AND(Done7 = 0, [Due Date]7 <= TODAY(-2), "Red", IF([Due Date]7>TODAY(-2),…
-
#INVALID DATA TYPE adding MONTH to COUNTIFS with cross-sheet refs
Hello all, using the following formula: =COUNTIFS({TACTICS design resource}, AND(@cell <> "", @cell <> "N/A"), {TACTICS status}, AND(@cell <> "Cancelled", @cell <> "On hold"), {TACTICS status}, <>"admin", {TACTICS project number}, <>"*N/A", @{TACTIC actual delivery date}, MONTH(@cell) = 12) I'm getting an #INVALID DATA…
-
Help with formula to populate a quarter (text) based off date field.
Hello! I've searched many posts on the forum and can't find a solution. I'm at my wits end, any help would be very much appreciated! I am looking for a formula that will show a text result of Q#-YYYY based on an entry in a date field ("Expected Delivery Date", which is using the standard SmartSheet Date column. I can't…
-
Formula For Finding Quarter
Hello, New to smartsheets I have been using excel extensively. Can someone pleased teach me the formula for finding a quarter based on a date? For instance [Date Data Column]1 = 1/1/2016 I want the formula to return "Q1" in the [Quarter]1 cell Havent been able to figure this one out...help please!
-
If multiple cells are not blank - check the box
I figured the formula to check a box in a column if one other column is not blank, but I can't figure out how to check the box if multiple cells within a range are not blank. I'm using this formula: =ISBLANK([Monday | Start Time]1) = false I have a form that feeds into a sheet that has options for meeting start time,…
-
How do I return a date based on a dropdown value?
Hi all, I am trying to work out a formula to get a cell to return a date based on a dropdown value. I need the cell to tell me when a matter is due, based on when the matter was received and the priority rating. the priority levels are (Service Type): * Fast track - 7 days * Basic - 14 days * Standard - 30 days * Complex -…
-
Count of Completed Tasks by Month across multiple sheets
I am trying to get a count of completed tasks by a team by month across multiple sheets. I've created this formula in the sheet summary: =COUNTIFS(Status:Status, "Complete", [Team Assignment]:[Team Assignment], "Lean"). If I'm trying to get a completed count of tasks for the month of September, how could I get that count?…
-
Countif range from multi select contains (value) under each parent from another sheet
I need a cross reference formula using countif a specific value in a multi select column is under a parent from another sheet.
-
Autofill formulas not applying per conditions
I have two formulas in my sheet, one that populates with different symbols and one that triggers a checkbox. One formula is using reference to @row while the other is using a specific row number. The formulas are applied to all rows in their respective columns. Neither of the formulas is getting updated when a new row is…
-
Formula
Hello, Can someone help convert this excel formula to SS? =IF([DAY COUNTER]1 = "1", =TODAY(), IF([DAY COUNTER]1 = "2", =TODAY() + 1, IF([DAY COUNTER]1 = "3", =TODAY() + 2, [DAY COUNTER]1))) Expected result is to determine that 1 = current date, 2 = current date+1, 3= current date+2 I'm not sure if I'm explaining it…