Power Platform Community Forum Thread Details

I have a site content type named Asset, and this content type contains a site column of type Date/Time named ETA. now i am using KQL formula inside PnP Modern search web part to get all items which have their ETA equal today + have the Asset content type as follow:-

{searchTerms} ContentType:"Asset"
RefinableDate01=today

now i am facing this issues:-

1) in SharePoint online >> the date/time will be stored based on the UTC time, while my SharePoint site time zone is set to Pacific Time.

2) Also the "today" keyword inside the above formula will be based on UTC.

3) So if the current date time is 03:40 am Pacific Time + 10:40 am UTC.

4) And I added a list item and i defined the ETA Date/Time to "8/21/2024 10:00 pm ":-

5) This will be stored inside SharePoint using UTC which will be "2024-08-22T05:00:00Z".
So when i do a search using this Query Template: -

{searchTerms} ContentType:"Asset"
RefinableDate01=today

I will get the above item, although based on the site time zone which the users follow this item is not for today 22 August (based on their timezone) it is for yesterday 21 August..

so can i fix this using Power Automate? where i will define another field named ETA(Pacific)? and define a scheduled flow which runs on the items modified in the last one hour, and update the ETA(Pacific) in a way that will work when i search for the items using this KQL :-

{searchTerms} ContentType:"Asset"
ETA(Pacific)=today

any advice?

Categories:

Building flows

I am not 100% understanding what you are looking to accomplish. But, SharePoint stores all dates in UTC even when you set a different time zone at the site level. Users should specify their time zone in their user profile, which will then adjust the UTC stored value to their time zone.

If you need to convert this in a workflow to send data to the users, you can use a Graph API request to get their time zone value and adjust it with the Convert time zone action. I think if you modify the time how you propose, this will just throw off the date/time for people who have correctly set up their user profile.

The flow would look like this:

This is the scheme for the Parse JSON action and what is returned:

{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "archiveFolder": {
            "type": "string"
        },
        "timeZone": {
            "type": "string"
        },
        "delegateMeetingMessageDeliveryOptions": {
            "type": "string"
        },
        "dateFormat": {
            "type": "string"
        },
        "timeFormat": {
            "type": "string"
        },
        "userPurpose": {
            "type": "string"
        },
        "automaticRepliesSetting": {
            "type": "object",
            "properties": {
                "status": {
                    "type": "string"
                },
                "externalAudience": {
                    "type": "string"
                },
                "internalReplyMessage": {
                    "type": "string"
                },
                "externalReplyMessage": {
                    "type": "string"
                },
                "scheduledStartDateTime": {
                    "type": "object",
                    "properties": {
                        "dateTime": {
                            "type": "string"
                        },
                        "timeZone": {
                            "type": "string"
                        }
                    }
                },
                "scheduledEndDateTime": {
                    "type": "object",
                    "properties": {
                        "dateTime": {
                            "type": "string"
                        },
                        "timeZone": {
                            "type": "string"
                        }
                    }
                }
            }
        },
        "language": {
            "type": "object",
            "properties": {
                "locale": {
                    "type": "string"
                },
                "displayName": {
                    "type": "string"
                }
            }
        },
        "workingHours": {
            "type": "object",
            "properties": {
                "daysOfWeek": {
                    "type": "array",
                    "items": {
                        "type": "string"
                    }
                },
                "startTime": {
                    "type": "string"
                },
                "endTime": {
                    "type": "string"
                },
                "timeZone": {
                    "type": "object",
                    "properties": {
                        "name": {
                            "type": "string"
                        }
                    }
                }
            }
        }
    }
}