Help with

Daisy A

Hello All,

I'm new to Smartsheet and

I am trying to create a formula to implement a policy of ours that students must reserve an item 72 hours/3 days in advance — requests less than 72 hours are either denied/must require the student to resubmit an intake form. What's the best solution to this since I cannot restrict an intake response based on time limits?

I've come up with the following "statement": Students cannot check out a specific item if the pick-up date is less than 72 hours/3 days of the created date. I'd like for anything that falls within the less than 72 hours of created date to automatically flag the row and make an "update request" to the user who sent the request in.

1. Is this the best way to create a 72-hour in advance system?
2. If so, how would I code this/what are the steps to make the above statement work in smartsheets?

Apologies for the mouthful, I'm hoping my request is clear enough. I'm providing a screenshot of my Smartsheet.

Thank you so much!

Kerry St. Thomas

TIME calculations are a little icky in Smartsheet. If you're able to flatten your position as "3 days" instead of "72 hours" it becomes MUCH easier. So the recommendation would be to start there if you can. IF THAT IS AN OPTION, you can use a formula in a helper column (like your Flagged column, or a checkbox column, or something like that):
=IF(NETDAYS([Date Created]@row,[Pick-up Date]@row)<4, 1,0)
…and then set up an automation. Trigger: when a row is added; Condition: Flagged is checked, Action: send an email that says "Pickups must be scheduled more than 3 days out; please resubmit your request. Here's a helpful link to do just that."

Timestamps ONLY exist in Smartsheet in the Created and Modified columns. So time calculations are a little icky. Using your formatting above, you can add in another layer to also check against the =RIGHT([Created date]@row,2) characters (AM/PM) and if the request is for "Morning" but the Created is PM, you can tighten down on that - but this begins to get really really convoluted… while it's POSSIBLE, I recommend against it.

One last thing to note: NETDAYS counts the first date and the last date in the range. So the difference between 7/16 and 7/17 is TWO DAYS. (Most people - myself included - count that as 1 day different.). So you may want to test that formula above and adjust the "<4" up and/or down to meet your individual use case.

Good luck!

kchenoweth

Put this formula in your "Flagged" column and make it a Column Formula:

=IF([Pick-Up Date]@row<([Created date]@row+3),1,"")

Then create an automation that is triggered when the "Flagged" column is flagged that then sends an Update Request to the contact in the "Email Address" column with your custom message.

If you are populating this via a form, you may be able to put in some logic for when they put in the Pick-Up Date to then show the rest of the fields if it is at least 3 days in the future.

Kerry St. Thomas

TIME calculations are a little icky in Smartsheet. If you're able to flatten your position as "3 days" instead of "72 hours" it becomes MUCH easier. So the recommendation would be to start there if you can. IF THAT IS AN OPTION, you can use a formula in a helper column (like your Flagged column, or a checkbox column, or something like that):
=IF(NETDAYS([Date Created]@row,[Pick-up Date]@row)<4, 1,0)
…and then set up an automation. Trigger: when a row is added; Condition: Flagged is checked, Action: send an email that says "Pickups must be scheduled more than 3 days out; please resubmit your request. Here's a helpful link to do just that."

Timestamps ONLY exist in Smartsheet in the Created and Modified columns. So time calculations are a little icky. Using your formatting above, you can add in another layer to also check against the =RIGHT([Created date]@row,2) characters (AM/PM) and if the request is for "Morning" but the Created is PM, you can tighten down on that - but this begins to get really really convoluted… while it's POSSIBLE, I recommend against it.

One last thing to note: NETDAYS counts the first date and the last date in the range. So the difference between 7/16 and 7/17 is TWO DAYS. (Most people - myself included - count that as 1 day different.). So you may want to test that formula above and adjust the "<4" up and/or down to meet your individual use case.

Good luck!

Daisy A

https://community.smartsheet.com/discussion/comment/445183#Comment_445183

Thanks Kerry, this is a FANTASTIC explanation. I knew something was tricky with TIME calculations, but I couldn't tell if I was having difficulty as a beginner Smartsheet user. Happy to know it's not my complete lack of knowledge!

Daisy A

https://community.smartsheet.com/discussion/comment/445182#Comment_445182

This is incredibly helpful, thank you!