How can i compare dates from different sheets and return 1 and 0 as output.

darshan

Hello everyone,

I have two sheets: one is the main sheet containing almost 130 columns, all related to project updates, and the other sheet has 7 columns. Both sheets include an ID column and project name. On the second sheet, there's a date column filled with dates.

Firstly, I want to check if the ID on the second sheet matches the main sheet. If it does, I'd like to compare the milestone date from the second sheet with the handover column in the main sheet. If there's a match, return 1; otherwise, return 0 in the sheet 2 on respective columns.

I have around 600 projects in total. I've attempted a few approaches, but none seem to be working at the moment. Can someone please assist me with this? Thank you. Below, I'm sharing both tables.

(MAIN SHEET)

(secondary sheet)

darshan

Hey @Scott Peters , thanks for your response. I've made some improvements to the solution you provided by combining both parts together, and it worked perfectly.

=IF(INDEX({MAIN handover}, MATCH([MMNP ID]@row, {MAIN MMNP ID}, 0)) = [milestone ]@row, "1", "0")

Scott Peters

Hello @darshan - There are a variety of ways to go about this, so this is just how I would attempt it:

First, in the second sheet, set a column formula in the HANDOVER DATE column, like this: =index({Main Sheet | Handover}, match([MMNP ID]@row, {Main Sheet | ID}, 0))

Second, add a column to the sheet to compare the dates: =if(milestone@row=[handover date]@row, 1, 0)

Does this track with what you're trying to accomplish?

darshan

Hey @Scott Peters , thanks for your response. I've made some improvements to the solution you provided by combining both parts together, and it worked perfectly.

=IF(INDEX({MAIN handover}, MATCH([MMNP ID]@row, {MAIN MMNP ID}, 0)) = [milestone ]@row, "1", "0")

Scott Peters

@darshan - Glad I could help. I also like to combine like that once all my 'components' are working, if only to reduce the # of columns. Happy Smartsheeting!