How to write Power Automate expression for year

[Anonymous]

Hello All,

I have a SharePoint document library. 

I want to archive files based on created date minus 3 years column in SharePoint.

For example: I want to move all files that has been created 3 years ago based today's date. This automation can go on.. 

What I am looking for is this:

In Power Automate,  in the Filter Query: 

How I can write a formula that achieve the above requirement?

Thanks for your advise in advanced.

[MarvinBangert]

Hey @Anonymous 

you can use the expression "addDays()", the first parameter is your datetime (e.g. utcNow() to get the current date time when the flow starts), the second parameter is the days you want to add (e.g. 1095 but we want to go backwards so -1095) and the third is the format. Your Column "Year" could be the standard SharePoint "Created" column. Your expression could look something like this:

Created lt 'addDays(utcNow(),-1095,'yyyy-MM-dd')'

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

[Anonymous]

Thanks Marvin. I think you are almost there.

I want the formula/query looks like this:

Any files that greater than 3 years utc(Now) should be moved to the archive document library.

We are in 2021. If a file created date(in SharePoint default column) is 2018 and then it should be moved to the archive document library.

Such as: Created = utc(now) - 3 years.

[MarvinBangert]

I see, so not on the specific date but in general all documents from the year. Please try the following expression to get all documents between the 01/01/2018 and 12/31/2018:

Created gt '2018-01-01' and Created lt '2018-12-31'

If you want to make it dynamically, you can also use:

Created gt '@{sub(int(formatDateTime(utcNow(),'yyyy')),3)}-01-01' and Created lt '@{sub(int(formatDateTime(utcNow(),'yyyy')),3)}-12-31'

First we get the UtcNow(), format this into only the year, and then subtract 3 (years). Because you can only subtract numbers, we format the "2018" into a value using int():

sub(int(formatDateTime(utcNow(),'yyyy')),3)

Hope this helps you!

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

[Anonymous]

In this case: sub(int(formatDateTime(utcNow(),'yyyy')),3)  will calculated 3 previous year?

Let's say we are in 2025, then the formula would give us 2022?

Thanks

[MarvinBangert · Accepted solution]

Yes, sub() stands for subtract and will subtract the second number from the first. The first number we dynamically calculate by utcNow(), using format we only get the year of utcNow(). So the expression calculates currently 2021 - 3 = 2018, if we are in 2025, it will calculate 2025 - 3 = 2022.

You are welcome!

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.