Jan 06 2021 08:50 AM
Jan 06 2021 08:50 AM
I am trying to create a macros that will do the following :
Column F (Duration) = calculates the days between First engaged (D) and Last Check columns (E).
Column G (Agreement Time) This is the complex one =
If the Importance is HIGH AND Site is DA109 AND Service Owner is Painters AND Duration is =<3 THEN "WITHIN AGREEMENT"
If the Importance is Medium AND Site is DA109 AND Service Owner is Painters AND Duration is =<3 days THEN "NOT WITHIN AGREEMENT"
If the Importance is low AND Site is DA109 AND Service Owner is Painters AND Duration is =<3 days THEN "cancel"
If the Importance is HIGH AND Site is DMT1944 AND Service Owner is Painters AND Duration is =<3 THEN "NOT WITHIN AGREEMENT"
depending on the site, importance and service owner combination, it will tell me if im within agreement or not. I am attaching the main spreadsheet im working on, sheet one is the main sheet, sheet two is the logic for the formula, the thing is each cell will have the entire IF/AND/OR formula.
Jan 06 2021 12:33 PM - edited Jan 06 2021 12:51 PM
@RickJames_ I could write a long nested IF/IFS statement but there are a few issues. First is that your logic doesn't make sense or isn't complete. You have "LOGIC" sheet that only addresses Gardners and in the above post you address Painters and then above you have <=3 days for every case and the result changes based on the Importance (I doubt that was your intent) and then on the sheet you have the cases that are >= some days as sometime NOT within agreement but other times they ARE within agreement (which I suppose is possible) but the alternative in each case is = (EQUALS) some duration instead of < the other value which is even more an issue when the = value is >= the other value and hence BOTH conditions could be true and many cases where neither condition is true.
That all said, it feels like you have an explicit duration threshold for each combination and it would be easier to just add a column called Contract Duration and enter it there (assuming you clear up those discrepancies). If this is just a small sampling and you actually have hundreds or thousands of these and lots of each combination then I would recommend trying to create a lookup table (similar to what you have on the LOGIC page) so that based on site, owner, and importance the formula can find the Critical Duration value (assuming the things I noted above are just errors and can be cleared up).
In the attached I changed you LOGIC page to make a table like I described and added a corresponding formula to do a lookup and check the duration accordingly. Note I also created a formula to automatically take that text you have for date-time and convert them into dates and find the difference in days.