Adding a hours and minutes text string as an accumulated total for current user

(0) Share

Report

Posted on by gazzo1967

1,151

Hi All

Hope you had a good festive period and Happy New Year 🙂

OK i'm look for some help to provide and accumulated value of hours and minutes for the current user.

I understand parts of the formula that needs to be constructed but not how to nest it all.

i have a variable set on app start for Current user (loggeduser)

Start date and Finish Date are Date Pickers etc from my sharepoint list

The 'Hours:Minutes' is a text input field which submits the hours and minutes to a single line of text field in my sharepoint list 'hour_flex'

the figure is achieved using @CarlosFigueira formula from this post

DateDiff(
dtStart.SelectedDate + Time(Value(ddHourStart.SelectedText.Value), Value(ddMinuteStart.SelectedText.Value), 0),
dtEnd.SelectedDate + Time(Value(ddHourEnd.SelectedText.Value), Value(ddMinuteEnd.SelectedText.Value), 0),
Hours) &
":" &
Mod(
DateDiff(
dtStart.SelectedDate + Time(Value(ddHourStart.SelectedText.Value), Value(ddMinuteStart.SelectedText.Value), 0),
dtEnd.SelectedDate + Time(Value(ddHourEnd.SelectedText.Value), Value(ddMinuteEnd.SelectedText.Value), 0),
Minutes),
60)

which as you can see works great.

But

What i would like to do is now have an accumulated total of Hours and Minutes for the Current user display in a label.

IF Current user has 3 entries in the sharepoint list of

1:20

2:23

1:20

I would like to display

5:03

Any help would be appreciated

Gary

Categories:

Building Power Apps

All responses (10)

Answers (1)

Sort by

Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Here's a rough template:

Clear(TotalMinutes);ForAll(Filter(DateTest,1=1),Collect(TotalMinutes,DateDiff(StartDate,EndDate,Minutes)))

You can then use this to Sum() them:

Sum(TotalMinutes,Value)

---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up.
gazzo1967 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous
Thank you for the swift reply 🙂
I kind of understand the formula in that i would be using a collection but i am trying to achieve an accumulated total for that individual who may have multiple entries as shown in my orig post.
Will this only show me the total in minutes?
the figures are hours and minutes that are stored in the list column
so i would like to achieve

hours Minutes
5 : 03
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Yes, the ForAll() function loops through your data to find the DateDiff in minutes for all records. You can also apply a filter for a specific employee or so on..
It's returned in minutes, but you can use this to return the format you want, building onto the Sum(TotalMinutes,Value) example above:
RoundDown(Sum(TotalMinutes,Value)/60,0) & ":" & Mod(Sum(TotalMinutes,Value),60)
---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up.
gazzo1967 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous
Thank you but forgive me if im coming across stupid lol

I get errors
So in my Text = pasted formula
i am getting errors as i am not sure what to change as this is new to me (collections)

Sorry 😞
gazzo1967 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous
StartDate and EndDate would be the 2 columns in my sharepoint list?
which are 'start' and 'finish' for me
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

No worries! Change StartDate and EndDate to your corresponding date column names.
gazzo1967 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous
Done.
Before i go any further where am i putting this formula?
On my label#.text?
if so
i am still getting an error even after changing the required entries
ps im EU based
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

You can put it on the OnChange property on your date and time controls or you can add it to a button.
gazzo1967 1,151 on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous
You Sir are an absolute gentleman it works great now 🙂
Thank you so much for your help and patience 🙂
Just need to filter it for the current user which i think i have done but as i haven't got another user to test it yet i can't check that part 😞
I will mark as accepted solution and thumbs up but would you mind if i contact you again if i have any ssue with the Current USer filter?

Thank you Gary 🙂
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Happy to help! You're more than welcome to message me with additional questions, but you may get a faster response by posting it to the forum!