cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kbrose
Level: Powered On

How to get the total working time per month from Sharepointlist

Hi Community,

 

I've been struggling with an issue in Powerapps. 

I have an PowerApps application with that I save my workingtime to a SP-List. 

In that List I have different columns for example : User, Date, Starttime, Endtime, workingtime and Month-Year.

So what I want to have is an small Dashboard-Gallery that's shows me the total workingtime of every Month in my SP-List for the current User. 

See Screenshot for example

gallery Arbeitszeit BSP.PNG

The Values from the Screenshot above (06.2019 and 07.2019) I get from this formula

Distinct(Zeiterfassung;Mona_Jahr).Result

But actually I'am struggling with the GroupBy and Sum Function 😕

A nice-to-have additional to that would be a comparisson of target and actual workingtime. Therefore I need to know how I can get the number of weeks a month have, with that I can calculate the target workingtime

So maybe someone can help me.

 

Thanks and kind regards

4 REPLIES 4
TheRobRush
Level 8

Re: How to get the total working time per month from Sharepointlist

i don't have time to do the groupby function right now (it's late here, but if noone gets to it by tomorrow I will try to do it then for you) but did want to share this image with you I made for another person. Handy one to have on hand for calculating time differences. (since you have a start/endtime column)

 

 

Capture.PNG

 

Community Support Team
Community Support Team

Re: How to get the total working time per month from Sharepointlist

Hi @kbrose ,

Do you want to get the total working time per month?

Try this formula:

AddColumns(
GroupBy(
AddColumns(listname,"month",Month(Date)), //add one column with the month value
"month","detail" //group based on month
),
"sumworkingtime",Sum(detail,workingtime) //add one column with the sum working time of every month
)

Please replace the listname with the name that you use.

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kbrose
Level: Powered On

Re: How to get the total working time per month from Sharepointlist

@v-yutliu-msft 

Hi,

sorry for my late answer. I was on holiday.

 

Thanks for your answer but it doesent actually :/.

Add Columns Fehler.PNG

I tried to change the "Month(Date)" part with the "Datum" Column from my SP List, but it didnt work either.

Add Columns Fehler_2.PNG

Here is a snapshot from my SP List. I have all Date informations that I need like (Month, Monthname, Year, Month_Year) but I cant Group it in Powerapps 😞

 

SP List.PNG

 

Maybe you have an idea to solve my problem.

Thanks and kind regards

 

kbrose
Level: Powered On

Re: How to get the total working time per month from Sharepointlist

okay, I think I got it. But now I have a lot of zeros after my number. Commas are also not displayed. The correct values are (11,5/58,00/29,00) for Aug./Jul./Jun. Does anyone can explain why I have these zeros and no commas?

I want to have it like "8,00 h"

My Functions are:

 

For the Month-Label : GroupBy(Zeiterfassung;"Mona_Jahr";"Items")

For the Workinghour-Label: Value(Sum(Items;AZ))

 

Gallery Sum AZ.PNG

 

Thank and regards

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (4,255)