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

Robert S

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.

Genevieve P.

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

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:

https://community.smartsheet.com/discussion/109494/add-months-to-date-formula

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.

Robert S

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

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

Robert S

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.

Robert S

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.

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

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.

Genevieve P.

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

Robert S

Thank you Genevieve for the help. Your line worked.

And thank you for the detailed description of how it works. I will probably read it over a few times since I am wanting to continue learning how to use SS for some other projects I have in mind.

Thanks,

Robert.