cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Flow for Deleting rows older than 21 Days

Hi, I have an excel document on OneDrive for storing office visitor information. Each record takes up one row and has a date attached. I want to setup a flow to delete rows that are older than 21 days.

Are there any examples of how this might be done? Any help would be greatly appreciated.

Thanks

Stuart

9 REPLIES 9
Highlighted
Super User II
Super User II

Hi @stuartemh 

 

Yes perfectly doable. Here's the structure to achieve that:

2020-08-10 10_58_11-Flow - manuel.gomes@pestana.com - Outlook.png

 

The formula to calculate the date is the following:

addDays(utcNow(),-21)

 

Please note that the date comes from excel in a number format. Here's how to parse it:

https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/

 

Is this what you need?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted

Hi Manuel,

Does the date formatting need to be done before the Apply to each?

Also looking at your guide, request response is a premium function. Can the flow work without it?

stuartemh_1-1597059970070.png

Thanks

Stuart

 

Highlighted

Hi @stuartemh 

 

You can format it in the Condition with an expression. Just use formatDateTime('<date>','<dateformat>')

 

Regarding the template you can remove the trigger and the response and attach the middle in your Power Automate. All of that uses non-premium components so it should work fine.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted

Hi Manuel,

 

I'm getting an error when I run a test:

stuartemh_0-1597065969210.png

I'm using formatDateTime('<date>','<mm-dd-yyyy>') in the first part of the condition.

And addDays(utcNow(),-21) in the second.

 
 
Highlighted

This is what the flow now looks like.

stuartemh_0-1597066504620.png

 

Highlighted

Hi @stuartemh 

 

I was giving you the way to build the formula not the actual formula. You need to replace it with the variables for the data in the first field and the format in the second. 

 

So you need to use the variable in the Apply to Each and add it to the first, something like this:

 

formatDateTime(items('Apply_to_each')?['date'],'yyyyMMddThhmmssZ')

 

Makes sense? Then you can compare with the utcnow date on the other side..

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted

Hi @manuelstgomes,

 

I'm a bit confused with this to be honest. I'm testing the following flow:

stuartemh_0-1597138168631.png

With the condition 

formatDateTime(items('Apply_to_each')?['date'],'mm-dd-yyyy')

is equal to

addDays(utcNow(),-21)

 

This returns the error "Unable to process template language expressions for action 'Condition' at line '1' and column '14448': 'In function 'formatDateTime', the value provided for date time string '44048' was not valid. The datetime string must match ISO 8601 format.'."

 

Highlighted

Hi @stuartemh 

 

What Power Automate is telling you is that it cannot convert from a number (that excel returns as a date) to a valid date.

 

In my article, I explain how to do it, but it's quite easy:

https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/

 

Please add a compose before the formatDateTime to build the reference date:

formatDateTime('1-1-1900','dd-MM-yyyy')

 

and the use it the following formula in another compose:

addDays(outputs('Compose'),sub(items('Apply_to_each')?['date'],1))

 

Then use this compose in your condition, and you'll have two valid dates to compare.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted

Hi @manuelstgomes

 

Thanks the flow runs without error now. It isn't deleting the test rows in my spreadsheet though for some reason. When a new row is added into Excel, it adds the date in with the format mm-dd-yyyy . In my tests I am looking back 7 days: 

addDays(utcNow(),-7). Could this formula be looking at the mm, not the dd?
Please see the current setup below.

 

stuartemh_0-1597143377149.png

 
 
 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Users online (11,437)