Unanswered

Find days to future date - I don't understand the math

(0) Share

Report

Posted on by Community Power Pla...

I have a SharePoint column called "ExpirationDate." My workflow counts the days to the date. Everything seems to work except the positive results appear to be off by a day. I thought I understood that because I just figured the end date wasn't counted, but when I get the same result (0) on two different dates . . . I just don't understand the math.

Here is my expression:

div(sub(ticks(items('Apply_to_each_item')['ExpirationDate']),ticks(utcNow())),864000000000)

Today is 7/6/2022 and I get the following results for each expiration date:

ExpirationDate 7/5/22 results in -1 days (perfect)

ExpirationDate 7/6/22 results in 0 days (perfect)

ExpirationDate 7/7/22 also results in 0 days (Huh? 0 twice? Should be 1.)

ExpirationDate 7/8/22 results in 1 day (should be 2 and so on)

How can two different dates result in the same value using the exact same math function? I can't even add a condition that if the result is greater than or equal to 0 then subtract a day because I get 0 on two different days.

Can someone please help me with the math here before my brain explodes?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

ScottShearer 25,270 Most Valuable Professional on at

Like (1)

Report

Does your expiration date include the time? This might be a time zone issue.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report

I was able to figure this out myself. Not sure if this is the most efficient way or not, but I'll outline it . . .

To find the number of days between today and the ExpirationDate value, I was subtracting utcNow(). However, utcNow() will always have the current timestamp, and my date column had zeros (date only column). Original expression: div(sub(ticks(items('Apply_to_each_item')['ExpirationDate']),ticks(utcNow())),864000000000)

To fix this I formatted utcNow() to just the date: formatDateTime(utcNow(),'yyyy-MM-dd')
Then I formatted that result to round-trip date pattern (but I think it could have been any pattern with time): formatDateTime(formatDateTime(utcNow(),'yyyy-MM-dd'),'O')
Then I converted to ticks: ticks(formatDateTime(formatDateTime(utcNow(),'yyyy-MM-dd'),'O')) and plugged it in where I originally had ticks(utcNow()).

So the full revised expression is:
div(sub(ticks(outputs('Get_item')?['body/Date']),ticks(formatDateTime(formatDateTime(utcNow(),'yyyy-MM-dd'),'O'))),864000000000)

My new result is:

Today is 7/6/2022 and I get the following results for each expiration date:

ExpirationDate 7/5/22 results in -1 days
ExpirationDate 7/6/22 results in 0 days
ExpirationDate 7/7/22 results in 1 day
ExpirationDate 7/8/22 results in 2 days

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Yes, thank you. I was actually posting my solution as you were replying. Thank you!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

UPDATE:

For anyone having this issue, I am posting a correction to my solution. I had to also format the expiration date as well as the utcNow() date. I didn't think it would be necessary since it is a date only column, so the time default is midnight, but it seems I do. For an apples-to-apples comparison, and to get the correct value, format both dates in the same manner.

My revised expression is:

div(sub(ticks((formatDateTime(formatDateTime(outputs('Get_item')?['body/Date'],'yyyy-MM-dd'),'O')),ticks(formatDateTime(formatDateTime(utcNow(),'yyyy-MM-dd'),'O'))),864000000000)

Hopefully this one sticks.

Was this reply helpful? Yes No