Project Roll-up Template Question

Reggie Andaya

We want to use the project roll-up template for a client implementation that we will be doing. I took the raw template and added data to it of course. My question is, in the At Risk column, should that be flagged if the due date has passed? I not seeing rows not being flagged while others are. As I added rows I may have broke the automation. Iooked in the automation options and the conditional formatting but can't find where I can fix this. Any assistance is appreciated.

We just want to give the viewer an indicator of a deadline being passed (or approaching).

Paul Newcome

@Reggie Andaya Yes. As Genevieve mentioned, the formula is only flagging rows where the end date is in the past and the status is not complete.

As Genevieve also mentioned, there are a number of ways to modify the formula based on what you need.

If you want to make it flag when the end date is TODAY or in the past, you would adjust the operator from "less than" to "less than or equal to".

=IF(AND([End Date]26 <= TODAY(), NOT(Status26 = "Complete")), 1, 0)

If you wanted to flag for rows that are in the past and also rows that are within the next 7 (or any other number) days in the future, you would put that number in the parenthesis of the TODAY function.

=IF(AND([End Date]26 <= TODAY(7), NOT(Status26 = "Complete")), 1, 0)

Edited to add:

I also suggest switching it to "@row" instead of specific row numbers so that it can be applied as a column formula and thus automatically added to new rows.

=IF(AND([End Date]@row <= TODAY(7), NOT(Status@row = "Complete")), 1, 0)

Paul Newcome

In your screenshot those rows that aren't flagged are either today or in the future. It may be that they are only being flagged when it is in the past. Is there a formula in the flag column?

Reggie Andaya

@Paul Newcome Ahhh yes, there is a formula.

=IF(AND([End Date]26 < TODAY(), NOT(Status26 = "Complete")), 1, 0)

So it looks like if END DATE is greater than TODAY, then CHECK the flag?

Genevieve P.

Hi @Reggie Andaya

The flag will be raised if the End Date is in the past (or "less than Today") and if the status is not Complete.

We can adjust this formula to raise the flag based on different conditions, if you'd like! Do you also want to see the "approaching" End Date? If so, how many days prior to the End Date would you like the flag to appear?

Cheers,

Genevieve

Paul Newcome

@Reggie Andaya Yes. As Genevieve mentioned, the formula is only flagging rows where the end date is in the past and the status is not complete.

As Genevieve also mentioned, there are a number of ways to modify the formula based on what you need.

If you want to make it flag when the end date is TODAY or in the past, you would adjust the operator from "less than" to "less than or equal to".

=IF(AND([End Date]26 <= TODAY(), NOT(Status26 = "Complete")), 1, 0)

If you wanted to flag for rows that are in the past and also rows that are within the next 7 (or any other number) days in the future, you would put that number in the parenthesis of the TODAY function.

=IF(AND([End Date]26 <= TODAY(7), NOT(Status26 = "Complete")), 1, 0)

Edited to add:

I also suggest switching it to "@row" instead of specific row numbers so that it can be applied as a column formula and thus automatically added to new rows.

=IF(AND([End Date]@row <= TODAY(7), NOT(Status@row = "Complete")), 1, 0)