Power Apps: Calculate Overtime

It’s hard to manage people, and we should not control them. We should empower people to work their hours and deliver the expected result. It’s the case for many developers where they need time to come up with the best solution and not the one that works right away. Although you should never equal time spent working on productivity, some jobs have specific schedules. When people work overtime, they should be rewarded or at least recognized for their efforts.

The (failed) experiment

A while ago, we tried (and failed) to implement a timesheet Power App that allowed us to know how much time a project was taking instead of the time we allowed for it.

Our objective was never to control the team, but it’s hard to prove this to people when you ask them to log what they do the whole day. The information would be super useful, allowing us to get the project’s actual cost and then check with the post-revenue results and check if the project was indeed profitable or not.

All well and good but have you have ever heard the expression:

The road to hell is paved with good intentions

Well, the project failed because of people:

  1. Though this represented more work with no added value
  2. Refused to do it thinking that we were trying to control them
  3. I had incomplete timesheets, making the information useless.

We scraped the project, but for some of you, this could be useful, especially the overtime calculation, and that’s what I want to focus on this article. Making a timesheet is easy, but having the Power App calculating, per person, the overtime is something all different.

Something good came out

We understood that we needed to calculate overtime. It helped us also to tell management that people were working more time continuously and try to justify more resources. We considered a typical 8h day, and everything above that was considered 1day and 1 hour, for example, instead of 9 hours. It made it easier to show over time.

The Power App

We started with a simple SharePoint list with three lists:

  1. Allocation per person
  2. Projects allocated per person
  3. Time spent in project

Simple enough. We wanted to tell people that they should be working on project X or Y, making things transparent, and, for those projects, each could log time spent.

First the sorting & filtering

If you do a lot of Power Apps, you know that sorting and filtering can be quite simple, but you should be aware of delegation. You can check what delegation means and potential workarounds. You can also check the delegable function reference to know where you can optimize and what functions can help you.

If you have a Browse Gallery, it becomes quite simple, but let’s look at a complex example:

SortByColumns(Filter(nameOfTheColumnFromTheDataSource, StartsWith(Title, TextSearchBox1.Text)),"reportdate", If(SortAscending, Ascending, Descending))
  1. SortByColumn is quite straightforward. You just need a source and the order. Let’s isolate it:
SortByColumns(DATA, If(SortAscending, Ascending, Descending))

The idea is to have a Boolean variable called “SortAscending” and if it’s true sorts it from lower to higher and if it’s false the opposite.

  1. Data filtered. Here we want to filter by the text inserted in a search box. We map the column “Title” and have a function that selects all data that starts with the text of the text box.
Filter(nameOfTheColumnFromTheDataSource, StartsWith(Title, TextSearchBox1.Text)
  1. The sorting column. In our case, “reportdate.”

How to calculate the overtime

The easiest way to do the calculation is on the client-side. You can calculate on the server-side, but since we’re filtering the data, then we need to check the information in the Browse Gallery and be done with it.

The formula can be quite scary, but I promise you it’s simple. Let’s look at the whole formula and then break it down:

If(RoundDown(Sum(BrowseGallery1.AllItems, logtime) / (60*8),0) <> 0, RoundDown(Sum(BrowseGallery1.AllItems, logtime) / (60*8),0) & "d ","") & If(RoundDown(Mod(Sum(BrowseGallery1.AllItems, logtime),(60*8))/60,0) <> 0, RoundDown(Mod(Sum(BrowseGallery1.AllItems, logtime),(60*8))/60,0) & "h ","") & If(Mod(Sum(BrowseGallery1.AllItems, logtime),60) <> 0, Mod(Sum(BrowseGallery1.AllItems, logtime),60) & "m ","")

🙀

OK relax. Let’s go item by item.

First, you need to understand that this is a long set of chained “If” statements. The objective is to sum all items contained in the BrowseGallery1 (even if they are filtered) and return something like 1d 1h 2m, based on the number of minutes inserted in the “longtime” data source.

Let’s break it down into parts:

If(RoundDown(Sum(BrowseGallery1.AllItems, logtime) / (60*8),0) <> 0, RoundDown(Sum(BrowseGallery1.AllItems, logtime) / (60*8),0) & "d ","")

If the total summed time in minutes, divided by 60 x 8 (60 minutes and 8 hours), is not zero, then show that number, otherwise don’t show anything. With this, we get the days. Simple so far. So now let’s do the same for hours:

If(RoundDown(Mod(Sum(BrowseGallery1.AllItems, logtime),(60*8))/60,0) <> 0, RoundDown(Mod(Sum(BrowseGallery1.AllItems, logtime),(60*8))/60,0) & "h ","")

For hours we need to use the Mod function instead of the division. Why? If you look, the expression is the same as before, but we want to get the remainder. That’s our hours if we divide them by 60 since we’re dealing with minutes. The RoundDown clears the decimal places.

The minutes are quite simple since we’re already dealing in minutes. Just get the remainder of the division by 60 (the hours that we calculated above), and we get the minutes.

If(Mod(Sum(BrowseGallery1.AllItems, logtime),60) <> 0, Mod(Sum(BrowseGallery1.AllItems, logtime),60) & "m ","")

I know it’s long and tricky, but if you break it down, it becomes simple, right?

Why no screenshots

You may have noticed that I didn’t include any screenshots. If you read my tutorials before you know that I go overboard with screenshots to make things clear. In this case, I avoided it because I needed to show the Power App running and could, by accident, show some information about the company, and that would be bad. I hope this is all clear for you, but as always, if something was not clear, please leave a comment or interact on Twitter, and I’ll do my best to make it better.

Also, be sure to check out other Microsoft Power Apps-related articles here.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Microsoft Power Apps-related articles here

Photo by Mitchell Luo on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

%d bloggers like this: