cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Displaying sharepoint list entries filtered from this week

I have a sharepoint list (CheckInOut_Test) where a column is named CheckIn. This column holds different dates. Another column shows the hour at office for the specific day. Now I want to display the sum of the of hours for the current week and also for the current month. I tried this: 

 

"Days this month: " &

Sum(Filter(CheckInOut_Test; CheckIn >= Date(Year(Today());Month(Today());1) );WorkingHours_Day)

 

Unfortunately, the number displayed after calculation is really high and has nothing to do with the working hours dislayed in the column. The problem seems to be this part: [>= Date(Year(Today());Month(Today());1)] 

Can someone help me with this? Thanks!!

EDIT: I removed the Filter to see what happens then. When just using the sum, the number was even higher. In the area of billions. So I assume the problem is not the part mentioned above, but the way SUM() handles the working hours. The working hour column is calculated as (checkOut - Checkin) which are two columns themselves. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Dual Super User
Dual Super User

Re: Displaying sharepoint list entries filtered from this week

Hi @anotherlama , 

 

To get the sum of hours in current week, use the formula:
Sum(Filter(CheckInOut_Test,CheckIn >= Now() - Weekday(Now(), StartOfWeek.Monday) && CheckIn <= Now() - Weekday( Now(), StartOfWeek.Monday )+7),NoOfHours)
I am assuming that the week starts on Monday and ends on Sunday, but if you want to change this, please modify StartOfWeek.<Day> in the formula with the required day.
To get the sum of hours in current month, if you want to get it from 1st to last date of the month, use the expression:
Sum(Filter(CheckInOut_Test,CheckIn >= Date(Year(Today()), Month(Today()), 1) && CheckIn <= Date(Year(Today()), Month(Today()) + 1, 0)),NoOfHours)
If you want to get the data till current date, use this formula:
Sum(Filter(CheckInOut_Test,CheckIn >= Date(Year(Today()), Month(Today()), 1) && CheckIn <= Today()),NoOfHours)
Replace NoofHours with the field name that you are using.
MicrosoftTeams-image (44).png
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Dual Super User
Dual Super User

Re: Displaying sharepoint list entries filtered from this week

@anotherlama You have to add that as an expression from the expression editor:

yayy.png

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

10 REPLIES 10
Highlighted
Dual Super User
Dual Super User

Re: Displaying sharepoint list entries filtered from this week

Hi @anotherlama , 

 

To get the sum of hours in current week, use the formula:
Sum(Filter(CheckInOut_Test,CheckIn >= Now() - Weekday(Now(), StartOfWeek.Monday) && CheckIn <= Now() - Weekday( Now(), StartOfWeek.Monday )+7),NoOfHours)
I am assuming that the week starts on Monday and ends on Sunday, but if you want to change this, please modify StartOfWeek.<Day> in the formula with the required day.
To get the sum of hours in current month, if you want to get it from 1st to last date of the month, use the expression:
Sum(Filter(CheckInOut_Test,CheckIn >= Date(Year(Today()), Month(Today()), 1) && CheckIn <= Date(Year(Today()), Month(Today()) + 1, 0)),NoOfHours)
If you want to get the data till current date, use this formula:
Sum(Filter(CheckInOut_Test,CheckIn >= Date(Year(Today()), Month(Today()), 1) && CheckIn <= Today()),NoOfHours)
Replace NoofHours with the field name that you are using.
MicrosoftTeams-image (44).png
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Community Support
Community Support

Re: Displaying sharepoint list entries filtered from this week

Hi @anotherlama ,

Do you want to sum up a weekly hours of work and monly hors of work?

Could you tell me what is WorkingHours_Day's and CheckIn's data type?

I assume that WorkingHours_Day is number CheckIn is date.

I've made a similar test for your reference:

Monthly:

Sum(Filter(AddColumns(list1,"month",Text(CheckIn,"[$-en-US]mm")),month=Text(Now(),"[$-en-US]mm")),WorkingHours_Day)

Weekly:(assume that Sunday is the beginning and Satday is the ending)

Sum(Filter(list1,CheckIn>=Now()-Weekday(Now())+1&&CheckIn<=Now()+7-Weekday(Now())),WorkingHours_Day)

633.png

 

 

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.
Highlighted
Helper II
Helper II

Re: Displaying sharepoint list entries filtered from this week

thanks a lot for your help! Could you also help me with doing the same in Flows / with an ODATA request. I tried very long and did not find a solution. This is what I tried: 

 

and((equals(EmployeeMail,body('Mein_Profil_abrufen_(V2)')?['mail'])),(greaterOrEquals(CheckIn,(startOfMonth(addHours(utcNow(),9))))))

 

Thanks!!

Highlighted
Dual Super User
Dual Super User

Re: Displaying sharepoint list entries filtered from this week

@anotherlama 

 

Can you post a screenshot of your flow?

Highlighted
Helper II
Helper II

Re: Displaying sharepoint list entries filtered from this week

pls find attached the screenshot 🙂 thanks!

Highlighted
Dual Super User
Dual Super User

Re: Displaying sharepoint list entries filtered from this week

@anotherlama You have to add that as an expression from the expression editor:

yayy.png

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Helper II
Helper II

Re: Displaying sharepoint list entries filtered from this week

@yashag2255 
Thanks for your quick help! I do not get a error message with the new version, but still no file is created... something still seems to be wrong... any ideas?

Highlighted
Helper II
Helper II

Re: Displaying sharepoint list entries filtered from this week

@yashag2255 

EDIT: Actually, I am getting this error message: 

 

The expression "EmployeeMail eq 'foo@foo.com' and CheckIn Ge '2019-06-01T00:00:00.0000000Z'" is not valid.

Highlighted
Helper II
Helper II

Re: Displaying sharepoint list entries filtered from this week

Found the solution... I entered "Ge" instead of "ge"... sometimes, it is the small things.. anyways, thanks a lot for your help!! 🙂

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,490)