Power Apps Time zones, UTC, SharePoint, and Excel

Поделиться
HTML-код
  • Опубликовано: 4 дек 2024

Комментарии • 112

  • @derek_shannon
    @derek_shannon 4 года назад +4

    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
      @ShanesCows  4 года назад +2

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

  • @debbiecheok9670
    @debbiecheok9670 Месяц назад +1

    i just want to take this comment to say you are a real life saver!! there are some changes from 3 years ago on the formula tho, but this still gave me such a quick fix!!!!

    • @ShanesCows
      @ShanesCows  Месяц назад

      Glad it helped. Making a new version of this is on my list. 😎 Time is still just so hard.

  • @theelectricsheep8204
    @theelectricsheep8204 4 года назад +2

    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
      @ShanesCows  4 года назад +1

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

  • @kayfax672
    @kayfax672 3 года назад

    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 :-)

  • @paulkroon
    @paulkroon 4 года назад +3

    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
      @ShanesCows  4 года назад +2

      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!

  • @MattSchuessler
    @MattSchuessler 4 года назад +2

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

  • @miltinhocamo3754
    @miltinhocamo3754 4 года назад +1

    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
      @ShanesCows  4 года назад

      Awesome. Glad I got it all. 😎

  • @lucianojesusdeoliveira3513
    @lucianojesusdeoliveira3513 2 года назад

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

  • @ashokpershad
    @ashokpershad 4 года назад +1

    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
      @ShanesCows  4 года назад

      Excellent. Glad this was the answer. 😀

  • @nagamanikantaamudalapalli9407
    @nagamanikantaamudalapalli9407 3 года назад +1

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

  • @franknielsen3219
    @franknielsen3219 4 года назад +1

    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

  • @kimgaskins6966
    @kimgaskins6966 3 года назад +1

    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
      @ShanesCows  3 года назад

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

  • @rochellenicholson6372
    @rochellenicholson6372 4 года назад +2

    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
      @ShanesCows  4 года назад +1

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

  • @CarFinanceSimplified
    @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.

  • @mvos1980
    @mvos1980 4 года назад +1

    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!

  • @huiengchua364
    @huiengchua364 3 года назад +1

    Thank you Shane. Great sharing!

  • @tenisviejos
    @tenisviejos 4 года назад +1

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

  • @kennyj5493
    @kennyj5493 3 года назад

    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?...

  • @rodrigopilli96
    @rodrigopilli96 4 года назад +1

    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
      @ShanesCows  4 года назад +1

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

    • @rodrigopilli96
      @rodrigopilli96 4 года назад

      @@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.

  • @ketimouravidze5895
    @ketimouravidze5895 2 месяца назад +1

    Shane, i wish you were my uncle.
    Thank you for the video

    • @ShanesCows
      @ShanesCows  2 месяца назад

      Happy to help. Have a great day. 🐶

  • @stevenned23
    @stevenned23 4 года назад +1

    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
      @ShanesCows  4 года назад +1

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

  • @abhishekkunder1193
    @abhishekkunder1193 4 года назад +1

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

    • @ShanesCows
      @ShanesCows  3 года назад

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

  • @davidmckenzie1889
    @davidmckenzie1889 4 года назад +2

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

    • @ShanesCows
      @ShanesCows  4 года назад

      That is one solution for sure David 😂

  • @mblherry
    @mblherry 4 года назад +1

    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
      @SwadDashcam 4 года назад

      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
      @paulkroon 4 года назад

      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
      @ShanesCows  4 года назад

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

  • @kumarb1261
    @kumarb1261 10 месяцев назад

    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!

  • @ashokpershad
    @ashokpershad 4 года назад +1

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

    • @ShanesCows
      @ShanesCows  4 года назад

      Correct. You cannot change them as they are auto generated

  • @mardithpascua8112
    @mardithpascua8112 2 года назад +1

    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
      @ShanesCows  2 года назад

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

  • @combatter
    @combatter 3 года назад

    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?

  • @stuartrobinson1658
    @stuartrobinson1658 3 года назад +1

    Very useful video Shane.

  • @sanjeevtiwari17
    @sanjeevtiwari17 4 года назад +1

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

    • @ShanesCows
      @ShanesCows  4 года назад

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

  • @chahineatallah2636
    @chahineatallah2636 Год назад +1

    Great video, do you have video about dataverse dates ?

    • @ShanesCows
      @ShanesCows  Год назад +1

      Not a lot but if you check this video ruclips.net/video/kxxEC1xH9sI/видео.html 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
      @chahineatallah2636 Год назад

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

  • @stayalive1017
    @stayalive1017 2 года назад +1

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

  • @louiscutmore
    @louiscutmore 3 года назад +1

    Complicated but love it - thanks!!

  • @Sustainable.149
    @Sustainable.149 2 года назад

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

  • @sadelina2763
    @sadelina2763 2 года назад +1

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

  • @R.SEntertainments
    @R.SEntertainments Год назад +1

    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
      @ShanesCows  Год назад

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

    • @R.SEntertainments
      @R.SEntertainments Год назад

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

  • @cmarcelacg
    @cmarcelacg 3 года назад

    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?

  • @alancoulter9688
    @alancoulter9688 3 года назад

    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
      @alancoulter9688 3 года назад

      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
      @alancoulter9688 3 года назад

      Note that I am working with Date Only fields

  • @dexterjamesignacio6879
    @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.

  • @alickcampbell8915
    @alickcampbell8915 3 года назад +1

    Thank you for the clarification

    • @ShanesCows
      @ShanesCows  3 года назад

      Happy to help. Have a great day. 🐶

  • @ClashwithAshishClashboy
    @ClashwithAshishClashboy Год назад +1

    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
      @ShanesCows  Год назад

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

  • @shubhamchavan7338
    @shubhamchavan7338 4 года назад +1

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

    • @ShanesCows
      @ShanesCows  4 года назад

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

    • @shubhamchavan7338
      @shubhamchavan7338 4 года назад

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

  • @sundrapillay6668
    @sundrapillay6668 4 года назад +1

    Thanks Shane great video

    • @ShanesCows
      @ShanesCows  4 года назад

      Glad you enjoyed it

    • @gajananchoudhary3914
      @gajananchoudhary3914 3 года назад

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

  • @senolibrahim
    @senolibrahim 3 года назад +1

    thank Shane :) thats the point

  • @gajananchoudhary3914
    @gajananchoudhary3914 3 года назад

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

  • @davidcarr7793
    @davidcarr7793 2 года назад

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

  • @sumaiyaissam1835
    @sumaiyaissam1835 3 года назад

    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 ?

  • @yashwantsuryawanshi9080
    @yashwantsuryawanshi9080 4 года назад +1

    Thank you Shane...

  • @CarFinanceSimplified
    @CarFinanceSimplified 10 месяцев назад

    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!!!!

  • @leetyler3102
    @leetyler3102 4 года назад +1

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

    • @ShanesCows
      @ShanesCows  4 года назад

      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.

  • @sushantpenshanwar8645
    @sushantpenshanwar8645 4 года назад +1

    Super Thanks !!

  • @landermatos7905
    @landermatos7905 3 года назад +1

    valuable tips

  • @stanleym69
    @stanleym69 4 года назад +1

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

    • @ShanesCows
      @ShanesCows  4 года назад +1

      It is tomorrow. Did you watch it? 😎

    • @stanleym69
      @stanleym69 4 года назад

      My head hurts now

  • @maorifulla
    @maorifulla 2 года назад +1

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

    • @ShanesCows
      @ShanesCows  2 года назад

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

  • @VANDIK01
    @VANDIK01 2 года назад

    make sense, thanks @ShaneYoungCloud 👋👋👍👍