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

✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭

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

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!