This is fantastic -thank you Shane! It is difficult to find the 24-hour explanations and demos, but you nailed it. You saved me countless hours. I found this in your training class too - great work and highly recommended. Thank you!
Hello Shane. U r so pedagogue, the more i see ur videos, the more i learn & i add them on my private list. As i said in another video, u help me a lot with ur very didactic demonstrations & explanations. Thanks a lot from Paris (France) !
Hey Shane, i am doing an internship in a big company and you saved my last two weeks! Thank you so much! It feels like we are almost friends :D nice that you also explain with this video exactly what I have a problem with... again :D Greetings from Berlin
Thank you, Shane for this "timely" video. I am having trouble taking a time and sending it to Excel without the data component. Say it's 5:42PM. I have a button where OnSelect is set(varArrive, Now()). I display this in TextInputArrive1 as Text(varArrive, ShortTime24) and it shows 17:42. The Patch says Patch(ShuttleTrack, Defaults(ShuttleTrack), {Arrive:TimeValue(TextInputArrive1.Text&"Z"}). The Arrive column in Excel shows "1/1/1970 18:42". So it is offset by an hour and also, that "1/1/1970" date is present. I have tried many many times to format the Patch command to strip off the date and send just "17:42" to the Excel file but nothing seems to be working.
Thank you Shane, I have a question about the date placeholder. I managed to create the date box but the placeholder date shows 1/1/2001. Would I be able to use Today's date for the placeholder?
Great Videos Shane. Thank you. Is there a way to allow a user to type in a time instead of picking from 2 drop boxes? example user would type 1800 or 1805 in a box and it would change it to 18:0018:05 and still recognize it as time?
Yes. You would need to use Left function and right function to get the pieces and then put a colon in the middle. Then wrap that string in a DateTimeValue function. 😎
Nice Thanks for posting. Looking forward to your upcoming video on time-zones etc. My first attempt at using PowerApps got mired in time/date problems for which I couldn't find a decent solution (I'm in EST in Canada). I gave up. Hoping your insights will be what I need to give PA another try.
Hi Shane... I have a gallery with start dates and times and end date and time which is used for tracking the total hours (adding up number of people x elapsed time etc). The problem is whenever I have to edit the record to for example add a name to the list, the time resets to 00:00. The date itself seems to stay put, but the hours/minutes reset. Any ideas how to get the edit form to leave the datetime information alone?
So I have a SharePoint list with a Start and Stop date and time picker that is set on SharePoint to default to Today() but the Powerapp I still have to pick the date and time manually is there a way to set the default value to Now() when they create new entry?
Shane, I am having a horrible time trying to calculate time difference. Could you assist or point me in the right direction ... please? I inserted a datepicker, and can calculate the difference in days. The Hours, Minutes, Seconds fields are dropdowns options. I am having trouble calculating the time when the end date is the next day or multple days out. If hours are greater than 24, I need to add 1 to days. If minutes are greater than 60, I need to add 1 to hours, etc, etc. I hope I have provided a brief and clear ask. Please and thank you. I love all of the videos you have published. They have taught me a lot
Shane Young would it be possible to bring pin input to SharePoint list? So if a new item on the list is created it can be signed from the pen input and that signature added to a document?
how in the world did you make the entire box clickable for the date? I can only bring up the calendar if I click the little calendar icon toward the right.
thank you so much for the videos. I have a question in this regard, is it possible to change the calendar layout? what I mean, changing the calender to be instead of selecting the date and clicking OK, it takes some time and I would like to change this to be without clicking OK after selecting the date. Is it possible?
I am new to building apps. Is it possible to use PowerApps from a "personal" account and NOT my work account? When I go to sign up it prompts for a "work" email address. I don't want to build the app from my "work" account. At work I user PowerBI Desktop and therefore the reason I do happen to have an account under my work email addy. Please help me figure out how I can get started building my "personal" app. Thanks so much!!!
Dear Shane, I am tuck in very strange error while using datepicker, i have 2 containers on screen,i am using datepicker in right container. when i run app, and click on datepicker. it expands the datepicker in top left corner of screen.. i need your help on this
Hi Shane, Date value is getting updated along with time in excel data source and that makes some other issue with calculation. I want only date not with time. Is there any possibility to remove hours and minutes ? Note:¨ I have made visibility to false for hours and minutes field and set default value as 00, after submitting the form it takes 22:00 automatically and that leads to display selected date + 1 day in another field, not exact selected date.
Hi Shane, I have a form with a field selectDate when I leave the field in blank and submit the form...stay save the last date in a field the list....Sorry by my english...from Brazil
Hi Shane, Great vid as always. I have jumped the gun here in that I have watched this video without know how to create dropdowns. I am wanting to initially create a dropdown for times for an app connected to SQL Database. Can you point me in the right direction to view your vid on how to do this please as I cannot seem to find it? Many thanks
Hi Shane, I have a requirement to select the date in mmm/yyyy format and update the date in this format in the SharePoint list I couldn't achieve it could you help me figure this out?
Hey Shane, how can i convert a date value into string to be able to search a gallery by the date column of a sharepoint list by starting to type in the date in the search input box?
Is there a way to use these functions to filter/lookup between/over a range of dates, i need to check stock levels for new orders, so i have items booked out between 1st and 20th of Month and some that are booked from the 1st to the 10th of month , someone might put an order in that is from the booked from the 1st to the 9th of the month, i need to show that the items booked from the 3rd to the 10th aren't available, but also the ones booked from the 1st to 20th aren't available, if i use a greater than equals on the return items dates, it will only return items between the 1st and 9th range, it doesn't show anything after that, but then if i use a less than equals on the return item dates, it returns everything even if the booked out from date is after the new item date.
@@ShanesCows Hi Shane, I've tried using this filter but it doesn't take into account dates over the ranges so if a start date of one booking is before the end date of another, and then the end date of the 1st booking is after the end date of another, it won't show as a filtered Item as the CheckInDate is out of range, any ideas?
How would you trap for inappropriate inputs In date fields? Also, ms access has masking features to assure correct input for date/ time values. How would we do this in power apps?
Great video as usual. In my end, Date time mismatched between PowerApps and SharePoint. In PowerApps my input is - [9/30/2020 20:10], in SharePoint it becomes [9/30/2020 7:40 AM], I'm using sharepoint form, all (outlook, sharepoint) regional settings are same (US 8:00). Even it's happening with Created and Modified fields. I've tried Flow Convert date time, but results are same. Any solution / workaround on this? Thank you
@@ShanesCows Yes, I think it's sharepoint date time column , Some how it's taking browser time zone (IST +5:30), All regional settings (outlook, sharepoint) set to US time zone. Now I'm using a text field to store date time from PowerApps, and using it further.
Hey Shane,is there any way to get the date from internet in power app as the Today() function gets the users local date, there is a format issue which reads the user date in format mm/dd/yyyy as dd/mm/yyyy ( eg. User's local date is 12-06-2021 i.e. 6th December 2021 Power app reads it as 12th June 2021)
I think I talk about it in this video. ruclips.net/video/gJMgOlDy34w/видео.html You can provide the Language() function so when you are converting the date it knows what language you are using. I think this will help you.
Hi!! Thank you for all these videos!! I do not manage to have only date and no hours within a date format to be able to use a datepicker then but i dont want to have the hours on my gallerys.. I saw that you have a "today" on your video without hours... Could you please help me on that? Thank you!!
Hi. I am trying to use datapicker for calculating services dates, but how I can exclude weekend, holidays, some specific weekdays and add fixed count days for production at the same time?
Very good. Great. One doubt: I have two dropdowns to make the time difference between them. They are populated like this: ["10:00"; "11:00"; "12:00"; "13:00"; ...]. I am not able to make the difference between them when "22:00" is selected and the other "02:00". The result would be 04 hours. But it's 8 pm. Would you help me? The formula I'm using is this: DateDiff (DateTimeValue (DataCardValue30.Selected.Value); DateTimeValue (DataCardValue31.Selected.Value); Hours)
Dear Shane, I was wondering about the calendar date you made. Very aesthetically done imo and I'll like to copy it. However, if I my Power App is based on data sources extracted from onedrive/sharepoint, etc, do I need to have a separate columns of three where one is for date, month and year or just one column with date, month and year? I think my first option might be viable as it's simpler and probably guarantee wouldn't leave a dent on my head. What's your take?
Hi Shane, I save the Timestamp using the saveData() function in phone's local storage but when i fetch this variable from collection using LoadData(), it displays nothing in the timestamp. can you please assist!!!
@@ShanesCows I have the same question. Text(DateTimeValue(Parent.Default), "[$-en-US]m/dd/yy") is my formula, works great, but returning the wrong date due to the UTC time zone factor. I'm in MST. What should the formula look like to add the time? I just want it to add the time. I don't have another box to tell it the time to add. I've played around with it, but can't quite get it right.
Good afternoon Shane....I am having issues with my date and time to where it changes when I am trying to edit anything. How do i code it to where the original date and time (date of entry) will not change no matter how many times it is edited. The original date and time entry is very important to our project. Thank you.
How can we validate the holidays taking into account that they are not business days and including weekends, if a deadline is established for x or y action
Hi Shane! Probably outside the scope of this video, but filtering and sorting with dates is not delegable with a lot of data sources, especially sharepoint and can cause all sorts of problems, do you know of effective ways to get round this. For example one way I have got round this is to create a flow that runs when an item is created that fills in a text column with the date in pure text format that I can then use to filter my galleries. Is there a better way? Also, your label at the start could be made to update using a variable and a timer, I have in the past made a timer that every 60 seconds updates my variable with now and the label gets updated from there!
Hi Shane, His videos are brilliant. Congratulations on the material! I have a doubt. How to insert a datetime in a date type field in the Oracle database? I have not found the correct function for this. Can you help me ?
I have two time fields in my form, how do I make sure the second is greater than the first? Its basically a time tracking app so a user would enter 8am as start time and 4pm as end time.
Hi shane .. thanks for the video.. Is there any way we can calculate Years of service , depends on joining date and End Day..Like 12 year 5 month 2 Days .. Thanks
Yes. To do it I use the Mod function. It is one of my favorite demos but not a quick answer. I will add it to my list of QTT video topics to cover. Thanks
Hello, i was wondering if the video on how to change the time zone is already submitted in the channel, because i haven't been able to find it. If someone has seen it, please could you reply me with the link? Thank you.
Hi Shane, I have a doubt In difference between the two data pickers, lets say i have selected Start date(30.04.2020) End Date(30.04.2020), output is coming 0, This should be calculate 1, as per my requirement, let me know your solution
Shane I live in the UK and am experiencing problems with Powerapps to Flow to Sharepoint List. My List has a date and this is formatted as UK dd/mm/yyyy in Sharepoint. In the PowerApp I have also formatted the Date . The Flow works about 50% of the time. The other 50% I get this error: String was not recognized as a valid DateTime. clientRequestId: 3b0768d1-f215-415d-bbce-e6798b1ceb15 serviceRequestId: 3b0768d1-f215-415d-bbce-e6798b1ceb15 How does it work for some and not for others? Any Ideas???
HI SHANE, GOOD DAY TO YOU I have used this Now() function , but i want to fix the date format as dd/mm/yyyy format , as now every time the date format is changing when other user uses the app. can you assist me please
Hi Shane, I have a Time Management Power apps that save the start and the end of my hour work in a SharePoint list. My problem is that in the SharePoint list some hour time are with AM/PM format, other no, why?
Thanks Shane for the video !! I have this silly doubt . Is there a way we can show the UTC date along with the time ? . Today() is showing only the date and making its time as "12:00 AM". But I want the exact UTC datetime , just like the functionality of now() . Looking forward for the suggestion :)
Hi Shane, thank you very much for your videos. I'm struggle with excluding weekends on DateDiff. I've searched the internet and the solution from Microsoft falls into negative if the difference between the two dates is more than a week. I'm using : RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 + Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)
I want users to enter past and present date/time retrospectively and set a restriction to stop them entering future date/time. Can anyone please advise on format/code required and where?
You can’t use this date picker to do that. But you can steal the date picker from the Dataverse for teams date picker to do so. I should do a video on that.
May have been asked but how do I get the hours and minutes in decimal format that may cross the midnight time in calculation over the 24 hours. I also need all my time sin 2400 clock. In SharePoint list calculation.
Hi I have a date picker for searching the data but the picker is not working for previous year. only picking the current year can you please guide me how to resolve this
Nice Video Shane, we got an error like ""Inner exception: we cannot apply operator < to types Date and DateTimeZone" when getting all records which is less than Today() date from SQL server by using date time filter. Can you help on this?
Hi Shane, Thank you very much for your insights on the power apps, I have one question though can we add a button on-click it will capture the now time to the SharePoint list?
Hi Shane, though I have selected "Local" in DateTimeZone of my app when it submits the form, the excel where it stores is saving in UTC (a few hours behind). How do I capture same date on my source file that was chosen in the app?
Power Apps will also save the time in UTC. You may want to just add hours to the value in the app when saving so the Excel gets the local time. But then you have to subtract those hours when displaying inside the app. UTC is a pain.
@@ShanesCows UTC is a godsend when you work in Aviation. DateAdd and TimeZoneOffset helps me a lot as total noob. Love your videos, you’re a really good teacher.
Hi, very interesting video, thank you. How do you make the Now Variable field empty until you press Set Now Variable? I have it covered in Default varNow. if I remove it, nothing works. Thanks
Hi Shane, I see that someone asked but could you please tell some more about how to calculate difference between two days excluding weekends and bank holidays? I have in Excel the list of bank holidays in several countries (app must recognise the country first, can take it from AD? )
Hallo, I've had a problem for a few days! the date and time come (Text (Now (); "[$ - fr] dd / mm / yy hh: mm")) automatically when I enter data now the date changes but the time remains the same.
Have you made the video all about time zones that you reference in this video? This is becoming an issue with the app I wrote. Worked great when it was just my group. Now attempting to share it with people in another office in another time zone and having difficulty wrapping my head around it showing the correct time all the time.
Hey Shane, Thank you for your insights into Time functions. I doubt if we can disable a button(save form) when a user doesn't select a date in custom form?
Hi, fantastic video. Quite a lot in there for my future use cases. For the moment I am wondering how do I get the view form to show the minute value in a stored date column. For example , Text(CalendarEventsGallery4_2.Selected.StartDate, "HH") gets the hour value but "nn", "MM", "NN" dont return the minute value from source for me Text(CalendarEventsGallery4_2.Selected.StartDate, "HH") Text(CalendarEventsGallery4_2.Selected.StartDate, "HH")
@@ShanesCows i have done this but unfortunately in the sharepoint column list it doesn"t work. (the column is in date time type) . It comes always with the same (wrong = 31/12/1969 16:00:00) value. please if you know how to solve this help me...hehe
Thanks, Shane. Good explanation & made better by preparing your demo in advance. Here's my take on why the date-time value of Today() is midnight. (This is FWIW, not very important, & contributed here because where else would I contribute this?) If PowerApps date-time values are handled in the same way as Excel, the date value is the integer portion and the time value is the decimal portion. So, today (01/25/20) at 9:00 AM = 43855.25. Microsoft chose to store date-only values in date-time format (i.e., integer & decimal), probably to standardize date-time elements across the Microsoft ecosystem & to enable date math. Given that decision, Microsoft had to decide which time (decimal value) to use for date-only values. It makes sense to me that MS would pick .00 as the decimal portion of date-only values. Today is 43855.00, tomorrow is 43856.00, etc. The time value of midnight (12:00 AM) is .00, so the storage of the date-time value of 43855.00 for the date-only assignment of 01/25/20 means that the date-time representation of that value is 01/25/20 12:00 AM.
Thank's Shane. It's very helpful. I'm working on powerapps and I've some issues with the date. I can't update the Excel field with the date from powerapps without the time...i just need to have the date...Do you have any solution? Thanks un advance for your help 😀
Hi Shane, How are you ? Great video. How do I add the hours to a list? Example: I have a list with 500 lines, how do I do all the times on the list? Time column 12:00 pm 4:00 pm 11:00 13:25
Hi Shane. Great Video. Thanks for sharing. I am getting some error when i try to save the form to sql server db and the update for one datacard is TimeValue(HourValue8_2.Selected.Value & ":" & MinuteValue8_2.Selected.Value ) and my db column datattype is time(7). and the error is "We can not convert the value 420000 to time."
@@ShanesCows can we connect on saifalikhan1301@gmail.com , I need to understand this work week logic, to auto populate values from monday to sunday on 7 date fields of a repeating table in gallery
Hi Shane, Great video as usual, Passing date into Flow as parameter throwing error for SharePoint date column. Code - Text(DataCardValue13.SelectedDate,"[$-en-US]mm/dd/yyyy","en-US"),---- Error - string 'mm/dd/yyyy/' does not validate against format date, Any thoughts on this..? In flow I've used formatdatetime. But same result. Appreciate your reply on this. Thank you.
might be an easy fix but, showing people that the time zone can be changed for SharePoint might be worthwhile, took me longer then you'd expect to find documentation on how to do it. then the premade formulas work perfectly. anyway, thanks for the video it clarified a lot.
Great stuff to know Shane; I have several questions... I'm reading a date value from a dataverse table and showing in a gallery. The value showing is one day prior to the date stored in the table so, I want to add 1 to the date. I tried different lines of code but none seem to work... DateAdd(ThisItem.'Date Value',1,ShortDate) - this returns nothing. I have to use Text(ThisItem.'Date Value',ShortDate). Please help if you can. Thanks.
Hi Shane, awesome video! I've got some trouble with uploading a date to Sharepoint via Flow. I get this error code: input body for trigger 'manual' of type 'Request' did not match its schema definition. error details: 'String date' does not validate against format 'date'.'." I've tried using Text(Today(), "dd-mm-yyyy"), as well as DateValue("date string"), used the DatePicker, and straight up just putting Today() and nothing's changing that error code. I've redone the Flow from scratch and reconnected it to PowerApps as well, and in Flow I've tried Initialising the input as a string and formatDateTime() in the Compose step.
Hei Shane, Thank you for nice video, I have tried to find the days difference between to date from two different gallery fields . Both fields are DateTime fields in datavarse entity DateDiff(Gal_vak.Selected.'Dato og tidspunkt', Gal_booking.Selected.'Innkalling tidspunkt', Days) I got "incorrect argument ,expected number error. " I have modified the formula DateDiff(DateValue(Text(Gal_vak.Selected.'Dato og tidspunkt',DateTimeFormat.ShortDate)), DateValue(Text(Gal_booking.Selected.'Innkalling tidspunkt',DateTimeFormat.ShortDate)),Days ) then i got "The argument to DateValue function do not represent a valid date or time " can you help me to solve this error
Shane, Awesome PowerApps training and Help. Great vid's. I have a Calculated date field in a list and this shows as UTC in my app, and the Text format technique doesn't change it. Any Ideas would be appreciated.
How do you collect a date or date/time in a collection so you can patch it later? I'm looking for a summary of how to declare variables of the various types. (TextVar: "", NumVar:0, BooleanVar:true, etc) Thanks!
Thanks, Shane!! Great video, as always quality stuff. I've a question regarding the DatePicker control. Is there any way a user can select a date without hitting the "OK" button?
Hi Shane, thanks and great video. Do you know how to set the function to make sure that the exit date is greater than enter date? For example, If an activity starts on 20/11/2021 and it ends on 15/11/2021 the system gives an error message or just does not display any earlier date than start date.
You could do something like have a label (or make your save button greyed out) depending on the result of an If Then ... If DatePicker2 < DatePicker1 then show error
Hi Shane, been binging your amazing videos for the past week, and its so amazing how much you are able to teach through your videos and samples. My question is sort of to parts. Firstly, would you be able to give an example of how to create a week number drop-down/picker? and 2nd would you at some point make a video about that TimeSheet app you made. I am looking to make something very similar, and could use some insights (to steal ;) ) Thank you and keep up the great work!
Thank you for posting this vidos! I have a question I have tried to add a date from a date picker to sharepoint from power apps using power automate. Using formula: Set(StartDate, Text(DatePickerCanvasStartDate.Value,DateTimeFormat.LongDateTime24)) but its telling me its not in the rigth format... I have tried many different formulas. Innluding Set(StartDate,DatePickerCanvasStartDate.Value). No luck so far. Can someone help me out?
This is fantastic -thank you Shane! It is difficult to find the 24-hour explanations and demos, but you nailed it. You saved me countless hours. I found this in your training class too - great work and highly recommended. Thank you!
You're very welcome! Time is such a hard concept, maybe the hardest when add time zones. 😳
Hello Shane. U r so pedagogue, the more i see ur videos, the more i learn & i add them on my private list. As i said in another video, u help me a lot with ur very didactic demonstrations & explanations. Thanks a lot from Paris (France) !
Hey Shane,
i am doing an internship in a big company and you saved my last two weeks! Thank you so much! It feels like we are almost friends :D
nice that you also explain with this video exactly what I have a problem with...
again :D
Greetings from Berlin
Greeting friend 😎
THANK YOU! you are a genius, your video help me in my job, God bless you!
Greetings from Nicaragua
Thanks 🙏 Happy to help
Shane, you never disappoint, such a wonderful video loved it.
Thanks Shane, that was more interesting than I was expecting.
😊 I am glad. Love the avatar
Thank you, Shane for this "timely" video. I am having trouble taking a time and sending it to Excel without the data component. Say it's 5:42PM. I have a button where OnSelect is set(varArrive, Now()). I display this in TextInputArrive1 as Text(varArrive, ShortTime24) and it shows 17:42. The Patch says Patch(ShuttleTrack, Defaults(ShuttleTrack), {Arrive:TimeValue(TextInputArrive1.Text&"Z"}). The Arrive column in Excel shows "1/1/1970 18:42". So it is offset by an hour and also, that "1/1/1970" date is present. I have tried many many times to format the Patch command to strip off the date and send just "17:42" to the Excel file but nothing seems to be working.
Set the Excel field to be a text field instead of date time. 😅
@@ShanesCows please can you make video for this as mostly users are new to powerapps
A great video - really helped me understand date/time functions.
Awesome 🤩
Thank you for the video! I was able to set the due date based on user's request :) hoping for more tutorials!!! Stay safe and take care!!! :)
I have almost 100 power apps videos so enjoy.
Shane is there anyway to show in a label the weekday (eg: MONDAY) based on the date selected in a date picker???
Yes, there is a Weekday function. :) I have a video that shows it but I don't remember which one. 😐
Thank you Shane,
I have a question about the date placeholder.
I managed to create the date box but the placeholder date shows 1/1/2001. Would I be able to use Today's date for the placeholder?
Yes. Use the Today() function for placeholder.
Great Videos Shane. Thank you. Is there a way to allow a user to type in a time instead of picking from 2 drop boxes? example user would type 1800 or 1805 in a box and it would change it to 18:00 18:05 and still recognize it as time?
Yes. You would need to use Left function and right function to get the pieces and then put a colon in the middle. Then wrap that string in a DateTimeValue function. 😎
Nice Thanks for posting. Looking forward to your upcoming video on time-zones etc. My first attempt at using PowerApps got mired in time/date problems for which I couldn't find a decent solution (I'm in EST in Canada). I gave up. Hoping your insights will be what I need to give PA another try.
Me too Gord. PowerApps is awesome so we need to get you working
Great video looking forward to detailed video about date time in excel vs PA as you mentioned in video.
Excel is such a pain in the butt. Feel free to email me if you have a specific question and I will try to point you in the right direction.
Hi Shane. I'm struggling trying to sum two times, like 10:00 plus half hour (00:30) and there's no easy way to do, isn't it?
DateAdd(time, 30, minutes) should do it.
Hi Shane... I have a gallery with start dates and times and end date and time which is used for tracking the total hours (adding up number of people x elapsed time etc). The problem is whenever I have to edit the record to for example add a name to the list, the time resets to 00:00. The date itself seems to stay put, but the hours/minutes reset. Any ideas how to get the edit form to leave the datetime information alone?
Tony it sounds like you have the Default values for the time dropdowns wrong or not set.
So I have a SharePoint list with a Start and Stop date and time picker that is set on SharePoint to default to Today() but the Powerapp I still have to pick the date and time manually is there a way to set the default value to Now() when they create new entry?
If(EditForm1.Mode =New, Today(), Parent.Default) Found my solution!!
Aweseome! Glad you got it!
Shane, I am having a horrible time trying to calculate time difference. Could you assist or point me in the right direction ... please?
I inserted a datepicker, and can calculate the difference in days. The Hours, Minutes, Seconds fields are dropdowns options. I am having trouble calculating the time when the end date is the next day or multple days out.
If hours are greater than 24, I need to add 1 to days. If minutes are greater than 60, I need to add 1 to hours, etc, etc.
I hope I have provided a brief and clear ask.
Please and thank you. I love all of the videos you have published. They have taught me a lot
Use DateTimeValue to turn all of your fields into a true datetime. Then use DateDiff for your math.
Is it possible to add script to powerapps? Say script for stitches and save the outcome to SharePoint
Nope sorry
Shane Young thanks Shane :)
Shane Young would it be possible to bring pin input to SharePoint list? So if a new item on the list is created it can be signed from the pen input and that signature added to a document?
how in the world did you make the entire box clickable for the date? I can only bring up the calendar if I click the little calendar icon toward the right.
If you set IsEditable to true then you can only click on the right, if it is false you can click the whole control :)
Thank you Shane, You explain very well, it has been a great help, Keep it up :)
Thanks 😊
Hi Shane, thanks for the video , but the whole video on date saving 5 hours late , was it made?
Helpful as usual, thank you Shane!
My pleasure!
thank you so much for the videos. I have a question in this regard, is it possible to change the calendar layout? what I mean, changing the calender to be instead of selecting the date and clicking OK, it takes some time and I would like to change this to be without clicking OK after selecting the date. Is it possible?
Unfortunately not
thank you for this video. I am creating a timesheet but would like to minus a lunch break off the total hours. Is there any way to do this?
I am new to building apps. Is it possible to use PowerApps from a "personal" account and NOT my work account? When I go to sign up it prompts for a "work" email address. I don't want to build the app from my "work" account. At work I user PowerBI Desktop and therefore the reason I do happen to have an account under my work email addy. Please help me figure out how I can get started building my "personal" app. Thanks so much!!!
have you tried this? powerapps.microsoft.com/en-us/communityplan/
Dear Shane,
I am tuck in very strange error while using datepicker,
i have 2 containers on screen,i am using datepicker in right container.
when i run app, and click on datepicker. it expands the datepicker in top left corner of screen..
i need your help on this
Not sure on that. Sounds like a bug but not sure.
Hi Shane, how can I use the DateAdd - UTC to convert the datetime to another Timezone other than Local and UTC.
I think you would need to use DateAdd and then number of hours. You could put them in a collection or something. Nothing magical htat I know of.
Hi Shane,
Date value is getting updated along with time in excel data source and that makes some other issue with calculation. I want only date not with time. Is there any possibility to remove hours and minutes ?
Note:¨
I have made visibility to false for hours and minutes field and set default value as 00, after submitting the form it takes 22:00 automatically and that leads to display selected date + 1 day in another field, not exact selected date.
You need to look into UTC. That is why you are getting wrong day
Hi Shane, I have a form with a field selectDate when I leave the field in blank and submit the form...stay save the last date in a field the list....Sorry by my english...from Brazil
Check that the defaultdate is blank. That way when the form is reset the date is not set.
Hi Shane, Great vid as always. I have jumped the gun here in that I have watched this video without know how to create dropdowns. I am wanting to initially create a dropdown for times for an app connected to SQL Database. Can you point me in the right direction to view your vid on how to do this please as I cannot seem to find it? Many thanks
Hi Shane,
I have a requirement to select the date in mmm/yyyy format and update the date in this format in the SharePoint list I couldn't achieve it could you help me figure this out?
I haven’t done that one before. I would try the Text function for manipulating the format but not sure it does mmm
Hey Shane, how can i convert a date value into string to be able to search a gallery by the date column of a sharepoint list by starting to type in the date in the search input box?
There is a text function but that then makes your query not delegable 😔
Shane, I have an app that needs to get a Julian date for a shipping document. Have you figured out a way to grab a Julian date?
And never mind! I figured it out by using the DateDiff function: "Julian Date :" & DateDiff(DateValue("1/1/"&(Text(Today(),"[$-en-US]yy"))),Today()+1)
Glad you got it Patrick.
Is there a way to use these functions to filter/lookup between/over a range of dates, i need to check stock levels for new orders, so i have items booked out between 1st and 20th of Month and some that are booked from the 1st to the 10th of month , someone might put an order in that is from the booked from the 1st to the 9th of the month, i need to show that the items booked from the 3rd to the 10th aren't available, but also the ones booked from the 1st to 20th aren't available, if i use a greater than equals on the return items dates, it will only return items between the 1st and 9th range, it doesn't show anything after that, but then if i use a less than equals on the return item dates, it returns everything even if the booked out from date is after the new item date.
You can do And. Filter(datasource, CheckoutDate > yourdate And CheckInDate < yourdate) something like that.
@@ShanesCows Hi Shane, I've tried using this filter but it doesn't take into account dates over the ranges so if a start date of one booking is before the end date of another, and then the end date of the 1st booking is after the end date of another, it won't show as a filtered Item as the CheckInDate is out of range, any ideas?
How would you trap for inappropriate inputs In date fields? Also, ms access has masking features to assure correct input for date/ time values. How would we do this in power apps?
IsMatch ruclips.net/video/IvapIsBbM-U/видео.html
Great video as usual. In my end, Date time mismatched between PowerApps and SharePoint.
In PowerApps my input is - [9/30/2020 20:10], in SharePoint it becomes [9/30/2020 7:40 AM], I'm using sharepoint form, all (outlook, sharepoint) regional settings are same (US 8:00). Even it's happening with Created and Modified fields. I've tried Flow Convert date time, but results are same.
Any solution / workaround on this? Thank you
You have a time zone mismatch somewhere. Are you converting the time to your local time zone in flow?
@@ShanesCows Yes, I think it's sharepoint date time column , Some how it's taking browser time zone (IST +5:30), All regional settings (outlook, sharepoint) set to US time zone. Now I'm using a text field to store date time from PowerApps, and using it further.
Hey Shane,is there any way to get the date from internet in power app as the Today() function gets the users local date, there is a format issue which reads the user date in format mm/dd/yyyy as dd/mm/yyyy ( eg. User's local date is 12-06-2021 i.e. 6th December 2021 Power app reads it as 12th June 2021)
I think I talk about it in this video. ruclips.net/video/gJMgOlDy34w/видео.html You can provide the Language() function so when you are converting the date it knows what language you are using. I think this will help you.
Hi!! Thank you for all these videos!!
I do not manage to have only date and no hours within a date format to be able to use a datepicker then but i dont want to have the hours on my gallerys.. I saw that you have a "today" on your video without hours... Could you please help me on that? Thank you!!
Use the Text function to change the way the date is shown. Text(Now(), ShortDate) would only show the date even though it was a date time.
Hi. I am trying to use datapicker for calculating services dates, but how I can exclude weekend, holidays, some specific weekdays and add fixed count days for production at the same time?
You need to have a data source that marks every day as either a workday or not a workday. There is no way for Power Apps to know otherwise.
Very good. Great. One doubt: I have two dropdowns to make the time difference between them. They are populated like this: ["10:00"; "11:00"; "12:00"; "13:00"; ...]. I am not able to make the difference between them when "22:00" is selected and the other "02:00". The result would be 04 hours. But it's 8 pm. Would you help me? The formula I'm using is this: DateDiff (DateTimeValue (DataCardValue30.Selected.Value); DateTimeValue (DataCardValue31.Selected.Value); Hours)
Need to add the date to the fields so you can do the math correctly.
Dear Shane,
I was wondering about the calendar date you made. Very aesthetically done imo and I'll like to copy it. However, if I my Power App is based on data sources extracted from onedrive/sharepoint, etc, do I need to have a separate columns of three where one is for date, month and year or just one column with date, month and year? I think my first option might be viable as it's simpler and probably guarantee wouldn't leave a dent on my head. What's your take?
Either way works. If you have separate pieces you will need to use the DateTimeValue function to turn those 3 pieces into one DateTime object.
Hi Shane, I save the Timestamp using the saveData() function in phone's local storage but when i fetch this variable from collection using LoadData(), it displays nothing in the timestamp. can you please assist!!!
See if this helps ruclips.net/video/NwbqdA9j8Ek/видео.html
Hi, can you please explain how to use "Now" function inside "Patch" function? As you said at 03:05 duration.
Patch(SharePointList, Defaults(SharePointList), {DateTimeColumn: Now()}) would do it.
thanks shane, how can i have quarters or months in the dropdown for selection while using the current year
You would have to define them yourself. No automated way.
is there a way to enter a date and then dateadd to it with out it being from a date picker? not sure if I missed that.
Yes. You can’t let someone type in a date and then use them he DateValue function to turn the string into a date object. Then DateAdd
@@ShanesCows I have the same question. Text(DateTimeValue(Parent.Default), "[$-en-US]m/dd/yy") is my formula, works great, but returning the wrong date due to the UTC time zone factor. I'm in MST. What should the formula look like to add the time? I just want it to add the time. I don't have another box to tell it the time to add. I've played around with it, but can't quite get it right.
Good afternoon Shane....I am having issues with my date and time to where it changes when I am trying to edit anything. How do i code it to where the original date and time (date of entry) will not change no matter how many times it is edited. The original date and time entry is very important to our project. Thank you.
How can we validate the holidays taking into account that they are not business days and including weekends, if a deadline is established for x or y action
Hi Shane! Probably outside the scope of this video, but filtering and sorting with dates is not delegable with a lot of data sources, especially sharepoint and can cause all sorts of problems, do you know of effective ways to get round this. For example one way I have got round this is to create a flow that runs when an item is created that fills in a text column with the date in pure text format that I can then use to filter my galleries. Is there a better way? Also, your label at the start could be made to update using a variable and a timer, I have in the past made a timer that every 60 seconds updates my variable with now and the label gets updated from there!
Cool idea with the timer Rob. For delegation dates I too sometimes use them as strings instead. Removes a lot of the chaos.
Hi Shane,
His videos are brilliant. Congratulations on the material!
I have a doubt. How to insert a datetime in a date type field in the Oracle database? I have not found the correct function for this. Can you help me ?
I have never worked with Oracle and Power Apps. Sorry. Try putting Now() in the field.
@@ShanesCows DateTimeValue(Text(Now())) It's work ! ;-)
I have two time fields in my form, how do I make sure the second is greater than the first? Its basically a time tracking app so a user would enter 8am as start time and 4pm as end time.
Just use the > operator to compare.
Hi shane .. thanks for the video.. Is there any way we can calculate Years of service , depends on joining date and End Day..Like
12 year 5 month 2 Days .. Thanks
Yes. To do it I use the Mod function. It is one of my favorite demos but not a quick answer. I will add it to my list of QTT video topics to cover. Thanks
Hello, i was wondering if the video on how to change the time zone is already submitted in the channel, because i haven't been able to find it. If someone has seen it, please could you reply me with the link? Thank you.
Sorry still haven’t done a video on UTC chaos. 😑
@@ShanesCows oh, so sorry to read that, i was really needing it, hope to see it soon. Thank you!
Hi Shane, I have a doubt In difference between the two data pickers, lets say i have selected Start date(30.04.2020) End Date(30.04.2020), output is coming 0, This should be calculate 1, as per my requirement, let me know your solution
Add 1 to the difference
Hi Shane. Like many others I've also got a question (sorry). But how can I simply show the current weeknumber in my App? Thanks. :)
I have never done it. I Binged it and it looks crazy complicated 😳
@@ShanesCows Yes haha. I ended up at your video's and thought let's ask. Thanks anyway. 👍🏻
How did you add the semicolon for the time?
I just added it as a string “:” and use the & to concatenate it in the middle.
Shane I live in the UK and am experiencing problems with Powerapps to Flow to Sharepoint List. My List has a date and this is formatted as UK dd/mm/yyyy in Sharepoint. In the PowerApp I have also formatted the Date . The Flow works about 50% of the time. The other 50% I get this error: String was not recognized as a valid DateTime.
clientRequestId: 3b0768d1-f215-415d-bbce-e6798b1ceb15
serviceRequestId: 3b0768d1-f215-415d-bbce-e6798b1ceb15
How does it work for some and not for others? Any Ideas???
Yikes. No, I have not ran into that. I am sure there is a valid reason though. Hmmmm
HI SHANE, GOOD DAY TO YOU
I have used this Now() function , but i want to fix the date format as dd/mm/yyyy format , as now every time the date format is changing when other user uses the app. can you assist me please
Use the Text function to define the format. Text(now(), “dd/mm/yyyy”)
Hi Shane, I have a Time Management Power apps that save the start and the end of my hour work in a SharePoint list. My problem is that in the SharePoint list some hour time are with AM/PM format, other no, why?
Thanks Shane for the video !! I have this silly doubt . Is there a way we can show the UTC date along with the time ? . Today() is showing only the date and making its time as "12:00 AM". But I want the exact UTC datetime , just like the functionality of now() . Looking forward for the suggestion :)
I think I show it in this video ruclips.net/video/dbsc-Mvh7Co/видео.html
Hi Shane, thank you very much for your videos. I'm struggle with excluding weekends on DateDiff. I've searched the internet and the solution from Microsoft falls into negative if the difference between the two dates is more than a week. I'm using : RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)
Not exactly sure. I will say when I use WeekDay I often use the optional parameter of , Monday. That way Saturday and Sunday are 6 and 7.
Love your videos dude, thanks for watching my video.
😀
I want users to enter past and present date/time retrospectively and set a restriction to stop them entering future date/time.
Can anyone please advise on format/code required and where?
You can’t use this date picker to do that. But you can steal the date picker from the Dataverse for teams date picker to do so. I should do a video on that.
May have been asked but how do I get the hours and minutes in decimal format that may cross the midnight time in calculation over the 24 hours. I also need all my time sin 2400 clock. In SharePoint list calculation.
Hi I have a date picker for searching the data but the picker is not working for previous year. only picking the current year can you please guide me how to resolve this
Nice Video Shane, we got an error like ""Inner exception: we cannot apply operator < to types Date and DateTimeZone" when getting all records which is less than Today() date from SQL server by using date time filter. Can you help on this?
I have no idea why. Sorry
Hi Shane, Thank you very much for your insights on the power apps, I have one question though can we add a button on-click it will capture the now time to the SharePoint list?
Yes use Patch
@@ShanesCows Thank you Shane
Hi Shane, though I have selected "Local" in DateTimeZone of my app when it submits the form, the excel where it stores is saving in UTC (a few hours behind). How do I capture same date on my source file that was chosen in the app?
Power Apps will also save the time in UTC. You may want to just add hours to the value in the app when saving so the Excel gets the local time. But then you have to subtract those hours when displaying inside the app. UTC is a pain.
@@ShanesCows UTC is a godsend when you work in Aviation. DateAdd and TimeZoneOffset helps me a lot as total noob. Love your videos, you’re a really good teacher.
Hi, very interesting video, thank you.
How do you make the Now Variable field empty until you press Set Now Variable? I have it covered in Default varNow. if I remove it, nothing works. Thanks
Hi Shane, I see that someone asked but could you please tell some more about how to calculate difference between two days excluding weekends and bank holidays? I have in Excel the list of bank holidays in several countries (app must recognise the country first, can take it from AD? )
You have to create the logic to count days between dates and then remove the weekends and holidays
Hallo, I've had a problem for a few days! the date and time come (Text (Now (); "[$ - fr] dd / mm / yy hh: mm")) automatically when I enter data now the date changes but the time remains the same.
Hmm I am not sure. That should work
Have you made the video all about time zones that you reference in this video? This is becoming an issue with the app I wrote. Worked great when it was just my group. Now attempting to share it with people in another office in another time zone and having difficulty wrapping my head around it showing the correct time all the time.
Nope. Also on the list. The list is getting quite long 😅
Great video, Having real problems validating a date with a regular expression driving me mad do you know where I can find a good regular expression?
I don’t do much with regex Why are you using regex?
@@ShanesCows Just need to validate a date or birth and thought this would be the easiest way - obviously I was wrong :-)
@@ShanesCows Sorry realised where I was going wrong, sorry about the dumb question - still learning!
Hey Shane,
Thank you for your insights into Time functions.
I doubt if we can disable a button(save form) when a user doesn't select a date in custom form?
Look for my video on conditional formatting
Sensei, great video as usual. Thanks for your effort and the explanations. It's kinda PITA working with timezones...
Time zones are no fun. I can’t wait to try to tackle them. 😀
Hi, fantastic video. Quite a lot in there for my future use cases. For the moment I am wondering how do I get the view form to show the minute value in a stored date column. For example , Text(CalendarEventsGallery4_2.Selected.StartDate, "HH") gets the hour value but "nn", "MM", "NN" dont return the minute value from source for me Text(CalendarEventsGallery4_2.Selected.StartDate, "HH") Text(CalendarEventsGallery4_2.Selected.StartDate, "HH")
How can I update a column in a SharePoint list with only the time from the function "now". I've been trying and it's been really complicated
Use the Text function to turn Now() into a string of time.
@@ShanesCows i have done this but unfortunately in the sharepoint column list it doesn"t work. (the column is in date time type) . It comes always with the same (wrong = 31/12/1969 16:00:00) value. please if you know how to solve this help me...hehe
Thanks, Shane. Good explanation & made better by preparing your demo in advance.
Here's my take on why the date-time value of Today() is midnight. (This is FWIW, not very important, & contributed here because where else would I contribute this?) If PowerApps date-time values are handled in the same way as Excel, the date value is the integer portion and the time value is the decimal portion. So, today (01/25/20) at 9:00 AM = 43855.25. Microsoft chose to store date-only values in date-time format (i.e., integer & decimal), probably to standardize date-time elements across the Microsoft ecosystem & to enable date math. Given that decision, Microsoft had to decide which time (decimal value) to use for date-only values. It makes sense to me that MS would pick .00 as the decimal portion of date-only values. Today is 43855.00, tomorrow is 43856.00, etc. The time value of midnight (12:00 AM) is .00, so the storage of the date-time value of 43855.00 for the date-only assignment of 01/25/20 means that the date-time representation of that value is 01/25/20 12:00 AM.
Thanks for sharing Jim. Great stuff
Thank's Shane. It's very helpful. I'm working on powerapps and I've some issues with the date. I can't update the Excel field with the date from powerapps without the time...i just need to have the date...Do you have any solution? Thanks un advance for your help 😀
Use text(today(), shortdate) will get you the text date you want
@@ShanesCows thank's ! It's brilliant :)
Working eheh 😀
Hi Shane,
How are you ? Great video.
How do I add the hours to a list?
Example:
I have a list with 500 lines, how do I do all the times on the list?
Time column
12:00 pm
4:00 pm
11:00
13:25
Hi Shane. Great Video. Thanks for sharing. I am getting some error when i try to save the form to sql server db and the update for one datacard is TimeValue(HourValue8_2.Selected.Value & ":" & MinuteValue8_2.Selected.Value ) and my db column datattype is time(7). and the error is "We can not convert the value 420000 to time."
I am not sure. sorry
Aewsome video like always! Thank you very much :D
Glad you enjoyed!
what about displaying calendar week in a datepicker/textfield and validation of the workweek? I want to show YYYY WW and make the field uniek.
I think you could do that but you would have to create the work week logic.
@@ShanesCows can we connect on saifalikhan1301@gmail.com , I need to understand this work week logic, to auto populate values from monday to sunday on 7 date fields of a repeating table in gallery
Hi Shane, Great video as usual, Passing date into Flow as parameter throwing error for SharePoint date column.
Code - Text(DataCardValue13.SelectedDate,"[$-en-US]mm/dd/yyyy","en-US"),----
Error - string 'mm/dd/yyyy/' does not validate against format date, Any thoughts on this..?
In flow I've used formatdatetime. But same result.
Appreciate your reply on this. Thank you.
Hey, It worked, Same formula didn't work y'day, worked t'day.
Text(DataCardValue13.SelectedDate, "[$-en-US]yyyy-mm-dd"),
what has changed over night..
Glad you got it working
is that deep dive video on time zones out yet? thanks this was really helpful.
might be an easy fix but, showing people that the time zone can be changed for SharePoint might be worthwhile, took me longer then you'd expect to find documentation on how to do it. then the premade formulas work perfectly. anyway, thanks for the video it clarified a lot.
Not yet but still on the list. Time zones are a pain.
Great stuff to know Shane; I have several questions... I'm reading a date value from a dataverse table and showing in a gallery. The value showing is one day prior to the date stored in the table so, I want to add 1 to the date. I tried different lines of code but none seem to work... DateAdd(ThisItem.'Date Value',1,ShortDate) - this returns nothing. I have to use Text(ThisItem.'Date Value',ShortDate). Please help if you can. Thanks.
Nice video Shane, how can I update date and time on a label by clicking a button
Set a variable to the date and time you want. Have the variable be the Text of the label.
Excellent, solved, thanks Shane
Hi
Can you show the complete code for time stamp in text box by pressing button
Leave a new comment with the time stamp you want and I will try to find it.
Thank you, Mister Shane for this video. It's possible de make de filtre fonction with a datepicker?
Depends on the datasource but the same concept should apply
Hi Shane, awesome video! I've got some trouble with uploading a date to Sharepoint via Flow. I get this error code:
input body for trigger 'manual' of type 'Request' did not match its schema definition. error details: 'String date' does not validate against format 'date'.'."
I've tried using Text(Today(), "dd-mm-yyyy"), as well as DateValue("date string"), used the DatePicker, and straight up just putting Today() and nothing's changing that error code. I've redone the Flow from scratch and reconnected it to PowerApps as well, and in Flow I've tried Initialising the input as a string and formatDateTime() in the Compose step.
Justin I am not sure why. You tried exactly what I would have done. 🤔
Hei Shane,
Thank you for nice video, I have tried to find the days difference between to date from two different gallery fields . Both fields are DateTime fields in datavarse entity
DateDiff(Gal_vak.Selected.'Dato og tidspunkt', Gal_booking.Selected.'Innkalling tidspunkt', Days) I got "incorrect argument ,expected number error. "
I have modified the formula
DateDiff(DateValue(Text(Gal_vak.Selected.'Dato og tidspunkt',DateTimeFormat.ShortDate)),
DateValue(Text(Gal_booking.Selected.'Innkalling tidspunkt',DateTimeFormat.ShortDate)),Days
)
then i got "The argument to DateValue function do not represent a valid date or time "
can you help me to solve this error
I am not sure. Doesn’t seem like you did anything wrong. 🤨
Shane, Awesome PowerApps training and Help. Great vid's. I have a Calculated date field in a list and this shows as UTC in my app, and the Text format technique doesn't change it. Any Ideas would be appreciated.
Use DateValue to change it from text to DateTime. Then you can use Text function to manipulate it.
How do you collect a date or date/time in a collection so you can patch it later? I'm looking for a summary of how to declare variables of the various types. (TextVar: "", NumVar:0, BooleanVar:true, etc) Thanks!
Thanks, Shane!! Great video, as always quality stuff. I've a question regarding the DatePicker control. Is there any way a user can select a date without hitting the "OK" button?
Not that I know of Kevin. ☹️
HI SHANE, GOOD DAY TO YOU
HOW TO COUNT THE ROWS BY DATE WISE IN POWER APP
Use CountRows and feed it in a filtered data source of just the date you want to count.
Hi Shane, thanks and great video.
Do you know how to set the function to make sure that the exit date is greater than enter date?
For example, If an activity starts on 20/11/2021 and it ends on 15/11/2021 the system gives an error message or just does not display any earlier date than start date.
You could do something like have a label (or make your save button greyed out) depending on the result of an If Then ... If DatePicker2 < DatePicker1 then show error
Hi Shane, been binging your amazing videos for the past week, and its so amazing how much you are able to teach through your videos and samples.
My question is sort of to parts. Firstly, would you be able to give an example of how to create a week number drop-down/picker? and 2nd would you at some point make a video about that TimeSheet app you made. I am looking to make something very similar, and could use some insights (to steal ;) )
Thank you and keep up the great work!
Thank you for posting this vidos! I have a question I have tried to add a date from a date picker to sharepoint from power apps using power automate. Using formula: Set(StartDate, Text(DatePickerCanvasStartDate.Value,DateTimeFormat.LongDateTime24)) but its telling me its not in the rigth format... I have tried many different formulas. Innluding Set(StartDate,DatePickerCanvasStartDate.Value). No luck so far. Can someone help me out?
My sharepoint filed is set to date and time and includes time
Great Video! Thank you!
Glad to help. 😀
Great job, I have a doubt, I have a table where I need to add the time difference with the current one and the captured could you help me