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

1 REPLY 1
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

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,099)