Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

Find difference between current date and date from SP list

(0) ShareShare
ReportReport
Posted on by 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,139 on at
    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 at
    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,139 on at
    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 at
    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,139 on at
    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

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

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

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard