Flag out of Range Date

Lisi

Hello,

 

I have a date range:

Date Range Start & Date Range End, and I want to flag it if the "Actual Completion Date" is before the Date Range Start OR if the "Actual Completion Date" is after the Date Range End.

Can anyone help?

I've tried:

 

=IF([Actual Completion Date]@row > [Date Range Start]@row, OR([Actual Completion Date]@row > [Date Range End]), 1,0))

 

=IF(AND([Actual Completion Date]@row > [Date Range Start]@row, OR([Actual Completion Date]@row > [Date Range End]), 1,0))

Paul Newcome

Try this...

 

=IF(OR([Actual Completion Date]@row < [Date Range Start]@row, [Actual Completion Date]@row > [Date Range End]@row), 1)

Lisi

Hi Paul,

 

Thank you! That works, but what can I add so it does not flag if there is no date in Actual Completion Date?

Paul Newcome

Try this:

 

=IF(AND(ISDATE([Actual Completion Date]@row), OR([Actual Completion Date]@row < [Date Range Start]@row, [Actual Completion Date]@row > [Date Range End]@row)), 1)

Lisi

Makes sense!

 

Thank you very much

Paul Newcome

Excellent! Happy to help!