A client recently asked if it was possible to round their user’s time entries to the nearest quarter hour. After a little testing, I found the answer is yes – it is possible to round time entries using a simple Flow.
At a high level, the Flow will go through 5 steps.
- Determine the remainder of the Time Entry Duration divided by 15. This will be used in the following steps.
- Determine if the Time Entry should be rounded to 0, and therefore deleted.
- Check if the Time Entry is already in a 15 minute increment, and therefore doesn’t need to be rounded at all.
- If necessary, determine if the Time Entry should be rounded up or down.
- Update the Time Entry Duration to the nearest 15 minutes..
To begin, navigate to the Power Automation section of Office 365, go to + Create and create an Automated flow. Name your Flow and select the Common Data Service Trigger When a record is created. You will also need a copy triggered on Update and note that the Dynamics trigger works as well.
The Time Entry entity uses the Duration field to store the length of the entry. The Duration field displays in a unique way, but behind the scenes it stores data simply as the number of minutes.
With that in mind, the Flow should begin by with an Initialize Variable step to determine the remainder of the Duration when divided by the interval we want to round to; in this case 15 minutes. To do this, initialize the value of the variable to be Mod(Duration,15) which returns the remainder of the Duration when divided by 15.
Now that we have the duration remainder, we can use it in Condition steps. The first condition should be if the Time Entry should be rounded down to 0, which requires deletion. In this case:
If yes, add a Delete a record step with the Time Entry field from the trigger body to clear out the Time Entry record. Then add a Terminate step to end the Flow with a status of Succeeded. The if no side can be left blank.
The next condition will check if the Duration needs to be rounded at all by checking if the Duration Remainder variable is equal to 0. If yes, cancel the Flow with a status of Cancelled as the Duration is already in a 15 minute increment.
With those cases covered, the final portion of the process will determine if the Duration needs to be rounded up or down. Since a remainder of half or more is rounded up, the formula is if Duration is greater than or equal to 7.5.
Both the if yes and if no branches will have an Update a Record step on the trigger Time Entry, but they will have different formulas to populate the Duration field.
To round down, we need to update the Duration field to be the original value minus the remainder. The formula will look like:
The formula to round up is a little more complicated. In this case we need to find the difference between 15 and our remainder and add that value to the original Duration field. The formula will look like this:
And the Flow is complete! Make sure to copy your Flow and change the second version to use the When a record is Updated trigger. Overall, with condition branches minimized your Flow should look like this:
You can easily update this Flow to round to a different number by replacing any 15s with the new increment and any 7.5s with half of the new increment, and if you change frequently, you could even replace this values in the Flow with variables.