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

Options
darshan
edited 04/05/24 in Formulas and Functions


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)


Best Answer

  • darshan
    Answer ✓
    Options

    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")

Answers

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Options

    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
    Answer ✓
    Options

    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
    Scott Peters ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!