Why is this alert automation not working consistently?

JIV

I have a daily statistic grid set up where staff are entering new records on a daily basis. I have alert notifications set up based on when the stats are entered. The expectation is that the staff will enter their numbers via a form by 10:00 AM every day. The first automation to go out is therefore at 10:00 AM, and is simply based off of the condition 'Created Today = "Yes" '. No issues there.

However in scenarios where staff enter their records late that day (ex. 10:30 AM or 11:40 AM), I want a separate notification to go out at each hour (ex. 11:00 AM would send the 10:30 AM entry; 12:00 PM would send the 11:40 AM entry). Here are some examples of my "conditional" columns (used in automations) where I have entered formulas to pull out specific information based on the Created Date/Time (Auto-Number):

Here is my 10:00 AM automation (working fine):

Here is my 12:00 PM automation (works sometimes, but usually not):

You might be wondering why I broke the 12:00 PM condition up into 2 separate blocks. I don't think I should have had to being that they are all 'and' criteria. However this approach did not work. Breaking it up improved things for some reason. However, some times it works, some times it doesn't, and it is quite frustrating as there is no consistency.

So in this example: the first group of records would not send as they were not entered today (correct); the second group of records sends at 10:00 AM as they were entered today @ 8:00 AM (correct); the third set of records however, is not being sent at 12 PM even though it meets all of the criteria in the automation --> Created today = Yes; Hour = 11; AM/PM = AM.

The whole point of this sheet is to automate the manner in which those that need the stats for the day get them in an efficient and automatic manner. This is not happening however due to this automation not triggering correctly and thus admin staff are phoning me at 12 wondering why they have not seen any stats since the 10:00 AM automation.

One thing I have noticed is that the automations coming through correctly, have a different "created time" in the email than the sheet. For example, some stats entered in the sheet this morning:

Vs the email automation......

For some reason it looks like the automations are taking 3 hours off the time in the cell on that sheet.... which I suspect might be the reason they are not triggering correctly within the hour blocks like in the example above...? I'm not sure why this would be or how I would go about adjusting this.

If someone please explain to me why this is happening it would be very much appreciated!!

Paul Newcome

What if you changed the hour to a numerical value and used the condition of being equal to?

JIV

Thanks Paul. I tried both 'equal to' and 'one of' and didn't seem to have better luck with either of them. I'll try again though.

Paul Newcome

To use the Equal To, you would need to convert the hour to a numerical value. Right now they are being pulled as a text string. Whatever formula you are using to pull the hour, wrap it in a VALUE function.

=VALUE(original formula without beginning equal sign)

Then try the equal to.

JIV

Hi Paul - The test I just ran through seemed to work once the numbers were converted to a value! I will test again later today, but hopefully that solves the issue. Thanks so much, I really appreciate this!

Paul Newcome

Great! Happy to help! 👍️

JIV

Hi Paul - At first I was excited to see that my test with this new 'value' worked. However, in real time this morning it failed to work again... as before, there is no consistency with this. Sometimes it works, sometimes it doesn't.

If you have any other ideas I would be happy to hear them. Otherwise I'm feeling a little defeated with this and will just try to ensure staff enter before 10:00 AM every morning.

Paul Newcome

Hmm... At this point the best thing I can suggest is reaching out to SUPPORT. You could also include a link to this thread so they have access to the screenshots and whatnot that you have already pulled together.