cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lexx
Regular Visitor

Delete a specific row in Excel based on Date

Hi All,

Wonder if you can help. I want to build a flow that takes a specific date and time from MS Forms and searches for it in a specific column in an Excel spreadsheet. When it finds this date, I need it to delete that row. This is part of a larger flow, however I think I have run into a problem with the way Power Automate interprets dates vs Excel. Here are a few screenshots:

 

Lexx_0-1599641499097.png

I needed to use the "addHours" expression to move the time forward by +1 hour as Forms uses UTC whilst we are currently on UTC +1 (British Summer Time). Compose3 is used to change the date/time to an integer. I did this as I read somewhere that Power Automate needs this format to search for a date in Excel otherwise it searches for a text-based string.

 

Lexx_1-1599641712542.png

The excel file is stored on OneDrive with full permissions. I would prefer not to change anything in the Excel file (e.g. changing date format etc), since it is the store of data for the MS Forms questionnaire and used for other reporting.

 

Lexx_2-1599641833197.png

Appreciate any help or advice you can give as I've been scratching my head for a while on this one. I did also try a flow that searches for a straight forward date in pretty much all different formats (d/M/yy, dd/MM/yy, MM/dd/yy etc) but it just would not find the date in Excel.

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @Lexx,

 

I think you could try this approach, use addDays() function to convert the date number in Excel table into a Date string "M/d/yy HH:mm:ss", then append it into the condition action, loop each row to find the row met the condition and delete it:

addHours(utcNow(),1,'M/d/yy HH:mm:ss')
addDays('1899-12-30',int(item()?['Date']),'M/d/yy HH:mm:ss')

Annotation 2020-09-10 092853.jpg

 

Annotation 2020-09-10 092854.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-litu-msft
Community Support
Community Support

Hi @Lexx,

 

I think you could try this approach, use addDays() function to convert the date number in Excel table into a Date string "M/d/yy HH:mm:ss", then append it into the condition action, loop each row to find the row met the condition and delete it:

addHours(utcNow(),1,'M/d/yy HH:mm:ss')
addDays('1899-12-30',int(item()?['Date']),'M/d/yy HH:mm:ss')

Annotation 2020-09-10 092853.jpg

 

Annotation 2020-09-10 092854.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, 

I am having some issues in executing the flow after following the above steps. Please find below my problem statement. 

 

I have made a similar table in my power automate flow and I get an email along with submission date and time. The table is generated from an excel sheet present in one drive. The goal is to delete rows on the excel sheet once it passes 24 hrs since submission.  I followed the above steps, but I get an error 


Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'

 

I am not sure why this error keeps coming. I would be grateful if you can provide me an alternate route to my problem.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,525)