IF, OR and AND? Formula issue

Jack Parry

Hi, I'm trying to calculate if we're meeting the schedule start date.

The issue I have is if the Schedule Start Date is greater than today's date then it still thinks its completed on time. I think I'm missing AND statement with the formula?

=IF(OR([Actual Start Date]@row = "", [Schedule Start Date]@row < [Actual Start Date]@row), IF([Schedule Start Date]@row < Today@row, "Overdue", "Scheduled"), "On Time")

To summarize what I want the formula to calculate:

1. If the actual start date is blank, check to see if the schedule start date is less than today's date = "Overdue", if not its "scheduled" for completion.
2. If the actual start date has a date, compare it against the schedule start date, if the date is more than the schedule start date its "overdue", if its before the schedule start date or the same date its "On Time"

Thanks :)

Nick Korna

Hi @Jack Parry,

Something like this?

=IF(OR(AND([Actual Start Date]@row = "", [Schedule Start Date]@row < TODAY()), [Actual Start Date]@row > [Schedule Start Date]@row), "Overdue", IF(AND([Actual Start Date]@row = "", [Schedule Start Date]@row > TODAY()), "Scheduled", "On Time"))

Example output:

Hope this helps, but I've misunderstood something or you've any problems/questions then just post! 🙂

Nick Korna

Hi @Jack Parry,

Something like this?

=IF(OR(AND([Actual Start Date]@row = "", [Schedule Start Date]@row < TODAY()), [Actual Start Date]@row > [Schedule Start Date]@row), "Overdue", IF(AND([Actual Start Date]@row = "", [Schedule Start Date]@row > TODAY()), "Scheduled", "On Time"))

Example output:

Hope this helps, but I've misunderstood something or you've any problems/questions then just post! 🙂

Jack Parry

Works perfectly! Thanks @Nick Korna :)

Nick Korna

No problem, happy to help.