-
Date over 30 days = "text" formula help
I have a column where a date will be entered. Based on that date in another column, I want to state "Pending" if the date is over 30 days past the original entry date. =IF([DeliveryDate]@row > TODAY(+30), "Pending") I'm not getting errors with this, however, the text "Pending" is not displaying when the DeliveryDate is…
-
COUNTIF (Counting a column based on other column Information)
Using 2 columns (Status) & (Tracking) I'm trying to count the amount of "complete" status in the Status column where the Tracking column is not Past Due. Below is the formula I created. =COUNTIF(AND(Tracking:Tracking, <>"Past Due", Status:Status, "Complete")) It's not taking the formula, any thoughts? Bonus Question* ***…
-
Is this even possible?
I have a process document with columns with start dates and due dates. The process steps do not change from year to year. What I would like to do is trigger some action or create a formula that once an item is checked complete, the start date and due dates advance by 365 days. It would be great if the complete box was then…
-
Formula skipping rows
Hi all, I'm trying to use the below function to automatically change the color of Harvey balls to red, yellow, or green based on the length of time till a task's due date. =IF([Due Date]@row >= TODAY(+61), "Green", IF([Due Date]@row >= TODAY(=31), "Yellow", "Red")) However, for some reason some rows show "#invalid…
-
Math Formula Error
Hi, I am trying to create a formula to divide the Impressions column by 1,000 then multiply it by the Total Price column. This is the formula I am using "=VALUE(Impressions@row / 1000) * VALUE([Total Price]@row)". Not sure what's going wrong. Total price is a column formula that auto fills each cell with the $5.95 price…
-
How to count number of completed projects in a date range?
I am absolutely new to formulas and I'm struggling to find a way to count the number of completed projects during a 14-day range so I can share that information in graph form on a bi-weekly report. I have a tracker with fields for "status" and "actual completion date". I need to count the number of projects that have…
-
Percentage Formula
Hello, Sorry for the basic question, but I can't seem to get it to work, I'm looking to create a formula that will calculate 15% from the column [Product Price]. Thanks in advance
-
Index/Match where index is across 2 columns
Hi, I currently have a simple Index/Match to pull in the hourly rate from a source sheet when the freelancer's name matches. Some freelancers may work on a fixed rate instead. I would like to build a formula that indexes the fixed rate column in the source sheet, if the hourly rate column in the source sheet is blank. I'm…
-
SUMIFS question
On one of my sheets, I am summing a range based off of another columns date range and another columns value (true/false). Currently, the formula I am using is: =SUMIFS({JOBS AWARDED Lighting}, {JOBS AWARDED Bid Date}, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31)), {JOBS AWARDED Range 4}, @cell = 1) However,…
-
Automatically adding rows to sheet and automatically nesting them under parent row?
Is it possible to have a rows automatically added to a sheet (for instance from a form on another sheet), and have the rows automatically be nested under the correct parent row on the target sheet? For example, if the Primary column is "project name", and "parent project name" is a field on the form, is there any way for…