Delete row if it is in the past

(0) Share

Report

Posted on by frixel

901

Hi all,

I have this excel file and will delete the rows older than today.

Than i make this flow but it is not delete the rows from yesterday.

What is wrong?

All responses (9)

Answers (1)

efialttes 14,756 on at

Like (0)

Report

Re: Delete row if it is in the past

@frixel

I just so this topic was marked as solved, so I guess it was you that followed the instructions and overcame your challenge. Thanx for your kindness, and Happy Flowing!
Verified answer

efialttes 14,756 on at

Like (0)

Report
Re: Delete row if it is in the past

Hi again!

I can see two approaches:

1.- If you handle Start Time on your excel column selecting its format as Date instead of text. THis way, 'List rows present in a table' will provide some funny numbers (days since Days Since 1899–12–30) as explaind in previous posts.

In order to compare them with today, I would use the following WDL expression on the right side of you condition rule:

string(div(sub(ticks(utcNow()),ticks('1899/12/30')),864000000000))

2.- If you handle Start Time on your excel column with Text format, you definitly need to store it as yyyyMMdd in order to compare it properly with your today

Hope this helps
frixel 901 on at

Like (0)

Report

Re: Delete row if it is in the past

I don`t understand what i now must to do.

I don`t understand what i must fill in for the dateformat in the condition.
frixel 901 on at

Like (0)

Report

Re: Delete row if it is in the past

@efialttes

I thought it worked.
I have now added an extra column to the sheet with only a date.

This is the flow with the condition.

All the rows will be deleted also the rows with date today.
efialttes 14,756 on at

Like (1)

Report

Re: Delete row if it is in the past

@frixel wrote:

@efialttes

Edit.

Now the rows are deleted with the date format:

convertTimeZone(utcnow(),'UTC','W. Europe Standard Time','M/dd/yyyy HH:mm:ss')

But it deletes al the rows from today also because the time is in the past.

I want only delete rows with the date in the past.

What must i change in the flow?

Can you share more details of your current Condition? Are you using 'less than' operator? 'less or equal'?

As far as I understand, if dates are stored as text with format 'M/dd/yyyy HH:mm:ss' you need to reorder them first in order to make such operator work, i.e. use yyyy/MM/dd instead

So this way 2020/08/16 (aug 16th 2020) is greater than 2019/09/16 (sept 16th 2019)

But with the current representation in your Excel, I believe 8/16/2020 (aug16th 2020) is less than 9/16/2019 (sept 16th 2019)

THanx!
efialttes 14,756 on at

Like (0)

Report

Re: Delete row if it is in the past

Hi again

Sorry I didn't explained myself properly.

I was just trying to confirm that the excel column was of type 'Text'; because if it is of type 'Date', it is internally handled as nr of days since 1899-12-30 and represented as a string, as explained here:

https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate

I would keep the column as 'Text' and try with the TimeZone conversion... assuming your date input format is yyyy-MM-dd

Hope this helps
frixel 901 on at

Like (0)

Report

Re: Delete row if it is in the past

@efialttes
Edit.

Now the rows are deleted with the date format:
convertTimeZone(utcnow(),'UTC','W. Europe Standard Time','M/dd/yyyy HH:mm:ss')

But it deletes al the rows from today also because the time is in the past.
I want only delete rows with the date in the past.

What must i change in the flow?
frixel 901 on at

Like (0)

Report

Re: Delete row if it is in the past

@efialttes

Thanks for your advise,

I changed the format to "Date"

And changed also the UTC-Timezone

But the condition is always false.
efialttes 14,756 on at

Like (1)

Report

Re: Delete row if it is in the past

Hi!

Some thoughts

First of all I am assuming 'Start Time' column in your excel file is formatted as Text not Date, right?

Next, when you invoke utcNow() it provides date and time for UTC timezone. So, you will probably need to convert to your own time tone to avoid issues like the one you described. You can do it by means of the WDL expression convertTimeZone() but also by means of an action block with the same name.

Hope this helps