Alert After a Certain Time

Kyle Lewis

Hi,

I am trying to set up an alert to notify someone when a new row is added between 3PM and 5AM. This will cover proper alerts get sent out during non working hours.

The submissions will be inputted by customers via a form. I need the alerts to get to the correct people after hours.

I was trying use the created date and time column but can not think of how to use that time stamp or and formula to extract that info.

Kyle

Andrée Starå

Hi @Kyle Lewis

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kyle Lewis

Hi @Andrée Starå

Thanks for the response. I don't quite follow what your post explains. I get what you are saying about index/match, but how would i write my notification trigger to send the alert on my main sheet?

Paul Newcome

You could pull the hour and convert it to a 24 hour time then set up your alert with conditions in place for that column being greater than 15 (3pm) or less than 5 (5am).

To pull the hour:

=MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))

I'll have to get back to you on converting it to a usable 24hour format once I have had a chance to peruse my notes.

HERE is a thread I created with some various time based solutions. I know the conversion is tucked away in most of them. I'll have to dig through later today to isolate it, but at the very least maybe you can take a look and see if you can find something that helps.

Kyle Lewis

Thanks Paul!

I looked into all you posts and i think the "Flagging a Date and Time Overlap" formula will work awesome. I will continue to test today.

Thanks again!

Paul Newcome

You can use this to pull the hour and convert into 24hr format as a numerical value which can then be used in your condition(s):

=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12))

Andrée Starå

@Kyle Lewis

Happy to help!

I saw that Paul answered already!

I missed the added row part so Paul's solution is probably better for your use case.

Let me know if I can help with anything else!

Best,

Andrée

Paul Newcome

@Kyle Lewis It looks like we were typing at the same time. Haha. You should be able to use the above formula to pull the hour and convert it into a usable number for your conditions. I also posted it as an additional comment on the linked Time Thread.