Date format multi timezone year changing

[robinisathakur]

I have a Power App which is used in the UK and the US, with the dates stored in SharePoint as US time format. SharePoint site region settings list is in Pacific time. In the US this works fine, but in the UK if somebody opens the item edit form, it alters the time and year  to something in the future 2025 or 2026 seemingly randomly. Time Zone is set to Local in the PowerApp on the date control. 

Has anyone seen this before? 

[WarrenBelz]

Hi @robinisathakur ,

This is a Date/Time field in SharePoint ? Also is is being displayed in Power Apps as a date or a Text conversion ?

[robinisathakur]

It’s a text field in SharePoint which is formatted as a date time. This is then used as the default for the datetime picker in PowerApps, and that works fine in the US, but when people in the UK open the form, the date picker shows the wrong date. 

I did it this way because otherwise when I stored it as a date time field in SharePoint I had other issues with time zones where UK time zone people  saw the times incorrectly. The goal is to track flight times which are always at local time and should not ever be altered. 

If there’s a better way of doing it, I’m all ears. 

[WarrenBelz]

@robinisathakur ,\

I was suspecting you were going to tell me that. Unfortunately, the rest of the world (outside the USA) format dates in a logical ascending (day-month-year) fashion, but that is another discussion.

My first suggestion is to make it a date/time field and all will work as expected. 

Beyond that, you could turn it back "the right way" ( I am assuming your Country format is USA - it is Australia here)

If(
    Office365Users.MyProfile().Country = "USA",
    DateValue(ThisItem.YourFieldName),
    With(
        {
            _Data: Split(
                ThisItem.YourFieldName,
                "/"
            )
        },
        Date(
            Last(_Data).Value,
            First(_Data).Value,
            Index(
                _Data,
                2
            ).Value
        )
    )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[robinisathakur]

So that code you provided assumes I keep it stored as a text field? I’ll try it out today.

The reason I kept it as a text value was because it was adjusting the time automatically when stored as a date time for overseas people. For flights, it has to always remain the same at the origin. If there is a way to store as a date time and NOT have it update automatically to cope with time zones I’d do that as that’s the best solution. If I wanted to do that, would I just adjust SharePoint to UTC time zone, then change the date pickers to use utc instead of local time? 

[CindyZ]

@robinisathakur 
Thanks for your question!  I've struggled with this endlessly and have yet to come up with an answer.  I was back searching for the magic this morning and found your post. 

Sadly, I don't have your answer, but, in case it helps anyone, we've figured out what seems "random" is not necessarily so.  You may want to test this yourself to see if you are getting the same logic. 

The Great Britain date format is 

DD MM YYYY   

US date format is
MM DD YYYY 

US date shows 05 23 2024 
GB sees 05-Nov-2025

23 05 2024 (GB should see)

• What is happening is this. GB sees their format and the date is calculating for her format.
• 5 as the 5th day/not the 5th month, so 05 is the day
• 23 translates to 23 minus-12 months = 11th Month (thus November -- It does not know what to do with a month greater than 12)
• YY Adds the remaining 12 months to the year which bring you to the 11th month of 2025
  
  Also,  I have people in South Africa who have the dd mm yyyy date format who see the date as expected and the event is also added to their calendars correctly.  It seems to be only UK that has this issue.
  
  Off to see if I can adapt Warren's recommendation. 
  
  
  

[RTDoc]

That's interesting Cindy, glad it's not just me seeing this behaviour. 

@WarrenBelz That doesn't work for me. They are still seeing the behaviour where it alters the year in the UK when they open the form with this code in place on the default for the date picker. Does it matter if the date picker itself is set to use Local time or UTC?

[WarrenBelz]

Hi @RTDoc ,

I assume you are also @robinisathakur . What is the exact Text format the date is stored in ?