cancel
Showing results for
Did you mean:
Helper II

When pushing a math result into an integer variable does rounding occur?

I have an automated Flow that calculates whole days elapsed between two timestamps on a SharePoint list item by converting both timestamps to ticks, calculating the difference, diving that by 864000000000 and pushing the result into an integer variable.

All good and it works fine, except that I have +/- 1 day variability on my result, and I suspect it's because depending on exactly what time of day each timestamp is I may have a result of 4.6 days or 4.2 days and it rounds up or down when pushed into the integer.

Does this occur under those circumstances? If so, how would I drop off the decimal so that anything from 4.0 to 4.9 just goes to 4?

Another option I have been considering: maybe the decimal part isn't so bad, but for days who needs more than 1 decimal place. If I change the variable to a Float how do I limit to 1 decimal place?

Thank you. Still a Flow (Power Automate) newbie, so I have trouble finding the details on a lot of this stuff.

1 ACCEPTED SOLUTION

Accepted Solutions
Helper II

I think I may have a solution, though it'll take a few days of testing to confirm.

Previously I was doing the ticks conversion on the actual date stamps from SharePoint. Instead I took both those date stamps and ran them through a Convert time zone function (in Flow) with the output in Short date pattern - 6/15/2009 [d]. Those outputs were then subtracted for the difference.

I am assuming that with the short pattern it will treat every timestamp as midnight (00:00:00) on the given date, so if an entry is created on Monday afternoon it will treat any close time up to that midnight on Monday as "same day," any time on Tuesday (up to midnight) as "1 day," and so on.

Again I am not so familiar with these functions, so I could be wrong, but if my assumptions are correct this could be exactly what I am looking for.

4 REPLIES 4
Community Support

Hi @Daryl_K ,

A round function would be nice here because it would give the option to round the number to a specific value and also select how many decimals you would like, but it is not yet available in Flow. However, you could try the following expression. This will take away the decimal place for your value. It takes whatever your output is which is where you see I have 'outputs('Compose')' (you would need to put your output here), and it converts it to a string, splits the string on the decimal, takes the first part of the split, which in your case would just be the 4, and then converts it back to an integer.

int(first(split(string(outputs('Compose')),'.')))
Helper II

Well, I figured out what's "wrong," although technically absolutely nothing is wrong, it's just not exactly what I think would work best.

It turns out that the Created and Closed date stamps indeed store 24-hour time also. I thought I configured them for day/month/year only, but it turns out even if you do that the time is there, it's just not visible.

It also turns out my ticks conversion and math is working perfectly, so when I convert both to ticks I am getting a true difference, to the second.

So why the variation? Because everything as I set it is so accurate, if an entry is created - say at 3:00 PM - the calculation shows 0 days right up to 2:59:59 the next day, then at 3:00 it flips over to 1 day. So depending on the time of day the entry was created and time of day it was closed my "days to resolution" calculation  can vary by one day.

So, like I said, everything is working exactly as it should, but because I don't show time on the form (I don't think it's necessary) the result can be confusing to folks when they read it.

So any thoughts on a better way to do this - one that "rounds" to the day?

Helper II

I think I may have a solution, though it'll take a few days of testing to confirm.

Previously I was doing the ticks conversion on the actual date stamps from SharePoint. Instead I took both those date stamps and ran them through a Convert time zone function (in Flow) with the output in Short date pattern - 6/15/2009 [d]. Those outputs were then subtracted for the difference.

I am assuming that with the short pattern it will treat every timestamp as midnight (00:00:00) on the given date, so if an entry is created on Monday afternoon it will treat any close time up to that midnight on Monday as "same day," any time on Tuesday (up to midnight) as "1 day," and so on.

Again I am not so familiar with these functions, so I could be wrong, but if my assumptions are correct this could be exactly what I am looking for.

Helper II

Yay! This worked perfectly!

Again, this is for reminder emails to humans, not actual statistics, so rounding to the whole day is more than accurate enough. Using the method described in my previous email regardless of what time on any given day you create an issue, closing it prior to 11:59:59 that same day registers as 0 days elapsed (I put "same day" in the email), then starting at exactly midnight it rolls over to "1 day" elapsed.

When they see the "created" and "close" timestamps (which in in day/month/year format) it makes much more sense too.

Announcements

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.