Filter Array by Created Date - Not pulling Under 24 Hours Items

[Anonymous]

I have a MS List with items constantly being added (a manufacturing dept. where we have a near 24-hour work cycle where items are being created at all hours of the day). At the start of the morning shift at 6am I have Power Automate sending out an email with 2 tables for open items, one for items more than 1 day old and one for items made within the last 24 hours.

My “older than 1 day” filter array pulls items correctly. However, my “the last 24 hours” is only pulling the oldest ones that are close to 24 hours old but is ignoring everything more recent.

The 24-hour cycle I think is the difficulty here due to possibly time zones or date formats that "flatten" time like in the startOfDay function.

The below formula is only pulling the highlighted few items in the attached image (ran the flow around 6-7am). (note on the list: My list has formulas in the Created column color-coding the age of the item, green for <24 hours, yellow for 24-48 hours).

@And(lessOrEquals(ticks(startOfDay(body('Current_time'))), ticks(addDays(startOfDay(item()?['Created'], 'yyyy-MM-ddTHH:mm:ssZ'), 1))),equals(item()?['Status']?['Value'], 'To Do'))

My "older than 1 day" formula in case it adds some context, but this one seems to work perfectly.

@And(greaterOrEquals(ticks(startOfDay(body('Current_time'))), ticks(addDays(startOfDay(item()?['Created'], 'yyyy-MM-ddTHH:mm:ssZ'), 1))),equals(item()?['Status']?['Value'], 'To Do'))

[wskinnermctc · Accepted solution]

You are correct it has to do with time zones. 

You are using the step Current Time (which is good), but that is the same as using the expression utcNow() which will get the current time in utc time zone.

I'm in Central U.S. time zone which is currently -5 hours behind the utc time. So when it is 6:00am in Central time it will be 11:00am in utc.

So when you use the startOfDay() expression, it is using the current date which is fine, but it will use the start of day of utc which is midnight so Aug 8, 2023 12:00am UTC will be Aug 7, 2023 7:00pm Central.

The time coming from the SharePoint list will also be in utc even though when you look at the list the created time will be displayed in your time zone.

Using my Central Time zone as an example and assuming you run the flow at 6AM:

CurrentTime = 6:00AM Central Time

The current time will show as "2023-08-08 11:00:00Z" since it is in the utc timezone.

• convertFromUtc({CurrentTime},'Central Standard Time') = which will give me 6am in my timezone as the correct time format that power automate can use.
• addDays(convertFromUtc({CurrentTime},'Central Standard Time'),-1) = which will give me 6am my timezone for yesterday since I added a negative -1 day.
• convertToUtc(addDays(convertfromUtc(body('CurrentTime'),'Central Standard Time'),-1),'Central Standard Time') = which will convert yesterday 6am my timezone into the utc value that can be compared to the Created datetime of the item.

@greaterOrEquals(ticks(item()?['Created']),ticks(convertToUtc(addDays(convertfromUtc(body('Current_time'),'Central Standard Time'),-1),'Central Standard Time')))

Try this formula in your filter array. If the flow is run at 6AM it will return any items that are Created after 6AM of yesterday.

I made a quick example that shows how the values are converted. I had to use a variable and convert that to Utc to start the flow, but pretend those two steps are the same as Current Time if you ran the flow at 6:00AM.

Example Flow OverviewExample Flow Detail - Getting Current Time of 6AM as utcExample Flow Detail - Converting utc to current timezone and subtracting -1 a dayExample Flow Detail - Get Items and Compare Dates

The above flow photos are to show how the time is converted from utc to your local timezone then a day is subtracted followed by being converted to utc. The compose step will compare if my items dates are greater than or equals yesterdays time.

Example Time ResultsExample Items Compare Time

The final expression used in my compose is the same as the one I provided for your filter array except it doesn't need the additional conversions because that was done in previous steps.

Here is what it would look like if I made a filter array.

Filter Array

Hope this helps, let me know if you have any questions, or you can mark this as solution if it solves the issue.