Power Apps Time zones, UTC, SharePoint, and Excel

In this video, you will learn about PowerApps timezones and UTC and how they play out when working with data sources like SharePoint and Excel. If you ever wonder why you see the wrong time or date in your apps or datasources this is the video for you.
Power Apps Consulting and Training at www.PowerApps911.com
#PowerAppsTimeZones

[@derek_shannon]

Wow, Shane, super "timely" video ;-D Seriously, though, this video helps me better understand a bug we had to fix for our app, where users in Europe for example suddenly couldn't see any data since the date filters didn't line up, even though it seemed fine within the Eastern time zone it was created in.

[@ShanesCows]

That is awesome Derek and exactly why I made the video. Have a great day!

[@theelectricsheep8204]

Wow, that was so intense, my brain has chucked out a whole chunk of my childhood memories to make room for what I have just learned.

[@ShanesCows]

Hopefully only bad memories. There was a a reason I waited so long to do this video.

[@kayfax672]

Thank you so much for this - I love the "are you going to watch this video 10 times? Probably!". My kids are starting to mimic you, that's how often I watch it. This solved soo many issues for me. Inspired by your video I checked the settings on our Sharepoint Sites and surprise, according to SharePoint, we were in California, although actually in Central Europe... Now I understood why all date-entries in my list were wrong after entering... Also, I am working on an app, where, based on a Dropdown selection, the output of a form input needs to be used in a Html or RichText in three different languages (EN, GE, FR, hurray multiple speaking countries), AND therefore needed to output the date WITH weekday in three different languages, but AFTER the "view form (selected item)" had turned the original date into a text value... so thanks to you it is now converting it back to date value, and then outputting with a weekday :-)

[@MattSchuessler]

Great Video. This topic is such a cluster but fundamental for Devs to understand. Thank you for sharing it!

[@ShanesCows]

Glad you enjoyed it!

[@miltinhocamo3754]

Thanks a lot Shane ... you responded to me all my doubts about the crazy utc behavior, questions i did to you a few days ago... Good job.

[@ShanesCows]

Awesome. Glad I got it all. 😎

[@lucianojesusdeoliveira3513]

This video saved my day, Shane! Thanks, man! Greetings from Brazil! :)

[@huiengchua364]

Thank you Shane. Great sharing!

[@ShanesCows]

Glad to help. 😀

[@tenisviejos]

Great video, Thank you! This is extremely useful, no matter the app/platform!

[@ShanesCows]

You're welcome!

[@Sustainable.149]

You are best of the best Shane 😍,, thank you, most kind 🙏🏼🙏🏼🙏🏼

[@paulkroon]

He Shane, nice to hear you're in the Netherlands. I hope you enjoy your stay. I would like to share a approach I always use. The date I don't store as a date but as an iso formatted number (20201123). Based on this number field I calculated a date field purely for display purposes. An extra benefit with this approach is that indexing and filtering is more optimized with a number which will speed up your apps. If I need to show the real time, lets say 9 should stay 9 always, I also add a number field to store the offset from utctime. Always enjoy looking at you're video's Shane.

[@ShanesCows]

Paul thanks for sharing. I have used an approach similiar in some of my old SQL apps. Turning the date into Integers made a lot easier. Thanks again!

[@nagamanikantaamudalapalli9407]

This Time Issues really melted my Brain. So Much Helpful video, Thank you.

[@ShanesCows]

Glad it helped!

[@ashokpershad]

As always great and important video...since long I faced this issue from last two years when I used excel as data source..I tried every way to resolve but finally let it go...today I understand why it happened 😁

[@ShanesCows]

Excellent. Glad this was the answer. 😀

[@louiscutmore]

Complicated but love it - thanks!!

[@ShanesCows]

Thanks 😊

[@franknielsen3219]

My head just exploded..... Also having challenges when I combine SharePoint with Power Automate, and have to do something with the time. Can be a bit frustrating, but at least I now Know why. THX a lot for great tutorial :) And have a great day

[@ShanesCows]

Thanks Frank. 🐶

[@stuartrobinson1658]

Very useful video Shane.

[@ShanesCows]

