How to calculate whether time is between 2 time values

[Billy_C]

Hi there,

I have created a new Entity called Reminders and I would like to know if it is possible to determine whether the time of a field is between 2 other time values.

For example send a reminder every hour in between the office hours so I want to know if I can check whether the current time is equal to or larger then the start time but equal to or smaller then the end time. This is so that users won't receive reminders in the middle of the night.

Is it possible to achieve this with a calculated field, cause I don't want to execute flows too often, especially since there is a limit to the amount of API calls.

Thanks in Advance,

Billy Cottrell

[ChrisPiasecki]

Hi @Billy_C 

A calculated field can determine this, but they cannot trigger events or workflows as they don't actually store any values, they are just evaluated at runtime such as when retrieving the record. 

A scheduled flow is probably your best bet, and in your List Rows action you can add an OData filter query where the current time is ge (greater than or equal) to start time and le (less than or equal) to the end time. This will ensure you only get the records needed to send a reminder for. If you have no reminders then your API calls used will be minimal.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[Billy_C]

Hi @ChrisPiasecki,

I know that they can't trigger flows except when they are filled in for the first time (on create), but if it is possible to set a calculated boolean to null then it is possible to trigger flows as they go from "no value" to a "value" and this does trigger a flow I think.

Even if this doesn't trigger the flow it is still possible to do this indirectly by using a second field and a business rule that checks whether the value of the second field is equal to the calculated field and thus updating that value if necessary and also triggering the flow.

The only missing piece was if it was possible to determine whether a reminder could be sent based on the time (not the date). If this wouldn't be possible I would have to use a different method with flow so it would be possible to send reminders.

Thanks in Advance,

Billy Cottrell

[ChrisPiasecki]

Hi @Billy_C,

You can use a boolean to toggle the state and be your trigger yes, but this would require you to interact with the record directly, whether that is creating the record, editing, etc. If you want this to happen automatically for all your records without interaction then you'll need a flow.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[Billy_C]

Hi @ChrisPiasecki,

Well normally the user creates a reminder and when certain conditions are met a reminder will be sent. So my calculated field would change value when a reminder should be send and when the time occurs it should update the calculated field, execute the business rule and set a regular field to the same value as the calculated field. When that calculated field is updated it would trigger a flow send a reminder and then update that record again to keep track as to when the next reminder needs to be send.

Or won't it function like that?

Thanks in Advance,

Billy Cottrell

[ChrisPiasecki · Accepted solution]

Hi @Billy_C,

Unfortunately calculated fields don't work like that. The calculation only occurs when retrieving the record. Changes to calculated field values do not trigger workflows or plugins.

The only reliable way is to use a scheduled flow and retrieve the records that require follow up.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.