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

[gazzo1967]

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

[Anonymous · Accepted solution]

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]

@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

[Anonymous]

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]

@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]

@Anonymous 

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

which are 'start' and 'finish' for me

[Anonymous]

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

[gazzo1967]

@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

[Anonymous]

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

[gazzo1967]

@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 🙂