Date Compare Formula

M Morgan

Hi All,

I had a closed discussion that ended up not being answered so I thought I would repost

I've been having a problem with a formula where I'm looking to compare two dates at a task level - the baseline finish date and the actual finish date. If the task is actually late (past the baseline finish), I want a checkbox auto-checked.

The formula I'm looking for will satisfy the following:

If the actual finish date is later than the baseline finish date, OR if the actual finish date is blank (because it's still in progress) but the calendar date (that matches the baseline completion date) has passed, the checkbox needs to be checked. Is there a formula anyone can help me with for that?

Let me know, thanks!

M

Paul Newcome

Try this tweak...

=IF(AND([Finish (Baseline]@row <> "", OR([Finish (Actual)]@row > [Finish (Baseline)]@row, AND([Finish (Baseline)]@row < TODAY(), [Finish (Actual)]@row = ""))), 1)

Of course you are going to want to update the column names appropriately.

Kimberly Loveless

I think that the formula below should work, or be close to what you need...

=IF(OR([Actual Finish Date]@row>[Baseline Finish Date]@row, AND(ISBLANK([Actual Finish Date]),[Baseline Finish Date]@row<=Today())),1)

M Morgan

Thank you. I just can't get the second part of that formula to work yet.

AND(ISBLANK([Actual Finish Date]),[Baseline Finish Date]@row<=Today())),1)

I've tried multiple iterations with no luck.

M Morgan

Still no luck with the second part of the formula, and thoughts?

M Morgan

I think I'm so close, but I'm still getting #INVALID OPERATION

=IF(OR([Finish (Actual-Jira)]@row > [Finish (Baseline)]@row, AND([Finish (Baseline)]@row < TODAY(), [Finish (Actual-Jira)]@row = 0)), 1)

Can someone review and let me know what is incorrect?

Each individual formula works separately but not together

Thank you in advance,

M

Paul Newcome

Does this work?

=IF(IF([Actual Finish]@row = "", TODAY(), [Actual Finish]@row) > [Baseline Finish]@row, 1)

M Morgan

Thank you Paul. Unfortunately, that didn't work for me.

What I've found is my currently formula works, but it also checks the late box if the "Finish Baseline" is blank.

=IF([Finish (Actual)]@row > [Finish (Baseline)]@row, 1, IF(AND([Finish (Baseline)]@row < TODAY(), [Finish (Actual)]@row = 0), 1))

Any thoughts?

Jason Albrecht

Would replacing

AND([Finish (Baseline)]@row

with

AND(NOT(ISBLANK[Finish (Baseline)]@row)

work?

Paul Newcome

Try this...

=IF(AND([Finish (Baseline]@row <> "", OR([Finish (Actual)]@row > [Finish (Baseline)]@row, AND([Finish (Baseline)]@row < TODAY(), [Finish (Actual)]@row = 0))), 1)

M Morgan

Thank you Jason and Paul.

Jason, I did try your change and that wasn't the solution, but again, thank you so much!

Paul, your formula is so close but it returns #INVALID OPERATION if the actual date is not blank (early, on-time, or late).

Any additional guidance?

Paul Newcome

That's odd. How are the dates entered? Have you double checked that it is in fact set as a date type column?

M Morgan

They are date column properties:

You can see each example above:

1. Actual finish date is after baseline = #INVALID
2. Actual finish is before baseline = #INVALID
3. Actual finish is blank but baseline is in future = Working
4. Actual finish is blank but baseline is in past = Working

Paul Newcome

Try this tweak...

=IF(AND([Finish (Baseline]@row <> "", OR([Finish (Actual)]@row > [Finish (Baseline)]@row, AND([Finish (Baseline)]@row < TODAY(), [Finish (Actual)]@row = ""))), 1)

Of course you are going to want to update the column names appropriately.

M Morgan

YES!!!!! Thank you! Thank you! Thank you so much!

Paul Newcome

Happy to help. 👍️