Thanks 🤩

[@mvos1980]

Hi Shane, thanks for your very clear explanation. I'm busy with an app that will be used across the world with the data source (D365 Business Central) in The Netherlands. I'm warned now. Thanks!

[@ShanesCows]

Good luck 👍

[@alickcampbell8915]

Thank you for the clarification

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@yashwantsuryawanshi9080]

Thank you Shane...

[@ShanesCows]

Glad to help. 😀

[@sadelina2763]

Thanks I change time zone in sharepoint ang all works :D

[@ShanesCows]

Awesome.

[@sundrapillay6668]

Thanks Shane great video

[@ShanesCows]

Glad you enjoyed it

[@gajananchoudhary3914]

@@ShanesCows I would like to convert IST to UTC in poweapps through code. How will do?

[@sushantpenshanwar8645]

Super Thanks !!

[@ShanesCows]

Thank you too!

[@senolibrahim]

thank Shane :) thats the point

[@ShanesCows]

🐶

[@kennyj5493]

Shane great video as always, but i dont know if this is exactly linked to an issue i am currently having or not, although using the Text as time might solve longer term but is a lot of work now for a fully built app that's being used. I have a PowerApp where i patch Events (and their date) to a SharePoint list. SharePoint site is in UTC, my date picker is set to local and all my users are in the UK. When i add events everything works fine. But when some other users add events it shows the day before, BUT if i click edit on that record, it then shows the correct date (forward a day). Is this really what you are describing? When i have a label that shows now(), we get the same values in PowerApps. Im driving myself crazy on this one, any ideas?...

[@stevenned23]

Man what a great video again. It is ridiculous that several components in de Power Platform as in Sharepoint and Excel all can use different time zones. I had hard discussions with Microsoft about this. In your video I hoped to see a calculated column in your list based on de function now() (So calculated in SPO). I have that and it returns always the Pacific time zone: ALWAYS! Even when I have my SPO site regional settings on Amsterdam time zone..... It also the case with other columns with a time stamp. Really annoying. To solve this, I tried two days to find all time settings in my 365 settings because I thought there were some internal conflicts between time zone settings. Unbelievable on how many places you can edit time zone settings. I didn't count but I think there are more than 15 places where you can change this........and they all interact on an other part in 365. Your video brings some work arounds and I'm happy with that but it shows also that Microsoft have some work to do. Save a time field as text....... Ridiculous. Greetz from the Netherlands. Steven

[@ShanesCows]

Thanks Steven. Time is hard for sure. Hopefully one day they find a way to make it easier.

[@davidmckenzie1889]

I'm glad I live in the UK, this is UTC+0 ;-) Well at least until Daylight Savings Time adds an hour!

[@ShanesCows]

That is one solution for sure David 😂

[@CarFinanceSimplified]

Hi Shane. Desperate for help. I have an app whose date formats refuse to work correctly when running it on Windows. When I am previewing in Windows or running it on a Mac and iOS it all works perfectly. The app always wants to display with US date formats regardless of the browser setting, windows setting!!! I can’t figure it out.

[@stayalive1017]

Shane: "Hopefully I suffetiently melted your brain" My brain is happy with this info :)

[@ShanesCows]

Awesome! 😁

[@combatter]

Hi Shane, I had hoped to find a solution to my issue in your video, I cannot find anything on this unfortunately. I was wondering if you know how to fix getting a data from an excel online file (on teams) in Powerapps. It doesn't show in either a gallery or datatable, so it's not being recognized (blank/no data). I tried all types or time or datatypes, it's just not showing. But when I use Excel (static file) it does show. Problem is, my source is Teams and I cannot change it. Can you help?

[@abhishekkunder1193]

Great video Shane 👍 just one question... will this issue be there in CDS also

[@ShanesCows]

CDS aka Dataverse can have some of these challenges but SharePoint is where they seem to cause the most issues.

[@kumarb1261]

Hi Shane, my Powerapp datetime field was set to UTC timezone, and the SharePoint site was set to PST timezone. When I enter data in the SharePoint form it automatically converted to the PST timezone but if we opened the same item in view/edit mode again it is converting to local timezone. Please suggest how to fix this. I need to show powerapp form and SharePoint list saved date should be in PST timezone. Can you help!

