-
Formula for At Risk Flag
Hi Experts, I am using the following formula to turn the At Risk flag red when the condition is true. But I am getting a #UNPARSEABLE error. The formula is trying to say: If either of these conditions is true, I want to turn the At Risk flag red: When an End Date is within 3 days of today AND % complete is below 80% When…
-
Formula issue
Hi Can someone help me to write these five formula in a single formula to get the schedule health. IF(Status@row = "Not Started", "Gray") IF(Status@row = "On Hold", "Yellow") IF(Status@row = "Not Applicable", "") IF(Act1@row >Act2@row , "Red") IF(Act1@row <Act2@row , "Green") THanks
-
Formula not working
Hi , Can anyone help me with this formula issue:- I have 4 symbol color- Not Started- Gray Not Applicable- No color On Hold -Yellow Complete- Green Act1 greater than Act2 and Status is equal to In Progress - Red else Green =IF( Status@row <> "Not Applicable", IF( Status@row = "Not Started", "Gray", IF(Status@row = "On…
-
How remove a character ">" in order to SUM values in one column if they meet a criteria.
I need to be able to get the results using a formula. (I can't use a helper column, or the find and replace to remove ">". Logic needed: This formula should substitute the greater-than sign with nothing and convert those to numbers. Then it will need to sum the numbers where the criteria is met (in this case the condition…
-
if formula
Hi I was wondering if someone could assist. I'm trying to have a box check if the criteria on two sheets are the same. Example would: I have a column called Submission Number on one sheet and I have another sheet with a column with submission numbers. I would like if a number appears on both sheets I would like to check a…
-
Return task name associated with a future date
I have a project with linked tasks. I can get the start date of the next task using the following formula: =MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY())) How can I return the task name associated with this upcoming task? In this example, this formula gives the next start date as 6/19/19. What formula will return…
-
Formula for Actual duration excluding duplication
Hi Community, I am trying to calculate the actual duration starting from a list of Start and End dates. What I am trying to achieve is to exclude duration overlaps in order to get the actual effort days only, but including not contiguous time ranges. Please see example: Start: 01/10/2019 End: 04/10/2019 = 4 working days…
-
Date Comparison in Functions is Not Working
The following formula returns a value of zero (0): =COUNTIFS({DATE ONLY}, =DATE@row, {Shift}, ="1st", {Degreaser Check Passed?}, <>"BLANK") Here is the sheet that is being referenced: DATE : Contains only dates or blank cells. (formula driven) {DATE ONLY} : References a column that contains only dates or blank cells.…
-
Return status of cell (blank vs. full) at today's date.
Hello! I am trying to use a function that will tell me whether a cell is vacant or full at today's date. We are trying to create a desk booking system for our office, so the end-goal would be to have a vacant/occupied indicator on a SS dashboard. My test sheet is below: So, to determine whether Desk 1 is occupied or…
-
How to calculate tenure (years of service) between 2 dates?
Hello, I am trying to get a date on the Years of service column. In excel you just minus today's date-start date and it will give you a result with smart sheets I can't figure out how to do it. Can somebody help me?