Formula the compares date columns and inputs a value representing the comparrison

Dawn McCallister

Good Morning - I am working on a performance dashboard for my intern but I have run into a snag with the performance comparison of due date to actually delivery. The goal is to be able to graph the performance. (screen shot below)

I am using two columns, Due Date and Date Completed. Date Completed is auto populated when the Done box is checked. In the Late, OnTime, Early column, I would like to populate the appropriate response based on a comparison of the two date columns.

I attempted to use and IF(AND) formula but the results continue to come back as "Incorrect Statement". I then focused on an If formula on the second record and it also continues to reflect as "Incorrect Statement".

=IF([Due Date]@row, <[Date Completed]@row, "Late", "")

Tips on handling this scenario? Am I tackling it the right way?

Dawn

Paul Newcome

Try removing the comma after [Due Date]@row.

Jeff Reisman

@Dawn McCallister

You've got an extraneous comma here:

For this solution, nested IFs should work as opposed to using AND.

=IF(NOT(ISDATE([Date Completed]@row)), "", IF([Due Date]@row < [Date Completed]@row, "Late", IF([Due Date]@row > [Date Completed]@row, "Early", IF([Due Date]@row = [Date Completed]@row, "OnTime", ""))))

English: If the Completed Date is not a date value, leave the cell blank. Otherwise, run these IFs.

Paul Newcome

Try removing the comma after [Due Date]@row.

Jeff Reisman

@Dawn McCallister

You've got an extraneous comma here:

For this solution, nested IFs should work as opposed to using AND.

=IF(NOT(ISDATE([Date Completed]@row)), "", IF([Due Date]@row < [Date Completed]@row, "Late", IF([Due Date]@row > [Date Completed]@row, "Early", IF([Due Date]@row = [Date Completed]@row, "OnTime", ""))))

English: If the Completed Date is not a date value, leave the cell blank. Otherwise, run these IFs.

Dawn McCallister

@Paul Newcome @Jeff Reisman

Those little commas can be killers on Monday!

Paul - You were spot on! Thank you .....

Jeff - If not nested IFs formulas before. Thank you for offering up a new solution. Putting that one in my Tips/Tricks file.

Dawn

Jeff Reisman

@Dawn McCallister You're welcome.

Using the nested IFs will let that column make the date comparison and populate correctly when there's a date in the Date Completed field, and leave it blank when there's not.