You have a SharePoint list named UserTimeSheetEntriesDetail that records timesheet entries, including fields like Date, Hours, and Employee Email.
You need to calculate the total hours worked by the current user in the current week. The week starts on Monday.
Filter the entries
- The Filter function is used to filter entries from the UserTimeSheetEntriesDetail table.
Match User Email
- The condition vUser.Email = Employee. Email ensures that only entries where the current user’s email matches the employee's email are considered.
Note.(vUser = Current user email)
Calculate the Start of the Week
- Today(): Weekday(Today(), StartOfWeek.Monday) + 1 calculates the start of the current week (Monday).
- Today() gives the current date.
- Weekday(Today(), StartOfWeek.Monday) gives the day of the week for the current date with Monday as the start of the week.
- Subtracting the weekday number from the current date and adding 1 gives the date of the last Monday.
Calculate the End of the Week
- Today() + (7 - Weekday(Today(), StartOfWeek.Monday)) calculates the end of the current week (Sunday).
- Today() gives the current date.
- Weekday(Today(), StartOfWeek.Monday) gives the day of the week for the current date with Monday as the start of the week.
- Subtracting the weekday number from 7 gives the number of days until the next Sunday, and adding this to the current date gives the date of the next Sunday.
Date Range Condition
- Date >= Today(): Weekday(Today(), StartOfWeek.Monday) + 1 && Date <= Today() + (7 - Weekday(Today(), StartOfWeek.Monday)) ensures that only entries within the current week are included.
Sum the Hours
- Sum(..., Hour) sums the Hour values from the filtered entries to get the total hours worked by the current user in the current week.
Sum( Filter( UserTimeSheetEntriesDetail, vUser.Email = Employee.Email, Date >= Today() - Weekday(Today(), StartOfWeek.Monday) + 1 && Date <= Today() + (7 - Weekday(Today(), StartOfWeek.Monday)) ), Hour )
Join the conversation! Your thoughts help the community grow.