Adding Time to a date column to trigger an auto email notification.

Options

Hello.

I am creating a sheet that will help track when components need to be removed from our baking ovens based on its date in. Its a bit more complex then what I have done in the past so I need some help figuring out how I can do this.

Screenshot 2023-09-21 110429.png

So the Date In column is a Date type. I have it automated to fill out when a new line is added. I have a database sheet that has how long each part number needs to be baked before use. The bake times on the database sheet need to be added to the date in time to give me the Date Out time. I have attempted a few times but I am struggling writing out a function once dates get involved. Any help or advice on how I can get this to work would be much appreciated.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Robert S

    If you're looking into another sheet, we'll need to use a Cross Sheet formula to return the number in your Bake Time column.

    Assuming that the PartNumber is unique, we can use an INDEX(MATCH combination formula to look into your second sheet and bring back the time associated with the matching PartNumber. See: Formula combinations for cross sheet references

    So something like this:

    =INDEX({Bake Time Column}, MATCH([Part Number]@row, {Part Number Column}, 0))

    Then we can use this in your IF statement to say that if there's a date, add the days that the cross-sheet formula returns:

    =IF(ISDATE([Date In]@row), [Date In]@row + IFERROR(INDEX({Bake Time Column}, MATCH([Part Number]@row, {Part Number Column}, 0)), " - No Match"))

    Let me know if that makes sense and works for you!

    Cheers,

    Genevieve

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!