-
I need a formula to clear contents in a field if the date in another cell is in the past.
For our process documentation review cycle, we have a column for Date of Next Review. There is a column next to it, Reviewed Process, that has a dropdown to indicate if the document has been uploaded to our Sharepoint site. If the date of next review is in the past, I need the contents in the Reviewed Process column to…
-
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,…