-
Dropping day from date format
Hi Smartsheet Community, I need to report a date field in a report, however the client only wants to report mm-yyyy instead of the full date (dd-mm-yy). For example, in the fields below they want to report 09/2020 and 04/2021, and 08/2020 and 08/2020 respectively Is there anyway to drop the day component in another column…
-
Populate Q1, Q2, etc with IF function not working
Hi there, I currently have a formula that is working for Q1 & Q2, but for some reason, it is not working for Q3 & Q4. I am at a loss as to why this is not working properly. Any ideas? =IF(MONTH([End Training Date]@row) < 4, "Q1", IF(MONTH([End Training Date]@row) < 7, "Q2", IF(MONTH([End Training Date]@row), 10 <"Q3",…
-
Multiple users in dropdown formula
I'm trying to countifs a user is on target. My sheet column is a multiple selection column, some rows have multiple users selected. When I create my formula, it doesn't count if said user is sharing a responsibility with someone else. Here is my current formula =COUNTIFS({2021-2022 COMPLIANCE WORK PLAN Person Responsible},…
-
Formula for percent match to a multi select column
Hi, I'm working matching mentors and mentees. We would like to match them based on attributes they both selected in a multi select column. As in, choose your top three competencies. Now I would like to make a formula to determine the percent match of a mentee to a mentor's selection. That is Mentee 1 selected A, B, and C.…
-
Formula Question for Percentages into Risk Status
Hello, I am trying to have the formula below also reflect that if the percentage is 0% it automatically has the risk status show as blue. Could someone please assist with this? =IF([% Complete]@row = 1, "Green", IF(AND(Finish@row <= TODAY(1), [% Complete]@row < 0.51), "Red", "Yellow")) Thank you!
-
SUM IF formula upon specific criteria
Hello, I was looking for right formula to SUM IF upon specific criteria (KMF662), and how many fuel has been used over they May (sum only May). I want to apply this formula to each month separately, according to a separate car. Thank you for your answers in advance.
-
Summary reporting with conditional formatting
Hello! I'm trying to build out a way to dynamically report on 58 state/territory sheets. Ideally, this report would have the total number of rows in the state sheet, plus the number of rows that meet two separate sets of conditions, with conditional formatting applied. I've tried doing this two ways: By using a sheet with…
-
Exclude and If statement then "" and based on date then "Return Value"
=IF([Tier Status]@row <> "Group D", IF([Escalation Required]@row, <>"", (IF([WM Next Meeting Date]@row = "", "No Meeting Scheduled", IF([WM Next Meeting Date]@row = TODAY(-14), "Pending", IF([WM Next Meeting Date]@row > TODAY(-1), "Scheduled", IF([WM Next Meeting Date]@row < TODAY(-14), "Overdue"))))),"Escalated")) What…
-
I want my Status column to give me a red circle when it detects a flag in my Critical column.
=IF([% Complete]@row = 1, "Blue", IF([% Complete]@row > 0, "Green", "Yellow")) I want to add there to turn red when it detects a flag in my Critical column. Please, help
-
Need to return a value with INDEX(COLLECT) but not if cell is BLANK
Hello, I am unsure if what I am doing is possible or not but I need to return a value with specific criteria. I need the latest "Revised Date" from the most recent amended version (Occurrence). However, it is possible the latest amended has no updated "Revised Date" field so it should keep the previous revised date.. In my…