Deleting Rows on Spreadsheet Based on Date

[EJMikol]

I'm trying to build a flow to delete rows on a spreadsheet once the current date has been reached. I am attempting to use the column name less than UTCnow, that does nothing to the rows. When I use greater than it deletes all rows. This seems like it should be easier, can someone help with this?

[Jronash]

Excel stores dates in a pretty annoying way.  A simple comparison to utcNow() isn't going to work, I'm afraid.

https://www.shanebart.com/ms-flow-excel-dates/

[EJMikol]

So using this articles equation, how would I convert the number into a date on my spreadsheet in order for it to be recognized as a date in my formula so I can use the delete row function properly?

[Jronash · Accepted solution]

You can basically copy and paste the expression from that article, and then update it so that it points to your column instead.  We'll also want to change the default value.  It looks like your column is called 'End quarantine date', so the expression would be this:

if(empty(item()?['End quarantine date']),utcNow(),addDays('1899-12-30',int(item()?['End quarantine date']),'yyyy-MM-dd'))

In Automate, it looks like this:

It's good to understand a piece of code if you're going to be using it, so let's break that expression down a little.  First, looking at it with proper indentation makes it easier to understand.

if(
  empty( 
    item()?['End quarantine date']
  ),
  utcNow(),
  addDays(
    '1899-12-30',
    int(item()?['End quarantine date']),
    'yyyy-MM-dd'
  )
)

It starts by checking to see whether the field is empty.  It does this so you can provide a default value so your flow won't fail if there's a row with no End quarantine date.

Next it determines what value is returned if the field is empty, and what value it returns if the field is not empty.

In the article, it returns null if the field is empty.  This makes sense, but it won't work for us because trying to compare a null value to utcNow() in your condition will cause your flow to fail.  So we need a default value that will not fail.  I chose utcNow() as the default value, which means that if there is a row in your spreadsheet that does not have an End quarantine date, it will NOT be deleted (since utcNow() is not less than utcNow()).  If you want rows with empty values to be deleted, we'd have to change this.

Next, it specifies what is returned if the field DOES have a value.  As the article outlines, in date columns, Excel stores the number of days that have passed since Dec 30, 1899.  So to get our date in the proper format, we need to take the number from your spreadsheet and add it to Dec 30, 1899.  Then we format it to yyyy-MM-dd format.

Hopefully that makes sense.