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))
)
)
)
)
Thursday of last week:
(TODAY() - WEEKDAY(TODAY()) + 5 - 7)
Wednesday of this week:
(TODAY() - WEEKDAY(TODAY()) + 4)
Thank you!
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
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |