-
Round function within a formula
Hi! I am trying to round 2 numbers, multiply them and then take that sum and multiply it by another number. In excel it is =round(A1,2)*round(A2,2)*A4. I am having issues duplicating this in smartsheet. What am I doing wrong? I have rounded down the columns, yet when doing the calculation with out the round function, it…
-
IF Statements Date Range & Excluding Blank Cells
I need to build a status column that registers when any due dates in a project are nearing certain parameters. This is for projects that could have between 1 and 10 due dates. Formula at the bottom (it's a doozy). * Green if any due dates in a row are > than today(+7). * Yellow if any due dates are between Today(+7) and…
-
SUMIF(OR(???
I am trying to sum a column if either my Paid in 1st half in 2019 column is checked or my Paid in 2nd half in 2019 column is checked. I am trying to use a few different formulas without success. Here are a two that I've tried: =SUMIF(OR([Paid 1st half in 2019]:[Paid 1st half in 2019], [Paid 2nd half in 2019]:[Paid 2nd half…
-
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?…