I’m attempting to create a workaround for not having a datetime reminder. The way I’m doing this is with a time intelligence formula which will be tied to a notification automation.
I’m working up to the logic by first understanding if I can extract the hour from the times I need for the logic and then I’ll create the reminder column.
But I’m having issues with the HOUR() operator on two different instances:
With a datetime column, the HOUR() extraction from the datetime is not precise.
Please note the image below where the hour from the column is 5:00PM, yet the extraction gives out the number 11.
With an hour column. The HOUR() extraction from the hour says I’m missing parameters.
There is no documentation on how the HOUR() formula works, I’m tempted to say there is an issue with the casting on the operator itself, but hopefully I’m wrong and someone can point me in the right direction!
Hey @DBCC, thanks so much for posting about this! Happy to help as best I can
This article has our documentation around all our formulas, including the hours column.
Pulling the hour from the date column is not yet supported, unfortunately. For the second issue, would you be able to use the Hour column itself, instead of a formula?
If you’re looking for notification automations, is there another one you could use, triggered only by the date column for example? I’d love to learn more about your specific use case and why you were looking to have the formula column specifically tied to the automation instead of another column.
Extracting the hour from a datetime column was an alternative to the HOUR() from the time column not working, but from your previous comment I understand we’ll have to make the time column work.
The automation itself is part of a reminder sequence for our sales reps to contact leads at whatever time they accorded.
Say for example we agree to speak again at 5PM tomorrow, I’d enter this in the time column and my reminder column would have a conditional statement that says “reminder” if the time is ONE (1) hour before the actual time. In parallel, the board would have an automation to email a notification based on the reminder column.
Diagram:
I’m not sure if the automatic reminder is possible yet with a formula column, but I haven’t gotten that far because I’m unable to extract the hour from the time column.
If you think this is something that should be done in developer mode, please let me know and I’ll have a look at that angle.
