PowerApps Date and Time Functions

in today's show we talk about power apps date functions and the time functions to write what we're gonna try and do is just break down all the different date and time functions way to work with them talk a little bit about UTC and just make sure that we understand kind of how to change those data objects how to get them how to change them from text into actual date and sometimes formatting them just saw the little fun things you need in your life because who doesn't need another date but first here's our intro hi my name is Shane young with power apps 9 1 1 those guys and today's show we go the power up state function and it's not in functions and just overall what kind of date and time stuff that you need to do to be better at power apps right we all kind of get how to work with it beginning but you find as you go more and more down the rabbit hole your bosses I can't make the app do this or that your I wish I just knew like one more thing about dates or times well this is that video I've had a lot of questions lately from customers and consultants around you know how do I do this or that so hopefully this video just kind of walk you through all of it you know if you're super star power apps probably already know all this but that's alright sometimes a good refresher doesn't hurt either so with all that let's just switch over to my desktop and take a look and so over here my desktop just give me a preview what we're gonna work through right we're going to talk about now function the today function the time zone offset and just UTC time talking when we drop that how about how now works with variables versus on the screen and then we're gonna get into things like adding time to date getting the differences between two dates and then how to turn you know build yourself a little input like this and actually get to date so nothing too complicated but I thought it's good a little fun set of lessons so let's just start up here with now right if you haven't seen it before the now function it returns the current date and time and so by default power-ups goes ahead and formats this out for you but in reality the now function is getting even down to the seconds as well right it's doing more than showing you also notice right so right now I'm hovering over it's like 3:05 but it shows 241 so anytime you put now like in a label whatever it is when that label got rendered it's going to stick with us there's no way to like refresh the timestamp on the screen so that kind of annoys people so one things you might see out here though is right now I've got a code here right so set var now create a variable called var now and we're going to store the now function in it the reason I do this is because if I click on the set now variable it goes and fetches the time right this minute and so if I click on the variable and you know two minutes from now it'd be 307 right through you know you get that but the reason I point this out is because a lot of times we want to date time stamp things like one of you one of the user edit this record right when did they clock in when did they clock out when did this thing happen and in those scenarios right well the fact that now didn't refresh the screen that's a problem you're right but when you use now again in a function look I talk for a minute good job me boy use now in a function then what happens is it goes and fetches it so if you use now inside of like your patch statements which is very common right now I want to patch the time that the user did this thing if you're using now there or using it here where I'm setting in a variable it'll always get the most it'll get right now but if you stick a label on the screen then the first time that available gets rendered it's going to kind of lock that in and even if I leave the screen right it says 241 so if I go to a different screen and I go back it's still gonna stay 241 so dowing labels only refreshes once but now inside of a formula it does all the time okay secondly there's a function call today so if you just need the date then that will return today and once again power naps is kind of smart it returns it as a date/time object but it only returns you to date and so if you hover here you're gonna see well actually what it is is it is 124 20/20 at 12 a.m. it sets today to midnight at that particular spot so every now and then that causes a little bit of confusion for people because they're like well why didn't wanted to pick midnight well when you just pass a date they get stored as a date time and so it just uses the midnight or assumes the midnight is the the time you needed so sometimes like I'm working with my buddy Jim right now it's kind of who made me think about making this video on some stuff around time off request and calculating time dips and things like that and we had the kind of account for you know midnight is real if we're working with just dates midnight is what it looks at versus you know if you say I want to take off tomorrow from lunch from 12:00 to 1:00 that's a little bit different so just think about that when you're looking at these but today returns to today now the most common challenge that people have when they start getting the times I mean I literally get this call probably once every at least twice a week someone will say hey I'm writing my times to somewhere and the time is off right there but yeah why is it my time work well what really happens is underneath the covers power apps in most data sources they talk in UTC time right so if I patch to my SharePoint list now it is actually going to save into SharePoint the UTC time all right so it's going to say all right well right now it is three o'clock in the Eastern time zone which is 8 o'clock in UTC so it's going to store over in SharePoint 8 p.m. right because that is the UTC time now in SharePoint when I go look at the time over there though what a SharePoint going to show me well if SharePoint is going to show me the time based on the regional time zone or the regional time setting of that SharePoint site so as long as that site is set into eastern then it would go it would show me that I did now at 3 o'clock just like I would expect so this problem of time zones if you get into Kissin areas where you're like hey all my times are off by three hours or five hours or you know some static hour number it is almost always a time zone issue and so what I'm gonna do is I'm gonna make a whole nother video where we're gonna dive into and understand that deeper but since we were talking about date and time today I want to kind of start planting that seed with you guys that you know UTC is usually the most common reason for time confusion the same type of thing can be held true remember we talked about this date right so if I save this date to a date/time field it's gonna get saved as one 24 12 a.m. or right it's actually gonna get set you know the time zones can affect that so sometimes I get people especially at my Eastern time zone people they're like hey all my dates are off by a day well they're not actually off by day what happened was you had a timezone issue and it's off by 5 hours which if you start at midnight five hours is the previous day so it looks like it's off by a whole day so anyway we're gonna do a whole video and then a different point but what I want you guys to know in this context of this video is there is a function called the time zone offset and so that will tell you how far the current user right so me logged in my browser here how far I am off and so it is always going to be in minutes so I am 300 minutes which is you know 300 divided by 65 hours and I'm five hours off UTC so there is a function for figuring out how far a person is away from time zone or from UTC this will also come up in another video so sharon recommended hey I want you to a video on calendars and setting calendar appointments and things that's a good idea shared and so when we do that we're gonna have to talk a little about time zones as well and how I kind of work around that the other thing I want to remind you guys is that we can format time and so we do that with the text function so I can say take now and then use the text function to show me now in UTC format right and you notice there that it's like Note 8 2001 124 at 1941 and forty six point six five seven Zulu right I mean it gets really complicated that is the full UTC of when this particular label got rendered remembered now got changed so if I say let's say let's do let's even get now to refire we'll do today so that's UTC of today right 124 and five hours right because today midnight is what now returned or what today returned and so it jumped it ahead five hours so then now hopefully if we put now in here now will read fire and so then 124 at 2011 yes that is 8 8 11 so that is a UTC time of right now so perfect there but I want you guys to know that there are a whole bunch of functions here so using the text function now remember though that this is that this is then changing the date and the time to be a string it is no longer to date time it is a string it just happens to be formatted but when you pass it you can see there's a bunch of optional parameter side Beck hey show me the long date time in 24 and so then now I would see you know Friday January 24 20 2013 12 28 all because that is 24-hour time so a lot of fun you can have here a time format and we want to do UTC that's what I had in the code but the text function is how you manipulate what the time shows back and if these little pre-canned ones don't work for you so my European friends right we us Americans we like our dates in mmm while mmddyy why right you like DD mmm yyy so what you can do is come in here and go DD / mmm / why why why why and so then now you can see that it is 24 of January of 2020 right so I was able to switch the date format around if I'm like oh you know what I want to show the month name here no problem all right three three M's is the short month name for Iams is long month name for the day I think there's some weirdness here - all right so he changed out 3ds is the date so it's Friday taste Friday congratulations for DS is Friday spelled out so lots of fun to be had with this and if you're like whoa how do you know all this why are you doing all this so fast the reason for that is because you don't have to remember any of this right you go to power apps formula reference my favorite piece of power apps documentation go right here and so then if we go find our friend or just duties over here the text function and so in here it explains all those pre-canned formats yay it also explains how to do like what the 1 and the 2 M 3 and the forums all mean so pretty nice of them so great little piece I'll put the link down below but if you guys don't come to this page at least at least once a week we're probably not friends right this is what me my friends doing Friday night says we find new ways to read the formula reference for power apps them I'm waiting on a version and Klingon or something cool like that that's not true but but anyway I think that you know this is a really great page and when you find a function like text it breaks it down for you pretty well okay so that's formatting date and time a little bit about how to figure out the offset we talked about the variable so the now let's go down here and so if we hit play just to make this easier so what I did here is I said all right whatever is in this date picker so we're gonna say so it's 124 I put a 5 here it's going to spit out 129 you know so today plus 5 and so the way that we do that is there is a function OOP where's my function it's right here it's called the date add function and so the date add function the way that it works is you give it a date in so in this case I'm saying my date picker the selected date and then I'm going to say here's the number of units so in my case I want to take this 5 in some sense it's a text 5 I need to turn it into a number 5 so I use the value function there and then I want here in the end you've got different options and so you can do days hours milliseconds minutes months quarter seconds years right all types of fun stuff that you can add to it but so this is the proper way if you have a date or a time and you want to add to it so that is how we add days to it all right and if we that gives us some different flexibility where I use this a lot is like one of my customers I have to decide whether or not today is a holiday and we're not the they want something delivered as a holiday in sorry and apparently use date add and then date subtractions and things like that to figure out what is today plus 5 like what is the actual date of today plus 5 so a lot of that type of stuff that happens in my life and so date add is the proper way to add it sometimes power apps about you'd be lazy and literally just say I take the date and plus the number five and it'll figure it out but don't do that type of stuff because you're just counting on power apps knowing what you mean right when you use the date add function you know that it's going to know what you mean now what if you want to subtract two dates that's a fair question so down here I did that and so if we change this one to be thirty first so you can see the difference between these two date pickers I think I did that in hours yeah so date diff so you provided a date he provided another date and then the same as before we can say I want you to give me the difference between these two dates in hours minutes seconds days whatever it is that makes sense for you but this is a great way for you to do the math right so in this case I said what's the number of hours he'll change us today so it's easier to read now notice the difference between date pickers is seven now if you go and that's because it's seven days right the way I wrote my thing but if I end up saying oh what if I put the from dates you know back at the 17th right so backwards so then and now it's even a negative seven so power apps understands what you're doing and it's based you're all you're doing is saying hey subtract the date from right which is the top one and then subtract this one and so that's where we got a negative seven there you can do that for I'm showing dates because dates are super easy to demo but you can do that with x as well and that is how you would figure out when you're trying very okay you know start a vacation into vacation how many days was that person want to take off there you go that's the type of thing you use is the date diff function okay move it right along over here I made a nice little input right because no invariably nobody wants users and most users aren't smart enough quite frankly to type in dates or times in a format that's really easy for us to like package up and turn into an actual date and time so what you want to do is we won't let the users in or a bunch of strings so in this case there we go yeah seven on 28 and then I just threw three dates in here right 2021 and so then that spits out July 28 21 over here is a date value and so the way that I do that the date value function takes strings or takes a string one string and turns it into a date and so what I've done here is I just wrote out a nice little fun string that spits out seven twenty eight twenty one because that's the format value expects and so you see drop-down month selected value that's the seven and then I concatenated a forward slash and then I concatenated the text input from right here at the 28th I can catenate that and then the drop-down for the Year selected you'll notice here in the 28th I did go ahead and change the text input only about numbers to go in here so that way people couldn't do it and if I was really writing this for a customer I would also then go write some logic to make sure that they weren't typing in like this is the 60th row ever what's interesting though watch what happens if I say 760 well power apps just says okay whoa July had 31 days and so you had 29 more days so then increment the month power out to actually figure it out what I meant but that's a really weird date so just don't let your users do weird things like that the other thing a lot of times you might do is just change this to be an actual drop-down of things so if you don't trust your users to type in numbers at all it happens but the date value function is great the other thing you can do the date value function right if we blow this out of here is I could just be like hey 1225 2019 close that out and so it figured out right so if you want to just be able to type in a string like just for because you're like hey I want to set this thing to always remind me on Christmas and you didn't want that's the best the way that you take the numbers or the the value of the string and turn it into an actual date value all right pretty fun that it knows the difference between the dots and all that so and then also remember you could pop these together so right now we that's turning this into an actual date yey what if I wanted to show that in a different format all right we just throw the text function around it text and then we would go out here and we'd say hey I want you to show me that in long date format and like that now it says Wednesday December 25th there makes it seem reader Wednesday December 25th because that's the long date format so you can always stack these things right I use this first function to turn words into an actual date and the text because the text function needed an actual dates to operate against and so then we went and did our so pretty cool they're down here below the time value function I did the same exact thing so just you know drop down our selected in my little picker here you know so right I have hours 1 to 12 so 10 and then just because I'm lazy I made it 15 minute increments you could put everything in here you could make it an input whatever you want but so 10:30 p.m. and so you can see over here it spit out a time value of 10:30 p.m. and I just did the same thing I just took and made that big old string right it's one concatenated string and so then power ups at oh I can figure out how to change that string into a time value and so now it's an actual time object and it's important because I want to do time calculations you can't do time calculations on a string you got to get it into time value so you can do things like you know date diff and things like that to calculate the difference between two times down here this is just the long version I will not explain it again but this is the date time value function so if you need to take a string that was both date and time that is an example of what that would look like pretty straightforward I think now the other thing you might have noticed you've been reading your power apps documentation is there is also a duty to do there is also a date function right so you're like what stairs between date and date time that's a great question so I'm supposed to be your answer date takes values and you have to put in numbers right so here on the back alright so 2012 comma 7 comma 11 7 11 and then that turns into July 11 2012 right and then once again now I'll give me a text function around that boom and then what do I got here I'm going to say let's do it as mmm slash enough space DD hope but I when you're doing these formulas capital M's versus lowercase M's make a difference so so now it says July 11 2012 probably do a little comma right there and so then you're right that spits that out but so what's key here though sorry I got sidetracked a little bit of myself there with the date function and there's also a time function they both require numbers whereas the date value and the time value took strings so it took me like a minute to wrap my head around it so I thought it was worth mentioning to you guys as well right but yeah time same thing so 12 and then what is the minutes 33 was the second one was the milliseconds oh yeah there is oh nine milliseconds boop and so then that spits out 12:33 p.m. and so then but if I wanted to see the whole thing right what would I have to do it have to be like text use the text function a lot in my life and then there is a time format of a long time a long time right there there you go and so then now it says 12 33 and a 1 so very very fun cool okay so those are the different functions that I want to make sure you guys understood how to use now as a little bonus real quick what I thought I would show you so over here you know you kind of got your head hopefully wrapped around these different functions and how you're going to use them over here I wanted to show you my fancy screen my buddy Daniel LeMay he we were talking about this earlier I he's one of our consultants here he gets Nerds out way too fast he made me a component where I can go in here and enter this stuff and it spits it out just like that right boob go here first and so the way that he did that all right I mean it literally is just a component let's go here right and so this label was set up to the output of that component if you don't have the components then you can see that he just built out all these things that we did and then he said all right spit out full date time as an output property and so he did that and if you go over here full date time and then there's the formula so he used the everything that I just taught you he just put it in a component so in his apps now instead of having to rewrite that every time he just drops in that component and now he's got a date time picker he's already got it formatted I mean it looks like looks really bad right now but you know he made it in 32 seconds so I won't judge but anyway he pulled all that together so that he has a reusable component for letting people in or select the date and the time and then it just spits out over here right right here it spits out and there's a date/time object so I could write that over to my data source I could do date math type of things on I could using my text function it doesn't matter so that'd be one of those things I'd love to hear about you guys doing right leave me a comment when you do a blow but take one of these concepts and build yourself a reusable component right maybe you have to calculate the difference between people's clock out and clock in times all-time or weird things like that or you want people to put in two different times and tell them how much overtime they didn't pay if they worked between those two times whatever make something cool make it a component so it's reusable so then that way you kind of always have the same entry experience kind of like that so as always right I just remind you guys real quick if you are a subscriber this app is available for downloads you can go grab it and see you don't have to copy all these formulas you can just grab the app and you've got all the formula that all works you can do your thing I don't even leave Daniels component in there don't tell him he'll want royalties or something like that but all that's available in training that power apps nine-one-one calm um it's always an easy thing you know how to get ahold of me leave me a comment below you can email me Shane at power apps 9-1-1 you tip the the power apps nine one one website i'm available on here so hopefully you found this useful and all that I was supposed to say thanks before you go be sure to click on the subscribe button over here that way be notified when new videos come out if you need any help or you all work together whether your problems big or small check us out at power apps 9-1-1 we do it all around or if you're looking for more formal training offerings we have those that lean to appear somewhere so check them out thanks and have a great day

Channel: Shane Young

Views: 56,308

Rating: 4.9481864 out of 5

Keywords: Shane Young, Bold Zebras, powerapps911, powerapps datepicker, powerapps date functions, powerapps date and time picker, powerapps date time format, powerapps datediff, powerapps date format, powerapps date time picker, powerapps date time stamp, powerapps time picker, powerapps time clock, powerapps dateimevalue, datevalue, timevalue, powerapps, datediff, timezoneoffset, powerapps timezone, powerapps utc, dateadd

Id: EbYMN4ouOvQ

Length: 24min 27sec (1467 seconds)

Published: Fri Jan 24 2020