cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

Formula to look at dates in the past

I am looking for a formula that is going to calculate days in the past. Tried to amend the formula currently in use for forward looking dates by adding -2 but it did not work..... 

 

addDays(utcNow(), -2, 'MM/dd/yyyy')

 

This is the formula that I tried to amend to account for a date in the past - is there a different formula that needs to be used for this? If so can someone tell me what it is? 

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past

Hi!


@Basic_User007 wrote:

I've amended the formula and it is working now but I am getting an error on the second action 'get items' where my filter query is. 

 

Error states - 

 "status"400,
  "message""The expression \"Expire eq '04/21/2020\" is not valid. Creating query failed...

 

 


Seems there is a missing single quote at the end of the day of Compose outputs.

Expire eq '04/21/2020'

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

12 REPLIES 12
Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past

Hi!

You can use getPAstTime() or subtractFromTime() instead

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#g...

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#s...

 

Official doc for date time functions

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#d...

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past

Hi again

Pleae also note addDays() allown nr of days positive or negative, and your syntax seems good

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#a...

 

So I'm just wondering why it's not working. Maybe Timezone issues? utcNow() provides info referred to UTC, so depending on the TimeZone you can get wrong results when adding /subtracting days, I mean obtain the day after, or the day before.

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Formula to look at dates in the past

@efialttestried entering the past days formula but it is giving me an error - will not accept it at all. 

 

getPastTime(1, day, MM/dd/yyyy)
 
I have tried removing the spaces and adding around 'MM/dd/yyy' and still will not accept it.
 
Have you used this successfully?
Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Formula to look at dates in the past

Ok so I got the formula working but I am trying to apply it in the same way as I am using the addDays formula against filter query but it is not working - not sure if that is correct way to get it to calculate.

 

Any offers? 🙂 

 

Inkedgetpasttime_LI.jpg

 

Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past



@efialttestried entering the past days formula but it is giving me an error - will not accept it at all. 

 

getPastTime(1, day, MM/dd/yyyy)
 

According from the examples en the link shared, your sytax is wrong. Please try this instead

getPastTime(1,'Day','MM/dd/yyyy')

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Formula to look at dates in the past

I've amended the formula and it is working now but I am getting an error on the second action 'get items' where my filter query is. 

 

Error states - 

 "status"400,
  "message""The expression \"Expire eq '04/21/2020\" is not valid. Creating query failed...

 

 

Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past

Hi!

 


@Basic_User007 wrote:

Ok so I got the formula working

 


So it works now? Congrats! Sounds we are closer to a happy ending!

 


but I am trying to apply it in the same way as I am using the addDays formula against filter query but it is not working - not sure if that is correct way to get it to calculate.

 

Any offers? 🙂 

 

Inkedgetpasttime_LI.jpg

 


So I guess the new problem is 'Get items' does not provide any output, right? If so, the reason can be:

 

a) the name of the column is not the internal one. IN this thread we discuss how to identify the internal column name

https://powerusers.microsoft.com/t5/Building-Flows/Getting-unknown-error-quot-e-is-undefined-quot-af...

 

b) the format MM/dd/yyyy is not the right one to compare, or

 

c) the operator is not the proper one... please note b) and c) can be related in between.

 

One question about how Expire field is defined on your Sharepoint List. Is it defined as a 'DateTime' column? A 'single line of text column'?  A 'lookup' column? A 'choice' column?

 

Hope this makes sense



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past

Hi!


@Basic_User007 wrote:

I've amended the formula and it is working now but I am getting an error on the second action 'get items' where my filter query is. 

 

Error states - 

 "status"400,
  "message""The expression \"Expire eq '04/21/2020\" is not valid. Creating query failed...

 

 


Seems there is a missing single quote at the end of the day of Compose outputs.

Expire eq '04/21/2020'

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Formula to look at dates in the past

@efialttes expire column is a date field - I want it to filter to check any dates that are minus however many days and then send an email. 

 

 

Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past

Hi!


@Basic_User007 wrote:

@efialttes expire column is a date field - I want it to filter to check any dates that are minus however many days and then send an email. 

 


Date field? Minus? Wait wait wait...

My suggestion is to ad this Filter Query expression just for troubleshooting purposes, with the best date for testing purposes

 

Expire lt '2020-04-21'

 

 ...then inspect 'Get items' results and make sure they are the ones expected. If so, then you need to work on your date format, since current one is not valid for ODATA

 

You will find great stuff on ODATA filters for Sharepoint in this great article

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Formula to look at dates in the past

well spotted @efialttes despite having looked and "fixed" that a million times it worked using getPastTime function and filter query on date field within SharePoint.

 

Thank you! 

Highlighted
Dual Super User
Dual Super User

Re: Formula to look at dates in the past

So, it works now?

Congrats!

Thanks for marking this topic as "Solved" by clicking "Accept as a solution". THis way others with the same problem can find a solution faster. My final suggestion (OPTIONAL) is to remember my motto: the easiest fastest way to say thanks to somebody spent its time to help (even on weekends!) is by clicking on 'Thumbs up' in every answer from this current thread you found valuable... or even in all of them 😉

 
 

Flow_thumbsUp.png

 

Thanx for helping to make this community great!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (8,726)