cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChezWam
Frequent Visitor

Add times

Hello everyone

I am working on an application to record arrival and departure times. I have two Sharepoint lists:
- one with the users
- one in connection with the first, which contains the time of day (morning, noon, evening, etc.), the time of the check-in, the day of the check-in, the person who checks in.
I would like to add to the application the total of hours spent during the day. I tried different approaches but without real success, in a gallery I start with:
Filter ('Table time';
IDRef.Value = DataTable1.Selected.ID)
to display the times corresponding to the user, then I am blocked for the calculation of the hours per day.
Do you have a working path? A solution ?
Thank you 🙂

11 REPLIES 11
BrianS
Super User
Super User

As long as you have the start and end times all you need is the DateDiff function. You can find the documentation here.

Basically DateDiff(Start, End, Minutes) will be what you need, assuming you want the time in minutes. The documentation shows all the other units available

ChezWam
Frequent Visitor

Thanks for your advices.

My problem is how to calculate the time in hours by making the link between a Choice column (morning, noon, evening), a Datetime column, a Date column and a Person column. The difficulty is how to correctly target the data.

BrianS
Super User
Super User

You said in the original post that you had check-in time as data you are collecting. Do the users need to return to the app to check out? If you don't record that time anywhere it is still available to the app through the Now() function. You could run DateDiff() on that and record it, or you could add the check-out time to your data. 

Maybe you need to be more specific about your data - what is the morning, noon, evening Choice column used for? What are the DateTime and Date columns? I assume the Person is holding the person using the app? When they go to check out I assume you do a Lookup() to find the record where they checked in - correct?

ChezWam
Frequent Visitor

I have a share point list with the users and their emails.

Linked by an IDREF in a share point list with UserName, TypeHour, Hour, IDRef, Day.

TypeHour is to differentiate at what time of the day the user records his time.

For example, some users check in at the same time but not for the same reasons.

I am considering using the date column to group the hours by days by users.

I do a Filter ('Table Temps';
IDRef.Value = DataTable1.Selected.ID) to display their schedules.

BrianS
Super User
Super User

So then I think what you want is to find the amount of time for each "type" of activity. If that is the case you would need to record the start time for each type and then when they are "checking out" you would need to Filter() the list by name and TypeHour to find the "check-in" entry for that type. You can then use the DateDiff() function to find the time spent for that TypeHour

ChezWam
Frequent Visitor

What I want is to calculate the time spent per day per person.


Time of arrival at midday break = x hours
Return time for lunch break - Departure time = y
Total hours of presence during the day z = x + y

 

In a gallery I first displayed the data according to the user, then I group them by day and I sort them by dates.

Sort (GroupBy (Filter ('Table time'; (Value (IDRef.Value) = DataTable1.Selected.ID)); "Day"; "DATA"); Day; Descending)

Once at this level I don't know how to target the hours to do the calculation.

BrianS
Super User
Super User

If you want it to happen "automatically you would need to use a Flow. If you want to do it with a button press, you can do it within PowerApps. Provided you can force there to always be 4 entries (Arrival, Lunch start, Lunch end, Day end) try the following: Create a gallery with the Items property containing

With(
{
wList:
Sort (GroupBy (Filter ('Table time'; (Value (IDRef.Value) = DataTable1.Selected.ID)); "Day"; "DATA"); Day; Descending)
)
},
ForAll(
Sequence(CountRows(wList)),
Patch(
Last(
FirstN(
wList,
Value
)
),
{RowNo: Value}
)
)
)

 Then use ClearCollect(col_test,Gallery.AllItems) to create a Collection, That will give you a collection with a RowNo field containing 1 - 4. You can then do the math by using DateDiff(LookUp(col_test,RowNo=1)."Heure Pointee",LookUp(col_test,RowNo=2)."Heure Pointee",Hours) - For your first duration. You can repeat the calculation for the other values and then add them together to get your daily total. I'm sure there is a way to do this without a gallery, but I wanted to get this out to you and was having a mental block on bypassing the gallery. Hope this helps.

BrianS
Super User
Super User

The ClearCollect and math will be in the OnSellect of the Button - sorry - missed that step

BrianS
Super User
Super User

Forgot to mention - the numbering scheme comes from @warren Blez

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,306)