-
Assign a value based on a date range
Hi everyone, Fairly newish user when it comes to formulas here; please forgive me if this has been answered elsewhere and I missed it in my searching. I'm wanting to assign a value to a column named Bucket based on the value in the Start column for any given task. The date ranges look like this (using a numbered list for…
-
Index / Match Multiple columns from another sheet
I tried to use index/match to reference multiple columns from a resource sheet. I can't seem to get the formula correct. My table (resource) has a list of teams with start times, parking information, notes, etc. The table includes all the team information. When I add a name to the new sheet, I assign that name to a team.…
-
Count of Cells less than a Date
Hello, I am working on a COUNTIF Formula which is not working. The whole formula which works: =SUMIF(Workstream:Workstream, ="Asset Mgmnt", [HIDE ME % COMP]:[HIDE ME % COMP]) / COUNTIF(Workstream:Workstream, ="Asset Mgmnt") Where Workstream is a drop-down column, and Hide Me % Comp is a % column. Focusing on the second…
-
Decimal Rounding + Adding letters to formula
Hello! I'm trying to have this formula round + add a "w" to the end but I cannot seem to figure it out. The formula is: =(Due1 - Due@row) / 7 + "w" I would like it to say 12.9w but it keeps saying 12.85714w and I've tried using the "Decrease Decimal" option and it does not work. Due1 = 2/24/20 Due@row = 11/26/19 90-day…
-
formula that checks if a project happened during the current month
I'm trying to create a formula that checks if a project happened during the current month. Example: Project Start: 09/09/19 | Project Finish: 12/13/19 Here is what I have that kind of works: =IF(AND(TODAY() >= Start@row, TODAY() <= Finish@row), 1, 0) However, this formula does not work if the project happened during the…
-
Join Collect
Hi i'm trying to find all of the timekeepers that worked for a specific client. Normally I would use Index Match however, there are multiple time keepers that worked for the client and index match only shows one name. For Index match I use the following formula: =INDEX({TimeKeeper}, MATCH([Matter Number]1, {Matter Number},…
-
VLOOKUP #INVALID DATA TYPE Challenge
Hi! I'm new to the community and already raising an issue. Sorry about that! If anyone can help me with this, I'd greatly appreciate it. I'm trying to use VLOOKUP within a Product Roadmap sheet to set the values of the feature start date and end dates based upon a Sprint it is assigned to, referencing a set of scheduled…
-
Join Collect
Hi I'm trying to to create a sheet that tells me all of the timekeepers that work on a client from data from another sheet. Currently I have sheet that list all of our clients and I will like to add a column that list all of the timekeepers that work on the project. Normally I would use Index Match however, some clients…
-
Formula Creation l Conditional Formatting (Automating the Process)
Hi! I am attempting to generate a formula to mimic the one I created using conditional formatting (essentially, I would like to automate the conditional formatting trigger). Here's how I would like the sheet to work. Based on the "Risk Classification" drop down selected, I would like the sheet to pre-fill the "Periodic…
-
SumIf with Date Range
I created a project management financial sheet (client / projects / deliverables & invoicing). I am trying to sum the value of invoices between a date range (screen grab). This is what I thought would work: =SUMIF(DTI:DTI, AND(@cell >= DATE(2019, 10, 1), @cell <= DATE(2019, 10, 31), InVAT:InVAT)) and it returns "0"? The…
