-
How do I account for leap years when adding 1 or 3 years to a date?
I have a database of clients on programs with expiry dates based on qualification date. Some programs expire in 3 years and some expire in 1 year. I was adding either 364 or 1094 to the date. Leap year seems to be throwing a wrench into this. Any ideas? Here is my code. I have a few other conditions in the calculation but…
-
Nesting OR into AND?
Hi All, I'm wondering if it is possible to nest an "OR" statement into an "AND" statement. In short, Task C requires Task A be "complete" and Task B be one of two statuses. I'd expect it to work something like this (note, I'm using status names to illustrate the point): =IF(AND(StatusA="Complete", OR(StatusB="In Progress",…
-
Auto-Populate Left Stub for Schedules
Thanks to Paul Newcome, I was able to get a WBS schedule working. Now I need to create several schedules, using the WBS as the left two columns. Here is the problem - I need for all schedules to update whenever a row is added or deleted from the original WBS sheet. Simply using Cell Linking does not achieve this. Here is…
-
Problem with average number formula, with certain criteria
Hi All! Im trying to get the average number of a certain range, but only if it meets a certain criteria. I have looked at other examples but somehow i don't get it to work. So I want the average of the blue columns. But only if the Red column is a certain name. And if its possible I want to do the formula on a seperate…
-
Hierarchical numbering patterns?
Anyone with experience setting up hierarchical numbering schemes? I want to add a column that is hierarchically auto-numbered such that child rows are added to the numbering scheme as increased decimal points. For example 1. Row 1 --1.1 sub item of row 1 ---1.1.1 sub item of row 1.1 ---1.1.2 sub item of row 1.1 ---1.1.3…
-
IF formula and dates: 'Red' status if the % complete is not 100% and the date is in the past
Hi there, Is it possible to incorporate dates in an IF formula? Essentially I want the RAG status to automatically go to 'Red' if the %complete is not 100% and the date is in the past - how would I go about doing this? Thank you
-
COUNTIFS function with two criteria
Hi all! I'm currently trying to count how many red entries (in the "Puri Safety" column) are in the month of June (using the "Created" column). I'm trying to use the following formula: =COUNTIFS(Created:Created, IFERROR(MONTH(@cell), 0) = 6, [Puri Safety]:[Puri Safety], "Red") However, it returns a value of zero... what…
-
How do I calculate an expiration date from a specific date column
I want to use the date in Column A plus "X" months so that Column B populates a new future date. What's the syntax I can copy/paste into Column B? Example - Column A (Contract Effective Date) - 5/29/2020 Column B (Contract Expires on Date) in 4 month. Should = 9/29/2020 ***"X" months can change as it may not be the same…
-
Formula needed - adding total of one column dependent on other columns criteria
I need the sum of one column conditional to the criteria of 3 other columns; below is the formula I am attempting to use with the goal of obtaining the sum of [% KRs Contribute to Corp Objs] that have the below additional columns criteria. Can anyone review and let me know if I need a different formula or what I am…
-
How to include Paranthesis in formula.
I am using an IF formula in a Total column Column A A) Option 1 B) Option 2 Total Column = IF ([Column A]@row = "A) Option 1" , "3", IF([Column A]@row = "B) Option 2" , "5")) I am getting error, as ) is prematurely closing out the computation. Any help is much appreciated. Thank you, Deepthi