Payroll function workweek issue

My client’s workweek runs from Thursday to Wednesday. Employees work 7 days a week. I need an expression to pull all the recorded timesheet entries for the work week for payroll to be done. I can’t seem to get the expression done correctly using calculations withWEEKDAY().

Here is the current expression I’ve got:
SUM(
SELECT(
Timesheet[Time Worked],
AND(
([Name] = [_THISROW].[Name]),
AND(
DATE([Time In])>=(TODAY() + MOD((5 - WEEKDAY(TODAY()) - 7), 7)),
DATE([Time In])<=(TODAY() + MOD((5 - WEEKDAY(TODAY()) + 7), 7))
)
)
)
)

Doing this with workdays is fairly complicated. 2 easier alternatives to consider:

1 = Why not instead let your client simply choose 2 days and output the results based on that

2 = Create a schedule to run at midnight on Wednesday to calculate the last 7 days

1 Like

Thursday of last week:
(TODAY() - WEEKDAY(TODAY()) + 5 - 7)

Wednesday of this week:
(TODAY() - WEEKDAY(TODAY()) + 4)

4 Likes

Thank you!

3 Likes