-
Formula to keep Date when another date column is blank
Hi, I am trying to create a formula that will keep the existing date in the cell if another column is blank and if there is a date then I want to add 365 days to the other column and return it in my NEXT CONTACT DATE cell =IF(TODAY@row, "", [NEXT CON TACT DATE]@row, IF(TODAY@row, TODAY@row + 365)) My Column Headers are:…
-
Current year streak and rolling unbroken streak formula help
I have a dashboard metric that I could use help with. I have a Consistency Club to recognize associates that achieve their monthly sales activity metrics, measured by individual month. Meaning if they have achieved their metrics for Jan, Feb, Mar, Apr, May and June their current year consistency streak would be 6. If the…
-
Formula to check box if oldest milestone?
Hi there, I have a set of project plans that I wish to summarize in one report, and in that report I only want to know the oldest (aka soonest) milestone that is not completed for each project. To get to that, I figure I will have a checkbox column in every project plan, with a formula that checks the box if it is a…
-
COUNTIF using Less than Today
I am struggling with a formula that is trying to count the Record Type entries in the reference sheet that meet the following conditions: Record Type = "TEST" Deadline is less than TODAY Assigned ISBLANK I tried many variations of the following formula but it keeps erroring out with INVALID OPERATION. When I parse it out,…
-
Populating a column in one sheet from a column in another sheet
I have 2 sheets, Techs and Totals. I want to: Populate a column called Tech/RSM List in the Totals sheet from the column called Tech Name in the Tech sheet. I guess the question is, how do I just reference the row in an Index formula. If I do: =INDEX({Tech/RSM List Range 1}, 1) I will get a valid return. If I do this:…
-
Control Center and date formatting
Hi there, i'm facing a simple issue. When using CC with data profiles, in my intake sheet, i'va got a date field in Eureopean format ( DD/MM/YY). In the destination shhet created with CC, the date format is MM/DD/YY in description field in the summary part of the created sheet. Any idae to change the format directly and…
-
How to do calculation rather than just COUNTIFS or SUMIFS
Hi, I have a table like a inventory with all goods with unit price. Such as: A1 A 100 $5.00 B1 B 200 $10.00 A2 A 150 $7.00 B2 B 50 $12.00 C1 C 80 $20.00 If I want to count how many "A" I have now, I can use COUNTIF(A). My challenge is, I need to calculate the total price of all goods under category A, like:…
-
Project Progress
1- How to show up the total progress of the project in the DASHBOAD? 2- How to add Formula in the Project Plan sheet to calculate automatically the status of the task by percentage progress, For example, if the task is not started, it will show automatically in the cell as 0%, if in progress 50% if it completed 100%.
-
Assistance writing formullae
Good day, I am in need of help writing the following formulae: Status to display 'red' bubble in a column if a project is between 0-50% completed. 'Blue' bubble if between 51%-75% completed. 'Amber' bubble if between 76%-99% completed and 'green' bubble if 100% completed. How do I calculate the percentage in a third column…
-
Formula for adding months onto a date
Hi! Can someone please help me with a formula to auto calculate the 'Expected Finish' by adding the 'Duration' to 'Actual / Expected Start Date'? Thanks so much! This formula is getting an 'invalid data type' error: =IF((MONTH([Actual / Expected Start Date]@row) + VALUE(LEFT(Duration@row, FIND(" ", Duration@row) - 1))) >…