[@kimgaskins6966]

Shane, we're having a time issue and we are NOT using a Sharepoint list, we're using the dataverse. We select 6/30, the actual value stored (via the flow) in the table is 6/29. We'll try .JustDate and see if that makes a difference.

[@ShanesCows]

Dataverse store time in UTC just like SHarePoint. Hope you got it figured out.

[@sanjeevtiwari17]

Very helpful video.can you please make some videos for model driven apps/dynamics 365.

[@ShanesCows]

I don’t do much with them so I am no help. 😑

[@CarFinanceSimplified]

UK date formats drive me crazy in Powerapps and Excel. No matter what I do, Excel always wants to convert a UK date format to US, unless the DAY is 13 or greater! 07/02/2024 (UK) always hits Excel as 02/07/2024!!!!

[@dexterjamesignacio6879]

Hello Shane, How can I display a time in a canvas app based on Internet Time or Server Time. (Which does not use the API) The problem is when I used the Function Now() it displays the user's local time in his/her PC. So everytime the user change their time, the Time in Canvas app will also change. I am creating an Attendance Tracker I don't want the user's to cheat in their Time-in or Time-out by changing their device or pc Time. I hope anyone can help me. Thank you.

[@cmarcelacg]

thanks in advance, I do not speak English and I am a beginner in power apps, I selected a one-click datatimepicker created in power apps and copied it to a power apps form connected to a share point list, I would like it to be TimeZoneIndependent, is this possible?

[@landermatos7905]

valuable tips

[@ShanesCows]

Many many thanks

[@rochellenicholson6372]

Shane, I am having trouble with this. I have a datacard from sharepoint which is a date picker in my app, but I want it to display DD/MM/YYYY can this be done, in my custom form.

[@ShanesCows]

Use the Text function. Text(Now(), “dd/mm/yyyy”)

[@alancoulter9688]

Hi Shane, I found issues when the current computer time is adjusting for daylight savings time (so in my case Melbourne Aus becomes UTC+11), but SharePoint stays at UTC+10. So my calendar control has say 18/10/2021 (dd/mm/yyyy here) but if I set it to say 01/05/2021 (1st of May), after patching it, the date in SharePoint becomes 30/04/2021. I tried DateAdd(calendar.SelectedDate, -TimeZoneOffset(), Minutes), but even that fails (because of the extra hour I think).

[@alancoulter9688]

Actually I found that it doesn't really matter what timezone offset I try to apply, SharePoint will capture the UTC date as Thursday, 30 April 2020 2:00:00 PM, which when converted to local time is Friday, 1 May 2020 12:00:00 AM (so SharePoint is ignoring the time value passed into the update). The problem is however is that the SharePoint Online view displays the date only field as 30/04/2021 after the PowerApp update, then when you click on the item in SharePoint and click the date column, it changes it immediately to 01/05/2021. It must be a rendering bug or something with SharePoint Online. If I re-apply the regional settings, the view then displays it as 01/05/2021, but if I refresh the page it changes it back to 30/04/2021 - weird!

[@alancoulter9688]

Note that I am working with Date Only fields

[@rodrigopilli96]

Hi Shane.. Thanks for your videos. My question is, In my powerapps i have two dropdowns one by hour and other by minutes. How I do to this two drpdowns save time in my excel table?

[@ShanesCows]

Use the DateTimeValue function to combine them into an actual datetime object.

[@rodrigopilli96]

@@ShanesCows In my PowerApps project, in my card the update looks like this: "TimeValue(HourValue4.Selected.Value, MinuteValue4.Selected.Value)", but when you go to save in excel, you save a totally wrong number. I already sent an email that I got on your website asking for help and I didn't get a reply.

[@chahineatallah2636]

Great video, do you have video about dataverse dates ?

[@ShanesCows]

Not a lot but if you check this video kzbin.info/www/bejne/oanbdnZnra1sqas about the 13-minute mark (There is a chapter link in video description) will take you where I add a date column and talk about a few of the date type nuances that should help.

