Seeking formula to catch mismatched dates for a single project across multiple Smartsheets

OneTortoise

Hi, Smartsheet community. I am hoping someone can provide guidance on a path to solve this problem. Since our team doesn't always get alerted when a project is rescheduled, we are looking to compare dates between our main Smartsheet and the information we extract from our company's master scheduling platform and subsequently import into Smartsheet.

We'll have two identically-formatted Smartsheets, one containing our main running list of projects and the other Smartsheet temporarily holding data newly imported from the company scheduling platform until we can screen it for information that has changed from original. We don't want to have Data Shuttle just overwrite and update the orginal dates in our main sheet as we'd like to track when surprise reschedules are occurring.

What I am hoping for is when a project number occurs in both the main and temporary sheets, can we compare the values in the "Proposed Date of Delivery" cells across sheets to determine if they match or if the project was rescheduled? I feel like it should be possible to write a formula that automatically compares dates for a single project across two sheets, but I haven't yet figured out how to make that happen. Thank you in advance for any help the community might be able to provide.

Paul Newcome

You would use something along the lines of...

=IFERROR(IF(INDEX({Date Column}, MATCH([Project Number]@row, {Proj # Col}, 0)) <> [Date Column]@row, 1), "")

Putting this in a flag type column should give you a flag if the dates don't match.

Paul Newcome

You would use something along the lines of...

=IFERROR(IF(INDEX({Date Column}, MATCH([Project Number]@row, {Proj # Col}, 0)) <> [Date Column]@row, 1), "")

Putting this in a flag type column should give you a flag if the dates don't match.

OneTortoise

Thank you, @Paul Newcome . The formula you provided is working for my sheets! That was very helpful, both in solving my problem and in allowing me to learn more about function syntax by deconstructing the pieces of the formula. I appreciated your taking the time to respond and provide such a helpful solution.

Paul Newcome

Happy to help. 👍️