Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Building Flows / Find days to future da...
Power Automate - Building Flows
Answered

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

(0) ShareShare
ReportReport
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?

  • Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Find days to future date - I don't understand the math

    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.

  • Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Find days to future date - I don't understand the math

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

  • Verified answer
    Community Power Platform Member Profile Picture
    Community Power Pla... on at
    Re: Find days to future date - I don't understand the math

    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

     

     

  • ScottShearer Profile Picture
    ScottShearer 25,228 Most Valuable Professional on at
    Re: Find days to future date - I don't understand the math

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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 1

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans