cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## 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 III

## Re: Displaying sharepoint list entries filtered from this week

Hi @Anonymous ,

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.

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!
Highlighted
Dual Super User III

## Re: Displaying sharepoint list entries filtered from this week

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

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!

10 REPLIES 10
Highlighted
Dual Super User III

## Re: Displaying sharepoint list entries filtered from this week

Hi @Anonymous ,

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.

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!
Highlighted
Community Support

## Re: Displaying sharepoint list entries filtered from this week

Hi @Anonymous ,

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.

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)`

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
Anonymous
Not applicable

## 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:

Thanks!!

Highlighted
Dual Super User III

## Re: Displaying sharepoint list entries filtered from this week

@Anonymous

Can you post a screenshot of your flow?

Highlighted
Anonymous
Not applicable

## Re: Displaying sharepoint list entries filtered from this week

pls find attached the screenshot 🙂 thanks!

Highlighted
Dual Super User III

## Re: Displaying sharepoint list entries filtered from this week

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

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!

Highlighted
Anonymous
Not applicable

## 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
Anonymous
Not applicable

## Re: Displaying sharepoint list entries filtered from this week

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
Anonymous
Not applicable

## 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!! 🙂

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,829)