-
MONTH/YEAR formulas based on date column
Hi there, I have a column for "Report Date", "Date received", and "Month/Year". In my "Month/Year" column, I want the report date to be written out instead of 1/6/21 as January 2021. However, sometimes our "Report Date" column is blank/has the word "Skip" written when we don't have to write a report for that row. When that…
-
Ranking values in a range based on matching a date
Hi Smartsheeters - I'm in a post-Covid brain fog and trying to work this problem. I have a list of materials. For each material, I list a warehouse, a quantity of scanning errors, and a date. I have a rank column I need to populate with the top 5 highest quantity for a given date at a given warehouse. Here's a snapshot of…
-
Multiple IFs help
I am trying to create a formula with about 10 if conditions. It works with two, but as I add the 3rd I get an error. Here's the formula =IF(AND(Publication@row = "Architectural Record"), " https://www.architecturalrecord.com/call4entries/forPublication", IF(AND(Publication@row = "BD+C"), "Word count: 500 – 1,200; 4 - 5…
-
Combining IF and OR
Hello, brain trust! I have a conundrum that seems easy enough but isn't quite working for me. I'm trying to reference a range in another sheet, and I only want to count cells if they are within a specific date range and have a status of either "Not Started" or "In Progress." I know that to count one category, the formula…
-
COUNTIFS returning 0
I am having an issue with the formula below. I am attempting to get the count where the year of {Conv Rec 020} equals the cell "2020" and the month of {Conv Rec 020} equals 1 (aka: January). I have quoted the 1 and get the same results. The formula is returning "0"... any thoughts to return the correct values? It should be…
-
A workflow has become invalid
Can anyone help me with this? I'm getting an error (below) which I've had in the past and resolved quite easily. This time though I can't find the duplicated name. I do have a system column in both which generates a number but they have different names, is this the problem? Rows can't be added to the destination sheet…
-
Return value in bottom of column
Is there a way I can get a cell to return the value of the last row? I have a sheet where we all add new info at the bottom of the sheet. I need a summary row to send out the most recent totals to my boss on a weekly basis.
-
Formula for project status based on where a project is at
How would I write a formula to automatically populate a status column with red, yellow, or green based on where the project is at? In the screen shot below the Implement Asset Works is on-track because the gantt bar is filled in to the dotted line. The Implement Salesforce is behind schedule since the gantt bar is only…
-
Multiple IF/AND/OR with Dates and Checkbox
I am trying to have a date populated by a formula below is what I have tried and I know individually they work. =IF(AND(Event@row = "Event A", [2nd_Event]@row = 1), Date:Date-25, IF(AND(Event@row = "Event B", [2nd_Event]@row = 1), Date:Date-30)) In both cases 2nd_Event needs to be 1 (Check box) If it was Event A, I need…
-
Nested COUNTIFs within Date Ranges
Hi Smartsheet Experts, I need some help creating a formula. The Source sheet contains the data and I have formulas on another sheet; therefore, the formula will contain cross-sheet references. Source Sheet looks something like this: I want to count the number of Items that have an ORIGINAL or NEW date in January 2021 but…