Power Apps Date Time Math - Do calculations for payroll and timesheets
HTML-код
- Опубликовано: 17 янв 2021
- Learn to use some of my favorite functions, like Mod and With, to do math with your date and times. Includes calculating Weeks and Days between dates, finding the start of the week, is it a weekday, and even rounding up or down to the nearest quarter hour. So much fun. ⏲
Functions included: DateDiff, TimeValue, Mod, Weekday, Text, and With.
Power Apps Consulting and training at www.PowerApps911.com 
Наука
I really like the way you do everything the long way (which is how us noobies think) and then show us how to cut it down to make it more usable. Awesome stuff.
Thanks. I try my best to make the content approachable.
Thank you so much for this! You have no idea how much it helps! I’ve been trying to create a timesheet form that captures start and end time with calculated duration, this could very well work! Thank you again!
Hi Shane,
Long time fan / student. You've made some great content that's helped me and others. You're the best.
Just wanted to leave a comment since this is an older/less visible video, but it's a video that I wish I saw sooner.
I hope more newbies find this video.
Also a fan of your IsMatch video lately too.
Take care!
This week I got a project about calculating the days off. I was worrying about how to calculate dates properly, so the video is spot on :D
I was looking out for you Anna. 😀
Hi Shane. You are a legend. Hot tip:
I like to use Switch statements with a true condition. As such
hoursBetween + Switch(true, minutesBetween > 52, 1, minutesBetween > 37, 0.75, minutesBetween > 22, 0.5, minutesBetween > 7, 0.25, 0)
Interesting 🤔
Another great video as always! Can't thank you enough.
You are welcome 🙏
Great video, Shane. I used to use rounddown function to get the amount of weeks. So, without using WITH function, my formula is
RoundDown(DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate) / 7, 0) & " weeks and " &
Mod(DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate) , 7) & " days"
Anyway, it is a great video. I'm learning a lot of thing with your videos.
Good stuff! There is always so many ways to do these things. 🐶
Awesome video Shane...thanks a lot
My pleasure!
Shane, 60/4=15 and 60/8=7.5 just to be completely exact; instead of 7 min.
Just had to say it, since I study research science mathematics.
Anyway love the video, much thanks!!!
Thanks! 😎
Hii Shane, you always come up with awesome ideas & solutions for our troubles in Power Apps. It's my request to you that can you please create one video tutorial on connecting and operate our home accessories with power apps.
That does sound fun. :) I need to.
Hi Shane, great video! Do u know if there is a way to show how many weekends falls between start and end date ?
Great video, Shane! I've watched it a few times 😂 along with the other Date & Time and the calendar ones. I feel like I'm kind of close but I am trying to figure out the output for a date entered with a date picker, the output must be the second Tuesday of the month, and if that has passed it moves to the next month. I've used DateAdd and Weekday functions together and apart, and I know I"m on the right track just can't seem to get it. Any tips?
Hi Shane thanks for the video ... just struggling to calculate between 4:00 PM and 12 :00 AM i have applied your formula but getting as -16 Hour 0 Minutes am i doing some thing wrong on the formula ... Thanks
"Total : "&" "&With({Minutsbetween:
DateDiff
(TimeValue(Dropdown1.Selected.Value &":"&Dropdown3.Selected.Value &" "&Dropdown2.Selected.Value),
TimeValue(Dropdown1_1.Selected.Value &":"&Dropdown2_1.Selected.Value&" "&Dropdown2_5.Selected.Value),Minutes)},
With({Minutsleftover:Mod(Minutsbetween,60)},
(Minutsbetween-Minutsleftover)/60&" "&"Hour"&" "&Minutsleftover&" "&"Minutes"))
Hey Shane - how would you 'add' two timestamps together? For example, and to play into the 'payroll' example in this video.... If someone is paid hourly, how do you add all their hours/minutes up to then pay them their hourly rate over say a week or 2 pay cycle?
Hello Shane, if my end date is less than start date, m able to popup message, but unable do this for date & time together, have any solution.??
Hey Shane, love your videos. All this date and time stuff is hard work. But learning all the time thanks. When you do your videos can you also show how to have dates show as dd/mm/yy for us on the other side of the world, thanks in advance it would be much appreciated.
Text(Today(), "dd/mm/yyyy") in a label should do it. 😀
@@ShanesCows thanks, I understand that. But can you make a dropdown display dd/mm/yy after selection? Or am I missing something here.
Hi, I love your videos on Power apps. I need a suggestion. I need to calculate the difference between current time and show it in a text box / label. I used the below formula in the Text property of the Text Label
Minute(now()) - Minute(ProdTime)
where the ProdTime is a variable which holds a time. When I update this formula, it gives me the hour difference between these two time (lets say 15 minutes). But the challenge is it is dynamic meaning when it past another minute, the text label still shows 15 minutes and doesn't change to 16. How to achieve this?
how do you save this in dataverse colums
How to use the 360 days function in powerapps?
How can I return all fridays between two dates ?
Nice videos! Do u know if there is a way to show calculated days between start and end date over months? So u could see in a gallery or graph how many days u have worked or is excepted to work for each month during year?
I haven't done exactly that before but I think all of the pieces to solve it are in this video. DateDiff and Mod, you just may have to get creative.
Thanks, Shane as always, great video great motivation,I would like to ask for networkdays like excel to exclude the weekends
The challenge is then you also have to exclude holidays. It gets tricky fast.
@@ShanesCows a video about datediff excluding holidays and weekends would be awesome 😵
Shane, can you provide me a step-by-step guide on how to produce the drop-down menu minutes as "00, 01, 02, 03, 04, 05" etc. I didn't see how the formula: ForAll(Sequence(60,0), Collect(colMinutes, {Value: Text(ThisRecord.Value , "[$-en-US]00")}) is related.
thx shane
You are welcome 🙏
Hi Shane, another great video! Do you think you can do a video how to calculate Business days/hours (extracting weekends and off-work hours).
Maybe at some point but it is the same math as here. Just more calculations
I agree this would be wildly helpful. Excel had NETWORKDAYS formula. Powerapps needs this.
Thank you very much for the video. Can you please share datediff excluding weekend and holidays.
I haven't done that one before because then holidays make it hard. 😑
At the end of the rainbow, there is a jar of gold.................... or Shane Young :)
Thanks Frank! 🐶
Thanks Shane. Really helpfull. How do I handle date serial number from excel as input in date picker powerapps. Any guide will be really helpful.
I have never done it in Power Apps only flow. You have to convert ticks. I think I showed it in this video. ruclips.net/video/QcWjAt7QVn4/видео.html
Hello sir, big fan from India. I have an issue with summing hours and minutes from TextInput in the Galley and display the total hours and minutes in Label which is outside of the gallery. I have weekly timesheet entry, there I have 7 inputs in a row as Sundayhour to Saturdayhour. If I wish I can add rows with same Sundayhour to Saturdayhour text inputs. I want to display the total hours of each day
Hello Shane, I just built my first fully working app and big part of it was because of you, now I just found out that you can only share it with people in our organization!!! is this right ? is there a way to add my app in our company website so people from uses it ?
Hey Sami that is correct. YOu cannot embed a Power APps app. YOu have to run it directly in a browser or the Power Apps app. And everyone needs to authenticate with the app and have a license.
Hey Shane, great video. How to add two days' working hours? For example, 8.5 and 8.5 in the PowerApps in the Total hours worked column? Appreciate it.
DateAdd function lets you add Hours by setting the 3rd input. But I don't think that is what you are asking?
@@ShanesCows , thank you for the reply, I just want to add 5 working days total hours worked, so I should have another column where I can add my time worked, thank you
Shane, may I ask, why did you use "with" instead of "set" or something else to set a variable?
Love those live troubleshooting moments! Again, awesome stuff! love those formulas!
With can be used in a label. Set cannot be. Bit with is only available in that one context.
@@ShanesCows Got it 👍 Thanks!
Master, give a class on calculating hours between different data. PLEASE! Save fear.
Did you see this one?ruclips.net/video/EbYMN4ouOvQ/видео.html
@@ShanesCows yes, but I couldn't understand it well, because of the "AM" and "PM" fields to differentiate. I would like to learn how to perform the calculation using only two inputs, with the condition to calculate the hours in the PM-AM period. for example:
The activity starts at 22:00h 08/10/2021 and ends at 05:00h 08/11/2021.
expected result 07:00h.
thanks for replying fast.😁👍🏼
Hi, how do I clear the list of commands given from Run?
Not sure I follow.
@@ShanesCows Sorry for my bad English.
In this image: prnt.sc/xaw9rs
In the Run window, when I click the drop down menu (the red arrow), a list of old commands that I issued in the past appears (the red rectangle). How do I clear the list, so when I click the drop down button (arrow), no command appears?
Thank you!
Can I restrict calender in terms of days like +30 , -30 days can be selected from today's days.
Nope, you will have to let them pick any date and then validate it falls in your range.
@@ShanesCows any function to validate range .
Talked about it a bit in the video. Or maybe this will help ruclips.net/video/s85i8UWw2QM/видео.html
You can't restrict that but you can don't allow go ahead with a date without your range using conditional in your submit button (or anything else like that).
This is awesome! I am 90% there.
WHAT IF .....
start date=today
end date=7 days in the future.
S.Hour=8
E.Hour=7.
We know there are 7 days difference, but how do I calculate if the ending time is less than the starting time. in this case the answer is -1. How do I calculate 6 days and 23 hours?
Maybe count the difference on hours. Then turn the hours total into days and hours as appropriate
@@ShanesCows I'll give it a go tomorrow. Having trouble with the math. 🙄
Thank you for the advice
Hi Shane, its a great video, but for example I am calculating extra hours for my employees, they submit the report and for example my employee reports that its extra hour began at 23:00 and finisih at 0145 of the next day, if I use this method its shows me a negative value, is there any way to make that kind of calculation? That if the hour its of the next day show me that difference
You need to add a date into the mix and set the logic to increment the date. With a little wiggle should be possible
Hi Cesar, did you fix the problem?
How can this problem be solved? I am dealing with summing times between 8:00 PM and next day 03:00 AM. What should I change in the code to see 7:00 hours in a textbox?
@@johnsengers98 Did anyone figure this out? I used a similar method but one thing I just noticed was if the user clocks in at 6:00 PM and out at 2:00 AM it is showing me they worked 16 hours instead of 8...
@@f.k.b.16 Add a datepicker as Enddate. In Text: Set(EndDate, name datepicker.SelectedDate. In another Textbox of Label put the same formule as written in this video. Example: With({minutesbetween:DateDiff(DateAdd(StartDate,StartHours,Hours),StartMinutes,Minutes),DateAdd(DateAdd(EndDate,Endhours,Hours),EndMinutes,Minutes)Minutes)},
With({minutesleftover:Mod(minutesbetween,60)},
(minutesbetween - minutesleftover)/60 & “:” & Text(minutesleftover,”[$-en-US]00”) & |” Hours” ))
This would help you to sum the difference between Starttime and Endtime over 24 Hours.
Great video. Though is giving me + 1 hour every 12 and 24 hours
How to calculate the month difference between a date range
DateDiff will do months. 😎
I was testing and noticed a rare event of a 4 week month. Feb 2026. So the last week of the month view is all the next month.
18:34
Hello!
How to find the number of hours worked by employee ABCD knowing that we have a table that looks like this:
Employee || Date ||Start time ||End time
******************
ABCD || 01/01/2022 || 08:00 || 11:01
ABCD || 01/01/2022 || 12:05 || 15:00
ABCD || 01/01/2022 || 15:55 || 18:16
Please help me.
need to calculate half day