# Date Compare Formula

✭✭
edited 10/01/21

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

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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)

• ✭✭

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.

• ✭✭

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

• ✭✭

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

M

• ✭✭✭✭✭✭

Does this work?

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

• ✭✭

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?

• ✭✭✭✭✭✭

Would replacing

AND([Finish (Baseline)]@row

with

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

work?

Hope this helps and that you have a great day,

Jason Albrecht MBA, MBus(AppFin), DipFinMgt

LinkedIn profile - Open to work

• ✭✭✭✭✭✭

Try this...

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

• ✭✭

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).

• ✭✭✭✭✭✭

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

• ✭✭

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
• ✭✭✭✭✭✭

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.

• ✭✭

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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!