Find difference between current date and date from SP list

(0) Share

Report

Posted on by Aleksandra1

254

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

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)

But I got this error

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

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report
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)

Was this reply helpful? Yes No
Aleksandra1 254 on at

Like (0)

Report

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.
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.

Was this reply helpful? Yes No
Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report
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')

Was this reply helpful? Yes No
Aleksandra1 254 on at

Like (0)

Report

Now I was trying to use you last expression
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

Was this reply helpful? Yes No
Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report
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')

Was this reply helpful? Yes No