[@chahineatallah2636]

@@ShanesCows will there be a dedicated video about utc, user local , and time independent? Most confusing subject 😀🤦‍♂️

[@davidcarr7793]

Does anyone know of an equivalent function to timezoneoffset for power bi?

[@mblherry]

We just had that issue and was struggling to fix it. What would you recommend to ensure that the user doesn't change their device time to fool the system. We need a common time source for an attendance tracking app. What we did is on app start we created a record in SharePoint and capture the create date/time, then use a timer to build a clock in the app and we took the time from there. We also reset the clock (with a new record created in SharePoint) every 5 mins.

[@SwadDashcam]

Greetings, If you store in SharePoint, it is stored as UTC regardless of what the users time zone offset is. Even if they change their zone, SharePoint still shows UTC....... I believe !

[@paulkroon]

He Marlon, the created date is in utc time and the same goes for the modified. So instead of your timer you could better use an variable to store the respons of your patch() and pick the modified date from the varible to calculate the time past.

[@ShanesCows]

Thanks for the assist guys. I like the answers. 😀

[@mardithpascua8112]

When I tried to display the my timezone, I am getting UTC-4 however, it should be UTC-5 based on my computer timezone? Is there any workaround for this one, I am thinking that Daylight savings is one of the factor here?

[@ShanesCows]

I have never seen it wrong. If you use Now() in a label do you see the correct time?

[@ashokpershad]

Shane, How to deal with default "Created" and "Modified" columns ?. I believe we cannot make them text...

[@ShanesCows]

Correct. You cannot change them as they are auto generated

[@gajananchoudhary3914]

Hello Sir, I would like to convert Ist to UTC in power apps, how I will do?

[@R.SEntertainments]

Hi,Good Morning, am a beginer in powerapps and trying to achieve a task and facing complexity to achieve,my task is to retrive a data from SQL based on Date filter,but the date filter in my laptop is "DD-MM-YYYY" and when an retriving the data from SQL am receiving as "MM-DD-YYYY",as to filter the sql data i need to pass a filter as "DD-MM-YYYY" format,how can i achieve any possibility,Date is Date data type

[@ShanesCows]

What if you said Filter(SQLTable, DateColumn = Text(Today(), "mm-dd-yyyy"))

[@R.SEntertainments]

​@@ShanesCows Set(STime,LookUp('XXXX','Task ID' = ThisItem.'Task ID' && Date = DateValue("2023-01-22","Fr") && Completed = 0,'Start Time'));

[@ClashwithAshishClashboy]

I need to capture time in UTC in a request form no matter from where the request is coming in or what is the local time of the requester. If anyone has any idea please help me

[@ShanesCows]

Power Apps has a TimeZoneOffset function that will tell you how far from UTC the users session is. So you could manipulate it.

[@sumaiyaissam1835]

I have a requirement to send power automate tasks.... at different time zones.... eg., 9am (so people should receive notification at 9am, irrespective they are in Asia, Europe or US) How can I do that ?

[@sumaiyaissam1835]

@Shane Young looking for your inputs here

[@shubhamchavan7338]

Hey Shane Young Sir, My Timezoneoffset(Now()) is showing me in minus(-).Why?

[@ShanesCows]

Are you in a time zone that + GMT? I would assume those show as negative numbers but not sure.

[@shubhamchavan7338]

@@ShanesCows Yes, My time zone is GMT. Thanks for your guidance.

[@leetyler3102]

why should we not be using refresh(....)?

[@ShanesCows]

It goes and fetches all of the data from your data source which on a slow connection can take a long time and there is usually no reason to do so.

[@maorifulla]

You could see the life drain from your eyes at the end of that video...lol Tough topic alright.

[@ShanesCows]

Yup. I don't know anyone who enjoys talking about or working on it. 😖

[@stanleym69]

The video I know I have to watch but dreading getting stuck in 🤣 it’s 9pm and think it’s a tomorrow task 🤣

[@ShanesCows]

It is tomorrow. Did you watch it? 😎

[@stanleym69]

My head hurts now

[@VANDIK01]

make sense, thanks @ShaneYoungCloud 👋👋👍👍