If we have a site collection with Pacific Time Zone, how we can get the items which have specific date/time using search Query Template using "today" keyword

Question

I have a SharePoint Online site collection with these regional settings (Pacific Time): -

And I am using this Search Query Template to get the items which have their RefinableDate01 =today , as follow:-

{searchTerms} ContentType:"Carrier"

RefinableDate01=today

where the RefinableDate01 is linked to a SharePoint Date/Time field named "PU-ETA". now i am facing this problem:-

1- Currently the time is 03:40 am Pacific Time + 10:40 am UTC.

2- I added a list item and i defined the PU-ETA Date/Time as "8/21/2024 10:00 pm ":-

3- 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:"Carrier"

RefinableDate01=today

I will get the above item, although based on the site time zone which the user follow this item is not for today 22 August (based on their timezone) it is for yesterday 21 August.. so how i can fix this? so i can do the check for today based on the site settings timezone? and not based on UTC? so when the user specifies the Date/Time to be PU-ETA as 8/21/2024 10 pm this item should be shown inside the Today report on 21 August Pacific Time and not on 22 August UTC??

Thanks

Answer 1

Hi @john john Pter ,

Welcome to Q&A forum!

There is a method you can try:

1, Use a calculated column to convert the UTC time to Pacific Time.

• Create a new calculated column, let’s name it PU-ETA_Pacific.
• Use the following formula to convert the UTC time to Pacific Time (considering daylight saving time if applicable):

    =IF(AND(MONTH([PU-ETA])>=3, MONTH([PU-ETA])<=11), [PU-ETA] - TIME(7,0,0), [PU-ETA] - TIME(8,0,0))
  

This formula assumes that daylight saving time starts in March and ends in November. Adjust the formula if your daylight saving time rules are different.

2, Adjust your search query to use the calculated column that represents the Pacific Time.

• Modify your search query to use the new calculated column PU-ETA_Pacific instead of RefinableDate01.

    {searchTerms} ContentType:"Carrier" 
    PU-ETA_Pacific=today
  

Hope this can help.

---

If the answer is helpful, please click "Accept as Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.