Skip to main content

Notifications

Community site session details

Community site session details

Session Id : qBluNncw+ZZP3lsqkj7RQx
Power Automate - Building Flows
Answered

How to subtract days using the Filter Query in Get Items

Like (0) ShareShare
ReportReport
Posted on 9 May 2023 21:19:39 by 523

Hi...

 

I need to go back to 14 days from the start date and I have tried several expressions. All errored out...

 

Subtract Days Expression.PNG

Subtract Days Expression 1.PNG

  • Holly_CMS Profile Picture
    523 on 10 May 2023 at 22:28:32
    Re: How to subtract days using the Filter Query in Get Items

    It works!!!!!!!

     

    Thank you Thank you Thank you!!!!Subtract Days Expression 6.PNG

  • Verified answer
    wskinnermctc Profile Picture
    6,517 Super User 2025 Season 1 on 10 May 2023 at 22:18:20
    Re: How to subtract days using the Filter Query in Get Items

    Is that SharePoint column a DateTime type column or is it a Text type column?

     

    I'm assuming it is a DateTime type column, if it is a Text type column this example will not work.

     

    The Filter Query is basically the same except you add another value to filter by which will create a range.

     

    So this is saying "I want items that do not have an email AND the Effective Date is greater than 14 days before today AND the Effective Date is less than today."

     

    Get Items Filter Date RangeGet Items Filter Date Range

     

     

    In my example my SharePoint columns were named "SP_Text_Email" which is a Text type column and "SP_DateTime_EffectiveDate" which is a DateTime type column.

     

    The expression that is in the Filter Query is below:

     

    (SP_Text_Email eq null) and (SP_DateTime_EffectiveDate ge 'addDays(utcNow(),-14,'MM/dd/yyyy')') and (SP_DateTime_EffectiveDate le 'utcNow('MM/dd/yyyy')')

     

    Don't forget to remove the single quotes ' ' from around the null. 

     

    The use of formatDateTime() is not necessary since most time expressions like utcNow() or addDays() allows for a formatting option within the expression itself. 

    formatDateTime(utcNow(),'MM/dd/yyyy') is the same output as utcNow('MM/dd/yyyy')

  • Holly_CMS Profile Picture
    523 on 10 May 2023 at 21:34:21
    Re: How to subtract days using the Filter Query in Get Items

    Let's try this...I have 2 items that started on 5/8/2023 yet they do not have the email address as of today 5/10/2023. So I need to go back in at least 2 weeks to capture the items that already started but are missing the email addresses. Not the upcoming start dates in which would be 5/15/2023....

     

    I need to pull all onboards that is missing an email address after their start date but not going back so far like months or years before. 

    Subtract Days Expression 5.PNG

     

  • wskinnermctc Profile Picture
    6,517 Super User 2025 Season 1 on 10 May 2023 at 21:12:27
    Re: How to subtract days using the Filter Query in Get Items

    I don't know of a way to filter by the value itself. You can't say, "I want the Effective Dates that are within -14 days of the effective date." That is a self reference and doesn't make a logical filter.

     

    You have to establish the date that is being compared as a filter for the values.

     

    Like you can say "I want the Effective Dates that are greater than or equal to utcNow()" Which will get all of the Effective Dates that are today or anytime after today.

     

    If you schedule the flow to run on Monday, but you don't want any Effective Dates from this week, you can say "I want the Effective Dates that are greater than or equal to addDays(utcNow(),7)" which would give you all of the effective dates that are the next Monday or a later date.

     

    Are you saying that you only want Effective Dates that are within the next 14 days? As in you only want to get the people that are supposed to start work within the next two weeks/14 days?

    Or are you saying you want all of the Effective Dates that are within the past 14 days? As in any date that is between or equal to 4/26/2023-5/10/2023?

     

  • Holly_CMS Profile Picture
    523 on 10 May 2023 at 20:27:07
    Re: How to subtract days using the Filter Query in Get Items

    Got it! Question - does the expression only bring back the ONLY date of 4/26/2023? It works but it made me realized that there was nothing for that day.

    If so, now I see what I did wrong...How do I ask in expression for the "previous within the 14 days" from the start date, not the precise of the previous 14th day. Like 4/24/2023  to 5/5/2023.

     

    Subtract Days Expression 4.PNG

     

     

     

    We don't want the "current week" of start dates like 5/8/2023 of this week to show up. This flow will be set up a weekly flow before the start of the current week (*Mondays).

  • wskinnermctc Profile Picture
    6,517 Super User 2025 Season 1 on 10 May 2023 at 19:06:40
    Re: How to subtract days using the Filter Query in Get Items

    Ok thank you, I made some columns in a sharepoint list and added some items. Using the -14 days from today 5/10/2023 would be 4/26/2023. So I have 2 items in my sharepoint list with the date of 04/26/2023. The first item does not have an email address in an Email text column, and the second item does contain an email address.

    I was able to get the single item that has a date of 04/26/2023 and does not have an email with the filter query example below. 

     

    Note: The view you have with the Filter Query that has drop down options in considered "Experimental Features". If your settings has turned On the Experimental Features, the view will look like yours with the drop down options. 

    If you turn Off the Experimental Features then the dropdowns are not available and the Filter Query has to be written in all the way.

    The two arrows beside Filter Query will toggle the view between the regular view and the Experimental Feature view.

    Below is an example of the same step that has the view toggled.

    Filter Query ViewsFilter Query Views

     

    Whenever you use the Experimental Features type view it will add single quotes ' ' around what is put in the filter value even though you are not able to see the single quotes. (If you went into the Peek Code option of the action you can see the quotes were added.)

    Single quotes usually work and are required for most values used in a filter query, but the quotes will not work when using null. You will have to toggle the view to be the regular text format and then remove the single quotes ' ' that are around 'null'. So the expression shows null instead of a text 'null' . 

     

    Below is the same step that had the view toggled and the filter value 'null' changed to null without single quotes.

    Edit Filter QueryEdit Filter Query

     

    I was able to filter my items with this example.

     

    The name of my SharePoint columns were "SP_Text_Email" and "SP_Text_EffectiveDate". I also had a separate column "SP_DateTime_EffectiveDate" that was a date time type column, but the results were the same when the date column was text as well as date time.

    My Filter Query is below.

     

    Incorrect:

    (SP_Text_Email eq 'null') and (SP_Text_EffectiveDate eq 'formatDateTime(addDays(utcNow(),-14),'MM/dd/yyyy')')

     

    Correct:

    (SP_Text_Email eq null) and (SP_Text_EffectiveDate eq 'formatDateTime(addDays(utcNow(),-14),'MM/dd/yyyy')')

  • Holly_CMS Profile Picture
    523 on 10 May 2023 at 16:49:45
    Re: How to subtract days using the Filter Query in Get Items

    Gotcha - I am creating a table inside the body of the email. So with that said, the effective date is the actual name of the column in SP list whereas I renamed it to Start Date in the flow. The Start date is when an employee starts with the company.

     

    Subtract Days Expression 3.PNG

     

     

     

     

     

     

     

     

     

    The reason why I want to go back 14 days is because the process at work shows all new onboarding tickets must be worked on within a day or so from the ticket submission date. However, we don't have the submission date in the SP list. The email address missing in the email field is an indication that the tickets have not been completed. Therefore, put the 14 days before start date expression in to see which older onboarding items are not done yet.  Hope this clarifies for you?

  • wskinnermctc Profile Picture
    6,517 Super User 2025 Season 1 on 10 May 2023 at 14:07:25
    Re: How to subtract days using the Filter Query in Get Items

    I only guessed Null is the problem because it usually is a problem in filters. Also, your expression formatdatetime(adddays(utcnow(),-14),'MM/dd/yyyy') is correct.

     

    So that means that the filter issue is with the Null, OR it is with the format of the "Effective Date" that it is being compared to. Or there is something else completely wrong with what you are doing that I would be unable to see with the information provided.

     

    I don't understand how your filter is based on "Effective Date", but then you want to go back 14 days from the "Start Date"? Is there a difference between "Effective Date" and "Start Date"

     

    And you are trying to filter a "Get Items" step with dates from and Excel table??

     

    Can you show your whole flow? It is difficult to tell what you are trying to do. 

     

  • Holly_CMS Profile Picture
    523 on 09 May 2023 at 21:55:54
    Re: How to subtract days using the Filter Query in Get Items

    How did you determine that NULL is the issue? It works fine. However, I need the Start Date to go back 14 days from its start date...

     

    Subtract Days Expression 2.PNG

     

     

  • wskinnermctc Profile Picture
    6,517 Super User 2025 Season 1 on 09 May 2023 at 21:51:29
    Re: How to subtract days using the Filter Query in Get Items

    I'm thinking the 'null' is the problem. You can't compare against null since it doesn't exist.

     

    You will need to check the CMSEmail some other way like empty() or length()

    empty(CMSEmail) eq true

    length(CMSEmail) eq 0

     

    Also, if the Effective Date is not in the exact same format, then it would be a problem too. Like if the EffectiveDate is a datetime column, it probably has some hours on the end, so it would never match 'MM/dd/yyyy'. 

     

    I don't know exactly what is your issue but I'm sure null is one of them. Hope this helps,

     

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

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,745 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard
Loading started