How many “ticks” until your opportunity closes?

January 13, 2022

I recently had a requirement to show the number of days before an opportunity was due to close, this turned out to be slightly more complex than it first sounded. So, I thought I’d create a quick blog post to explain how I achieved this requirement.

The challenge was to work out the number of days from “now” until the estimated close date. (Obviously a moving target!)

I initially thought I could simply create a calculated field. (As I knew I could calculate the duration between two dates.) The problem I found was that we can’t use “now()” in the calculation on a calculated field.

Instead, I decided to use Power Automate!

I created a scheduled cloud flow that would recalculate the days until close for all my open opportunities each night. Meaning the days to close should always be accurate.

Before starting I created a custom field on my opportunity entity called “Days to Close”. This was simply a whole number field.

My flow is shown below (shown at ahigh level);

In the narrative below I will explain the details for each action in my flow.

First off, I have my trigger, this was a scheduled flow so I simple entered the recurrence I required. I decided to run my flow at 2am every day.

Next, I initialized a variable. I called my variable “DaysToClose”. It was an integer.

My next step was to use the list rows actions. Here I listed all opportunities. I did add a filter on my rows, thus ensuring I only included open opportunities in my list. “statecode eq 0”

Below my “list rows” action I added an “apply to each” control. As the following steps will be applied to each of the items in my list of opportunities.

My apply for each control included the following actions. Firstly, I have a condition. That is simple as I’m just checking that the estimated close date on my opportunity isn’t blank. (I guess … as an alternative I could have also added this to the filter on my “list rows” action.)

The true (yes) side of my condition will set my “DaysToClose” variable and update my opportunity with the resulting value. In my example I didn’t add any actions to my “no side”.

In my “Set variable” action I added an expression that would calculate the number of days between now and my opportunities estimated close date. (The “slightly clever” bit of this Flow.)

The expression I entered looked like this ….

div(sub(ticks(item()?[‘estimatedclosedate’]), ticks(formatDateTime(utcNow(),’yyy-MM-dd’))),864000000000)

My formula is essentially working out the difference between the number of “ticks” for my estimated close date and the number of ticks for the current date. It then converts that back into days.

utcNow is giving me the current date and time. (I just needed to format it!)

item()?[‘estimatedclosedate’] is my estimated close date on the opportunity. From my list of opportunities.

If you don’t know (as I didn’t!) … a tick is a 100-nanosecond interval. Converting a date to “ticks” gives us the number of intervals between that date and “the start of time”. (Or at least since Jan 1^st 0001!)

Meaning if I can work out the ticks for each date. Subtracting one value from the other will give me the number of ticks between them. We then divide the result by 864000000000. Obviously! This isn’t as complex as it sounds. As this is simply the number of ticks in one day.

My final step is to update my opporuntity with the value I have calculated. Meaning I used the opportunity ID from my list. And then simply set my custom “Days to Close” field to the variable I’d just set.

I guess there are probably other ways you could satisfy this same requirement. But this seems to be working quite well for me. Hence, I thought showing how I’d achieved this with no code might be useful to others. Enjoy!