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 : 23wFUuUEn+5sr5W9v9TT9Y
Power Platform Community / Forums / Power Automate - General Discussion / Find difference betwee...
Power Automate - General Discussion
Unanswered

Find difference between current date and date from SP list

Like (0) ShareShare
ReportReport
Posted on 17 Oct 2022 08:44:15 by Aleksandra1 249

Hello guys, I have "Date" column in SP list. In Format 'dd/MM/yyyy'.

dateformat.png

And in Power Automate I need to write if current date - date from SP list > 30 days, than delete this row.

I've found in one video line of code and I tried to use it in my flow:

 div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(outputs('Get_items_from_SP_list:_audit_template__CS')?['body/Date'])),864000000000). So in flow it looks like this (I used greater than one just for testing)

DateCode.png

But I got this error daterror.png

I have checked name of columns is right. It is new error. Error before was Unable to process template language expressions in 'formatDateTime. So maybe problem is in data format that I use 'dd/MM/yyyy', can you please help me to rewrite this line of code

  • Expiscornovus Profile Picture
    Expiscornovus 31,402 Most Valuable Professional on 17 Oct 2022 at 10:32:09
    Re: Find difference between current date and date from SP list

    Hi @Aleksandra1,

     

    Apologies, I forgot SharePoint Date fields have a different format. I believe it should be yyyy-MM-dd.

     

    Can you try and use this instead for the comparison?

    addDays(UtcNow(), 1, 'yyyy-MM-dd')
  • Aleksandra1 Profile Picture
    Aleksandra1 249 on 17 Oct 2022 at 10:27:32
    Re: Find difference between current date and date from SP list

    Now I was trying to use you last expressiondateError2.png

    Now this expression is always false.  when I write 1 as a parametr  it should be true, when i write 30 as a parametr  it should be false, but it always false, unfortunately

  • Expiscornovus Profile Picture
    Expiscornovus 31,402 Most Valuable Professional on 17 Oct 2022 at 10:16:33
    Re: Find difference between current date and date from SP list

    Hi @Aleksandra1,

     

    I was still looking at the null error of your Set variable action.

     

    If you need an expression which supports a loop (without referring to any specific item) you should be able to use this instead for your set variable action:

     

     

    div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(item()?['Date'])),864000000000)

     

     

     

    For the condition you can add a format in the addDays function.

     

     

    addDays('<timestamp>', <days>, '<format>'?)

     

     

     

    In the condition I would use this expression

     

     

    addDays(UtcNow(), 30, 'MM/dd/yyyy')

     

     

     

  • Aleksandra1 Profile Picture
    Aleksandra1 249 on 17 Oct 2022 at 10:06:30
    Re: Find difference between current date and date from SP list

    Unfortunately, I cannot specify an index, because it shouldn't be applied only on one index, it should be applied on whole array.

    So the point is, I need to write if current date - date from SP list > 30 than delete row. Or

    I can say  if current date-30 > date from SP list than delete, because this expressions should give the same result  if I am not wrong.

    ResaultOfDate.png

    but it gives me wrong result: In my sp list date is 10/14/2022 in format "MM/dd/yyyy", so the condition should be false, which means dont delete yet, but condition is true, i dont know why.

  • Expiscornovus Profile Picture
    Expiscornovus 31,402 Most Valuable Professional on 17 Oct 2022 at 09:48:00
    Re: Find difference between current date and date from SP list

    Hi @Aleksandra1,

     

    I see you are using some other action to retrieve the date data. Seeing the name of the action I assume it is a SharePoint Get Items action, correct?

     

    You either need to specify a specific item with an index or loop through all items of the outputs. Seeing the logic of your flow I expect you are only interested in one item.

     

    For that scenario you could use something like below:

    I used the [0] index reference, but you can also use a first function to retrieve the first item of the list of values.

     

    div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(outputs('Get_items_from_SP_list:_audit_template__CS')?['body/value'][0]['Date'])),864000000000)

     

     

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

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,369 Most Valuable Professional

Leaderboard

Featured topics

How to Ask for Help with Your Power Automate Workflow

Product updates

Microsoft Power Platform Community release plans