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
Solved! Go to 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.
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.
@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
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.
@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 😞
@Anonymous
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.
@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
You can put it on the OnChange property on your date and time controls or you can add it to a button.
@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 🙂