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

List rows present in a Table filter Query before today filter

Hi all,

 

I have come across an issue that I have been trying to search my way around and I am afraid not getting very far. 

So far as I understand in a sharepoint excel file todays date is stored as "44157" so I tried 

DateofClaim lt StartOfDay(utcNow())

So I checked the output of StartOfDay(utcNow()) this returns "637416000000000000"

how am I able to return todays date in a format that excel can do a less than check on the filter ?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

PowerAutomate-ExcelDayToday.PNG

 

While your approach is completely valid. It is probably easier to convert the Excel integer into a date and compare based on that instead. I wrote some guidance here:

https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/

 

View solution in original post

2 REPLIES 2
Paulie78
Super User
Super User

This expression will give you todays date as an Excel integer:

div(sub(ticks(formatDateTime(utcNow(), 'yyyy-MM-ddT00:00:00')), 599264352000000000), 864000000000)
Paulie78
Super User
Super User

PowerAutomate-ExcelDayToday.PNG

 

While your approach is completely valid. It is probably easier to convert the Excel integer into a date and compare based on that instead. I wrote some guidance here:

https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,764)