cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gazzo1967
Impactful Individual
Impactful Individual

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

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)

 

Capture.JPG

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

1 ACCEPTED SOLUTION

Accepted Solutions
EricLott
Resident Rockstar
Resident Rockstar

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. 

View solution in original post

10 REPLIES 10
EricLott
Resident Rockstar
Resident Rockstar

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. 

View solution in original post

gazzo1967
Impactful Individual
Impactful Individual

@EricLott 

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

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
Impactful Individual
Impactful Individual

@EricLott 

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)

 

Capture.JPG

Sorry 😞

gazzo1967
Impactful Individual
Impactful Individual

@EricLott 

StartDate and EndDate would be the 2 columns in my sharepoint list?

which are 'start' and 'finish' for me

No worries! Change StartDate and EndDate to your corresponding date column names.

gazzo1967
Impactful Individual
Impactful Individual

@EricLott 

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

Capture.JPG

ps im EU based

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

gazzo1967
Impactful Individual
Impactful Individual

@EricLott 

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 🙂

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.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

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,496)