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

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.

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.

Answers

  • vdemattei
    vdemattei ✭✭✭

    I believe this may be similar to something I've recently received help on. You're adding a variable number of days to a date to create a new date?

    Here is the thread that helped me:

    There are a few iterations of the formula, so you'll need to read through to the end. This may be a bit different as you will need to match your bake time based on the part numbers and I believe you're adding days instead of months, but I THINK this may be helpful to you.

  • Thank you for the suggestion. I tried to use or modify that script but I just wasn't able to make it work. I just don't have an understanding of Smartsheet enough yet to convert the script right. I am still learning though.

    So any further help would still be much appreciated.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    When you say "time", are you referencing hours, days, weeks, something else? Which column houses the duration?

  • Hours is what I am aiming for but days would probably be accurate enough.

    The column that houses the duration is on another database sheet for all the part numbers.

  • Hello. Sorry for not replying for a while. Things are hectic during this season but I am back on this project with some weeks dedicated to finishing this project.

    I got the engineers to agree to some things so I will be programing this to use DAYS.

    So I am starting with the formula that Vedemattei posted as a starting framework and seeing if I can change it to suit my needs.

    =IF(AND(ISDATE([Date In]@row), ISNUMBER([Minimum Bake Time]@row)),

    The Minimum Bake Time column is in another sheet that is our database. Now I am trying to figure out how to get Smartsheet to see the current date and then add the number of days in the Minimum Bake Time column from the database sheet.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Robert S

    What does your second sheet look like, the one with the "Minimum Bake Time" column? Does it have a Part Number associated with each time?

  • Robert S
    Robert S
    edited 12/04/23

    Sorry it has taken so long for me to reply. I was on vacation.

    Yes it does have a PN associated with each of the bake times. And the 2 means two days or 48 hours min. bake time.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!