Power Platform Community Forum Thread Details

Hello! I've been trying to imitate the functionality of Excel's WORKDAY() using Power Automate. I plan to add a Due Date to a newly added row.

So far, the flow looks like:

#####

Dataverse Trigger when a Row is Added/Modified

Initialize ReceivedDate from trigger's Received Date (Date only column) as String - Formula: formatDateTime(Received Date, 'yyyy-MM-dd')

Initialize DueDate as String - Empty

Initialize AddedDays as Integer - 1 (default)

Initialize IsHoliday as Boolean - false (default)

Conditional: DayOfWeek(ReceivedDate) == 5

• If True: AddedDays += 2

List Rows (of Holiday table)

• Select Rows: Name and Date

• Sort: Date

• Filter: ReceivedDate eq formatDateTime(AddedDays(ReceivedDate, AddedDays), 'yyyy-MM-dd')

Conditional: List Rows value != 0

• If True: Do Until (a loop that will add a day to AddedDays if new DueDate is Holiday, until it is not)

Compose new DueDate

Assign to Due Date of the Row

#####

My problem is List Rows's Filter does not work. It returns an empty body (even when I used a solid example).

One solution suggested is using a formatDateTime to ensure they have the same type, but it wouldn't work as well. The Dataverse table it tracks is a Date column (Date only), so there should be no problems when comparing it. How can I make the Filtering work?

Categories:

Building flows

Hi, hope you are doing well.

First things first, I noticed in your post, the OData query that you are using is sort of wrong.

Instead of: ReceivedDate eq formatDateTime(AddedDays(ReceivedDate, AddedDays), 'yyyy-MM-dd')

It should be: ReceivedDate eq formatDateTime(AddDays(ReceivedDate, AddedDays), 'yyyy-MM-dd')

But this could be a typo as well, so I am ignoring it for now.

Now here are some suggestions on why else your current filter would be failing:

Missing Single Quotes: If your expression evaluates to 2024-12-25 but isn't wrapped in single quotes ' ', OData might try to treat it as a mathematical subtraction (2024 minus 12 minus 25).
Timezone Offsets: Although it's a "Date Only" column, Dataverse still stores a hidden time component and defaults it to midnight i.e., 00:00:00Z. If your filtered date is slightly off due to UTC conversion, the eq (equals) operator will return zero results.

Personally, I think it is most likely the time zone offset thing.

If List Rows continues to return empty body due to hidden time data, use a "range" filter to capture the entire day:

ReceivedDate ge 'formatDateTime(AddDays(ReceivedDate, AddedDays), 'yyyy-MM-dd')' and ReceivedDate lt 'formatDateTime(AddDays(ReceivedDate, AddedDays+1), 'yyyy-MM-dd')'

If this reply helped you in any way, please mark 'Yes' for "Was this reply helpful?" and give it a Like 💜
In case it resolved your issue, please mark it as the Verified Answer ✅.