Answered

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

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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)

Sort by

ScottShearer 25,290 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

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... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

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

Like (0)

Report
Copy link

Link copied!

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